DBCC CHECKIDENT Sets Identity to 0

Question

I'm using this code to reset the identity on a table:

DBCC CHECKIDENT('TableName', RESEED, 0)

This works fine most of the time, with the first insert I do inserting 1 into the Id column. However if I drop the db and recreate it (using scripts I've written) and then call DBCC CHECKIDENT, the first item inserted will have an ID of 0.

Any ideas?

EDIT: After researching I found out I didn't read the documentation properly: http://msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx - "The current identity value is set to the new_reseed_value. 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. "

Answer 1

Change statement to

  DBCC CHECKIDENT('TableName', RESEED, 1)

This will start from 2 (or 1 when you recreate table), but it will never be 0.

Answer 2

As you pointed out in your question it is a documented behavior. I still find it strange though. I use to repopulate the test database and even though I do not rely on the values of identity fields it was a bit of annoying to have different values when populating the database for the first time from scratch and after removing all data and populating again.

A possible solution is to use truncate to clean the table instead of delete. But then you need to drop all the constraints and recreate them afterwards

In that way it always behaves as a newly created table and there is no need to call DBCC CHECKIDENT. The first identity value will be the one specified in the table definition and it will be the same no matter if you insert the data for the first time or for the N-th

Answer 3

See also here: http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/06/26/fun-with-dbcc-chekident.aspx

This is documented behavior, why do you run CHECKIDENT if you recreate the table, in that case skip the step or use TRUNCATE (if you don't have FK relationships)