Solved

Auto-Increment Skips Line Numbers?

Posted on 2001-09-12
6
464 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 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 and will be exposed to the many uses the SELECT statement has.

828 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