Solved

Auto-Increment Skips Line Numbers?

Posted on 2001-09-12
6
457 Views
Last Modified: 2008-02-01
Hi,

I have a test table with 3 collumns named RecordID Identity(1,1) (int), ParentID(references RecordID) (int, null), and Data (just to insert to, it is a char datatype).

The ParentID column references the Record ID column, and can be null (as in a table of employees, some of which are supervised by others, but the supervisors would have that column as a null).

The problem I am having is this:

If I insert a few records, the first with ParentID as Null, the rest referencing the first or any row before the new one, they go in fine, with RecordID incrementing by one as it is supposed to.

Then I go to insert a row with ParentID referencing a row that does not exist (like say 1000000).  As you would expect, this insert fails.  If I do it say 20 times, it will fail 20 times.

Now, I go to insert a column with ParentID referencing a valid column, and the next record is inserted, but the RecordID column has skiped a number for each failed row...so If i tried to insert 20 rows and they failed, my next row will have an ID of PreviousRow+20.

Somehow the failed inserts are causing the ID column to increment.

Does anyone have any input on this?  Is it a glitch or am I missing something here?  I have never seen this before, could it have to do with the table referencing itself?

Thanks


0
Comment
Question by:elig
6 Comments
 
LVL 5

Accepted Solution

by:
kelfink earned 100 total points
ID: 6478157
Elig,

I guess you could call it a glitch, but I think it's just "the way it works", since it happens even if the table references a separate table.  Run this example.

drop table myRecord;
drop table parentTable;
create table parentTable (RecordId int not null identity (1,1) primary key, lname char(30));
create table myRecord ( RecordId int not null identity (1,1) primary key, lname char(30),
     ParentId int null foreign key references parentTable );
insert into parentTable ( lname ) values ( 'iacocca' );
insert into myRecord ( lname, parentid ) values ( 'smith', null );
insert into myRecord ( lname, parentid ) values ( 'jones', null );
insert into myRecord ( lname, parentid ) values ( 'bad', 10 );
insert into myRecord ( lname, parentid ) values ( 'blue', 10 );
insert into myRecord ( lname, parentid ) values ( 'bad', 10 );
insert into myRecord ( lname, parentid ) values ( 'bad', 10 );
insert into myRecord ( lname, parentid ) values ( 'good', 4 );
insert into myRecord ( lname, parentid ) values ( 'huh?', null );

select * from myRecord;

You'll see:
RecordId    lname                          ParentId    
----------- ------------------------------ -----------
1           smith                          NULL
2           jones                          NULL
8           huh?                           NULL

So, looks like SQLServer allocates that Identity before checking for foreign key violations.  Shouldn't be a big deal.  You'll end up with gaps, generally, for other reasons, such as deletes.  
I think SQL Server works this way for speed reasons.  THey can grab the new identity quickly before the data check, and not worry about locking that resource during the testing.  If another session wanted to run the same code, it wouldn't have to wait for this session, just to receive its identity value.

Kevin
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6478543
i agree with kelfink, and can tell you that ORACLE for example works exactly the same way using the SEQUENCE object...
CHeers
0
 
LVL 9

Expert Comment

by:miron
ID: 6479291
there is really strange thing though, the identity gets incremented for a table even while transaction has not been committed. I run code kelfink posted with modifications

....
begin tran
insert into myRecord ( lname, parentid ) values ( 'smith', 32 );
if ( @@error <> 0 )
     rollback tran
else
     commit tran
...

and got gaps in the identity. So, intermediate check and identity re - seed might help to avoid 'identity gaps'. However, it will take an overhead. So, it looks like kelfink put it as good as it gets. The code is not as straight-forward as I would of wanted it to be, but the DBCC may not be encapsulated into user defined function, and creating a function to encapsulate increment logic would not be practical, because it will have to use MAX( RecordId ) - an aggregate function on a rapidly growing table, and as table grows it will be too slow.

