THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Fun with DBCC CHECKIDENT

<Denis Gobo’s mode on>

Suppose you have an empty table:

 

SELECT COUNT(*) FROM SampleTable

---

0

 

What would be the result of the following query:

 

DBCC CHECKIDENT('SampleTable', RESEED, 1)

INSERT SampleTable(j) SELECT 1

SELECT SCOPE_IDENTITY()

 

<Denis Gobo’s mode off>

 

Surprisingly enough, the answer is “it depends”. If the table is brand new, you’ll get one, if there have been inserts into the table, the answer is 2. See for yourself:

 

CREATE TABLE SampleTable(i INT IDENTITY, j INT)

GO

SELECT COUNT(*) FROM SampleTable

---

0

DBCC CHECKIDENT('SampleTable', RESEED, 1)

INSERT SampleTable(j) SELECT 1

SELECT SCOPE_IDENTITY()

---

1

DELETE FROM SampleTable;

DBCC CHECKIDENT('SampleTable', RESEED, 1);

INSERT SampleTable(j) SELECT 1;

SELECT SCOPE_IDENTITY();

---

2

 

Even more surprising is the fact that this inconsistent behavior is by design – it has been documented in MSDN:

 

http://msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx

 

quoting from this MSDN article: “If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1.”

 So far I cannot come up with a reason why such inconsistent behavior could be required. Any feedback is welcome.

Anyway, if you are unit testing a stored procedure inserting into a table with identity, this feature of DBCC CHECKIDENT may break your unit tests. The workaround that I am using in my database unit testing is simple – when I set up my test fixture, I run the following script against all the tables with identities:

 

BEGIN TRY

  INSERT INTO MySchema.MyTable DEFAULT VALUES

END TRY

BEGIN CATCH

  PRINT '1'

END CATCH

 

Published Thursday, June 26, 2008 5:51 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

andyleonard said:

"<Denis Gobo’s mode on>"

Cool! Denis has a mode!

Great article and good points, Alexander.

:{> Andy

June 26, 2008 7:16 PM
 

Denis Gobo said:

Interesting,

I always used  DBCC CHECKIDENT('SampleTable', RESEED, 0);

when I deleted from a table, this is only if I couldn't use truncate

I guess I knew this but didn't put 3 and 3 together

And yes Denis has a 'mode' the Menace mode  :-)

June 26, 2008 8:07 PM
 

Alejandro Mesa said:

Hi Alex,

This is also documented in BOL, and does not happend if you use "truncate table" statement, when it is possible.

For Denis, what about if your original seed was zero, you would have to reseed it to -1 if you want next to be zero.

AMB

June 27, 2008 8:38 AM
 

Alexander Kuznetsov said:

Hi Alejandro, thank you for the tip! BTW maybe the documentation should be updated to reflect this behavior.

June 27, 2008 9:36 AM
 

Patrice said:

Hi,

Thanks a lot for the fix, I used it for testing purpose also... This behavior is really strange. It seems like a documented bug ;)

July 28, 2008 10:39 AM
 

LeoPasta said:

Hi Alex,

Just wanted to say thanks, I was facing the same issues on my unit tests and was cursing half of the SQL Server team for this behaviour. But your workaround worked brilliantly, you saved my bacon (and lifted hundreds of curses at the same time! :-) )

March 15, 2011 5:54 AM
 

Alexander Kuznetsov said:

Patrice and LeoPasta,

You are welcome, glad you liked it.

October 12, 2011 12:09 PM
 

Nabil said:

Thanks for publishing your solution. But there is a problem with the solution ;

If the table actually accepts default values which is

"INSERT INTO MySchema.MyTable DEFAULT VALUES"

This wont work.

So we should do sth like this:

BEGIN TRY

INSERT INTO MySchema.MyTable DEFAULT VALUES;

DELETE FROM MySchema.MyTable;

END TRY

BEGIN CATCH

END CATCH

Thanks.

January 8, 2013 6:13 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement