Solved

Auto-Increment Skips Line Numbers?

Posted on 2001-09-12
6
467 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 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
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

729 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