How to reset the value of identity Column in SQL Server ?

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 ) .

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 :)

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

2 Responses

Leave a Reply