Reseed table’s IDENTITY column value
There are times when you want to reseed or reset the value of an identity column in a table.
When you delete a lot of records from a table and then try to insert more records. You would expect the identity values to start after the max value present after delete. But it preserves the max value ever present in the table and continues from there.
Or when you delete all records form a table and want to reseed the identity column value to start from afresh 1.
All you need is following statement, syntax:
DBCC CHECKIDENT (TableNameWithSingleQuotes, reseed,seedValue);
-- Example: DBCC CHECKIDENT (Person.Contact, reseed, 100);
This will start assigning new values starting from 101. But make sure that there are no records that have value greater than 100.
If all records are deleted from the table and you want to reseed it to 0 then you can also TRUNCATE the table again. This will reset the IDENTITY column as per the DDL of that table.
More on DBCC CHECKINDENT on MS BOL: http://msdn.microsoft.com/en-us/library/ms176057.aspx