ALTER TABLE myRecord DROP CONSTRAINT FK__myRecord__ParentTABLE
drop table parentTable
drop table myRecord

create table parentTable (RecordId int not null identity (1,1) primary key, lname char(30));

create table myRecord ( RecordId int not null identity (1,1) primary key, lname char(30),
    ParentId int null CONSTRAINT FK__myRecord__ParentTABLE foreign key references parentTable );


declare @x int
insert into parentTable ( lname ) values ( 'iacocca' );

insert into myRecord ( lname, parentid ) values ( 'smith', null );
SELECT  @x = MAX( RecordId ) FROM myRecord
IF (  IDENT_CURRENT ( 'myRecord' ) > @x  )
     EXEC( 'DBCC CHECKIDENT( ''myRecord'', RESEED,' + @x + ' ) ' )
insert into myRecord ( lname, parentid ) values ( 'jones', null );
SELECT  @x =MAX( RecordId ) FROM myRecord
IF (  IDENT_CURRENT ( 'myRecord' ) > @x  )
     EXEC( 'DBCC CHECKIDENT( ''myRecord'', RESEED,' + @x + ' ) ' )
insert into myRecord ( lname, parentid ) values ( 'bad', 10 );
SELECT  @x =MAX( RecordId ) FROM myRecord
IF (  IDENT_CURRENT ( 'myRecord' ) > @x  )
     EXEC( 'DBCC CHECKIDENT( ''myRecord'', RESEED,' + @x + ' ) ' )
insert into myRecord ( lname, parentid ) values ( 'blue', 10 );
SELECT  @x =MAX( RecordId ) FROM myRecord
IF (  IDENT_CURRENT ( 'myRecord' ) > @x  )
     EXEC( 'DBCC CHECKIDENT( ''myRecord'', RESEED,' + @x + ' ) ' )
insert into myRecord ( lname, parentid ) values ( 'bad', 10 );
SELECT  @x =MAX( RecordId ) FROM myRecord
IF (  IDENT_CURRENT ( 'myRecord' ) > @x  )
     EXEC( 'DBCC CHECKIDENT( ''myRecord'', RESEED,' + @x + ' ) ' )
insert into myRecord ( lname, parentid ) values ( 'bad', 10 );
SELECT  @x =MAX( RecordId ) FROM myRecord
IF (  IDENT_CURRENT ( 'myRecord' ) > @x  )
     EXEC( 'DBCC CHECKIDENT( ''myRecord'', RESEED,' + @x + ' ) ' )
insert into myRecord ( lname, parentid ) values ( 'bad', 10 );
SELECT  @x =MAX( RecordId ) FROM myRecord
IF (  IDENT_CURRENT ( 'myRecord' ) > @x  )
     EXEC( 'DBCC CHECKIDENT( ''myRecord'', RESEED,' + @x + ' ) ' )
insert into myRecord ( lname, parentid ) values ( 'good', 4 );
SELECT  @x =MAX( RecordId ) FROM myRecord
IF (  IDENT_CURRENT ( 'myRecord' ) > @x  )
     EXEC( 'DBCC CHECKIDENT( ''myRecord'', RESEED,' + @x + ' ) ' )
insert into myRecord ( lname, parentid ) values ( 'smith', null )
SELECT  @x =MAX( RecordId ) FROM myRecord
IF (  IDENT_CURRENT ( 'myRecord' ) > @x  )
     EXEC( 'DBCC CHECKIDENT( ''myRecord'', RESEED,' + @x + ' ) ' )


select * from myRecord;
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 9

Expert Comment

by:miron
ID: 6479295
I really saw my code added at about 7:00PM PST, yesterday. Has anyone ever saw the posting to disappear?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6479304
... no, and i didn't get a email notification neither ...
0
 

Author Comment

by:elig
ID: 6480641
Wierd that I never noticed this before.  It really dosn't have any effect on the app, I am already way past this table, but it just startled me to see rows skipping.

Thanks everyone.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now