SQL Server identity column incrementing without insert

Question

I have a C# console app which is using a stored procedure to insert rows into a SQL Server database. Currently the database has about 1m rows, however the ID column is up at 26m!

On the last successful insert the ID went from 16m to 26m. The app runs multiple threads in parallel so there is a chance it could be trying to insert two things at the same time.

Is there anything other than a transaction rollback that would cause the identity column to increment without adding a row?

It seems unlikely that there were 10 million times that two threads tried to write a conflicting row at the same time.

I'm using a temporary table variable which is populated by passing XML into the stored proc:

-- insert data if it doesn-t already exist  
insert into DataStore (DataText, DataSourceID)
select distinct td.dataText, td.dataSource 
  from @tempData td
 where not exists ( select 'x' 
                      from DataStore ds 
                     where ds.DataText = td.dataText );

You have a race condition there. That pattern will not work under concurrency as illustrated here — Martin Smith
– Martin Smith, Commented Dec 28, 2011 at 11:37
Does DataText have a unique constraint on it? Maybe you did actually encounter a whole load of rollbacks. — Martin Smith
– Martin Smith, Commented Dec 28, 2011 at 11:44
DataText doesn't have a constraint, but another column DateCreated (populated by default value of getdate()) does have one. — finoutlook
– finoutlook, Commented Dec 28, 2011 at 12:59
(but DateCreated isn't a unique constraint I forgot to mention) — finoutlook
– finoutlook, Commented Dec 28, 2011 at 14:11

gbn · Accepted Answer · 2011-12-28 11:48:25Z

5

What can leave gaps?

DELETEs
Explicit rollbacks
Constraint violations (= implied rollback)
SET IDENTITY_INSERT
Replication without NOT FOR REPLICATION clause
DBCC CHECKIDENT (edit: as per Oleg Dok's answer) ...

I'd also check the increment value using IDENT_INCR in case it is really is 5 million or such

edited Dec 28, 2011 at 11:48

answered Dec 28, 2011 at 11:31

gbn

435k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

finoutlook Over a year ago

Increment is 1, never doing any deletes on the table. I'll take a look at constraints.

gbn Over a year ago

@finoutlook: added more ideas

Oleg Dok · Accepted Answer · 2011-12-28 11:41:00Z

2

Inserted and deleted record increments identity counter, as mentioned by @gbn - Rollbacks and c-violations also, but there is two more options:

SET IDENTITY_INSERT
DBCC CHECKIDENT(..., RESEED)

answered Dec 28, 2011 at 11:41

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

1 Comment

gbn Over a year ago

I added DBCC CHECKIDENT to my answer, forgot about that one. Cheers

Collectives™ on Stack Overflow

SQL Server identity column incrementing without insert

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related