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 an 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.
Like this
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 🙂
