Here’s a way to reset the value of the identity column in SQL Server . The scenario is explained below .
For example , when the table “Customers” has a identity column with the initial value of 1 and seed 1 .Each time when you start an App and perform an operation , you might want to delete all the records inside the table and perform the new inserts . ( Not the best of the methods , but the App had to do it ) .
How to reset the value of identity Column in SQL Server ?
Now Each time i wanted to have the identity value start from 1 .
The Delete statement alone is not enough to reset the identity value .
After the Deletion of the records we should execute the DBCC command along with the CHECKIDENT switch along with the table name and the seed value .
DELETE FROM CUSTOMERS DBCC CHECKIDENT (CUSTOMERS,RESEED, 0)
Here comes a better approach , instead of using the DELETE and DBCC commands , the Truncate will do the job for you .
TRUNCATE TABLE CUSTOMERS
This will delete the records as well as reset the identity value 🙂