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!