We are working on a big enterprise solution where many tables, Stored Procedures, and Triggers are used in MS SQL Server. Most of the tables have “Auto Increment” columns used as Primary Key columns.
It so happens that once in a while we want to clean data in a table, but when we enter new details, PK value starts from the MAX(). This poses serious problems for us as some of the values are already in use by other tables. So, once we delete all the records in the table we reset the counter back to zero by running the below command:
DBCC CHECKIDENT('<Table Name>', RESEED, <COUNTER Value>) ;
“Table Name” has to be replaced by the actual table name and the “COUNTER Value” has to be replaced with the increment value desired. For example, if you want to make Auto Increment value start from 9 in Employee table, SQL statement should look like the below:
DBCC CHECKIDENT('employee', RESEED,8) ;
‘8’ in the above denotes the MAX (Auto Increment Column value). When a new record is inserted in the employee table, that record will have the ID 9 (8 + 1).