?
Solved

Auto-Increment Skips Line Numbers?

Posted on 2001-09-12
6
Medium Priority
?
471 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 400 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 143

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 143

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

621 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