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 replies on “Re-setting Identity Column in SQL Server”
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.
it”s fine, but how to suppress the message returning from calling the function…
Hi James
Thanks for you article.
it was really very useful to us.
what will happen with the following command. Is it to set default seed value to zero?
DBCC CHECKIDENT (table_name, RESEED)
Hi James Crowley,
Thanks a lot.
You have saved my time.
Regards
Jegan.J