I have a small problem. I was rolling out new changes to our production database. This rollout required me to delete a few tables entirely, reseed them with a "zero" value and when the tables would get repopulated, the identity values would restart from zero. However, that did not happen all my tables have different identity values (depending on what was the last identity value before they were entirely deleted). For example, table A has the first identity value of 6437 and it continues sequentially.
Table B for example starts with 335 identity value now and so on.
What didnt my reseed script work? Please see below my code for deleting & reseeding.
Using SQL Server 2005 Standard Edition.
asked Dec 16, 2009 at 01:50 PM in Default
I would suggest TRUNCATE table rather then DELETE.
I beleive it is a flaw/limitation in DBCC CHECKIDENT.
I think this is ourr explanation...
From Books Online:
I don't think that the issue has to do with delete v. truncate. I created the following test script, which can't use the truncate due to the FK constraint to mimic the OP.
I ran this, it worked and reseeded the table correctly:
This was the dbcc output:
Beginning execution loop Batch execution completed 1000 times. Checking identity information: current identity value '1000', current column value '0'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
answered Dec 16, 2009 at 05:48 PM
Just a thought - Did you run those statements in the correct database?
You may have deleted from database 'Database', but the reseed would be run in the current database - which may be different!
answered Dec 16, 2009 at 02:02 PM
Kev Riley ♦♦