Subscribe For Free Updates!

We'll not spam mate! We promise.

Sep 28, 2011

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

Views:

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)  
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  
This will delete the records as well as reset the identity value :)

Plese Feel Free to Socializer This Post
SOCIALIZE IT →
FOLLOW US →
SHARE IT →

0 comments:

Post a Comment

Become a Fan

visual studio learn