Home Dashboard Directory Help

DBCC CHECKIDENT with reseed done twice with inserts between leads to incorrect identity value by TMMike



Sign in
to vote
Type: Bug
ID: 808024
Opened: 11/7/2013 2:31:43 PM
Access Restriction: Public
User(s) can reproduce this bug


This may be a duplicate of issue 778610 ("Violation of PRIMARY KEY constraint with identity column"), but I believe I have a reproducible test case.

The script below creates a brand new table, uses dbcc checkident( , reseed, high_number) to set the identity on the new table, then inserts to that table with identity_insert on, then uses dbcc checkident( , reseed, max_table_value) to set the identity so the next insert will not violate the primary key of the table. However the next identity value is equal to the max_table_value, not one more as would be expected.

This test case works in SQL Server 2008 R2, but fails in SQL Server 20012 sp1.

I suppose you could modify the SQL Server 2012 BOL line (addition in []): "If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, [OR if you ran DBCC CHECKIDENT when the table had just been created or truncated with reseed value larger than the number of rows in the table now, and the only rows added since then were added with identity_insert on,] the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity."

But that's a mouthful, and it is a change from 2008R2, so I'd say it's a bug.
Sign in to post a comment.
Posted by TMMike on 11/8/2013 at 10:09 AM
What I think you really should do is (1) fix this bug so dbcc checkident( reseed) works like it did in 2008R2 and prior; and (2) add a new command ("dbcc checkidentx"?) that resets the identity to the same value whether the table is new/truncated or not. Then you've got backward compatibility covered and rationality going forward.
Sign in to post a workaround.