Resetting the Identity Counter in SQL Server
Sometimes we want to reset the autoincrement identity field in an SQL database table. This might be done when deleting test data from a database, and moving the database to a production server. When doing this, it is nice to have the identity field start at 1 (one) again.
The SQL Server command to do this on a table called “tblUsers” would be:
exec DBCC CHECKIDENT (tblUsers, RESEED, 1)
This works when there are no rows in the table. If there are existing rows in the table, the reseed value has to be equal to of greater than the maximum existing identity value in the table. In this case, the next identity value would be the reseed value + 1.
More information can be found at the DBCC CHECKIDENT page from MSDN.
Advertisement
