?
Solved

Auto-Increment Skips Line Numbers?

Posted on 2001-09-12
6
Medium Priority
?
468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in 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.
Suggested Courses

771 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