Re-setting Identity Column in SQL Server

Discovered something new today – normally I’d just use the TRUNCATE TABLE command in order to reset an identity column in a table within SQL Server. However, SQL Server doesn’t let you do this if you’ve got foreign key constraints pointing at the table; so instead, I deleted all the rows using a standard DELETE statement, and then reset the identity with the following command:

DBCC CHECKIDENT (table_name, RESEED, new_reseed_value)

According to the docs, “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. If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.”

So, as we’ve already had rows in the table, I called the function with new_reseed_value  to zero – hence the next row inserted into the table gets an identity of “1”. Nice!

5 thoughts on “Re-setting Identity Column in SQL Server”

  1. I found it irritating that the new identity value is different depending on whether any rows were previously inserted. Still, it”s a useful feature anyway.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.