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

1. DELETE FROM CUSTOMERS  
2. DBCC CHECKIDENT (CUSTOMERS,RESEED, 0)  

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 .

1. TRUNCATE TABLE CUSTOMERS  

TRUNCATE TABLE CUSTOMERS

This will delete the records as well as reset the identity value

Plese Feel Free to Socializer This Post

SOCIALIZE IT →

Tweet

FOLLOW US →

SHARE IT →