HomeSQL ServerHow to Reset the Value of identity Column in SQL Server ?

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 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 🙂

Leave a Reply

You May Also Like

When dealing with a relational database management system (RDBMS) like SQL Server, compatibility level is an important concept to understand....
In this blog post, let’s learn about the error message “49975 – Unable to load controller client certificate due to...
In this blog post, let’s learn about the error message “49973 – Cannot remove tempdb remote file to local tempdb...