Thursday, April 17, 2008

Script To Reseed Identity Columns In SQL Server

At times we need to reset seed value of identity column of SQL Server tables. This is mainly required when we need to clean test data from identity columns tables and start inserting fresh data from the beginning.

You can use DBCC CHECKIDENT command to reseed or reset seed value of a identity column. For example, the following command resets seed of customer table 0.


After reseed of the identity column, the following INSERT statements uses reseed value + 1. If you INSERT a record in to table Customer after executing the above reseed statement, the newly inserted record gets 1 as identity value.