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.

One Response

  1. SQL LIon August 20, 2011 Reply

Leave a Reply


x

Interesting BlogPosts to read

How to print title on every page in Microsoft Excel?
Most of the time we might end up having a long workbook with huge amount of data and they have lot o...
How to quickly remove multiple line breaks in Excel using VBA code?
There are times when you might copy few data from different source and you would end up getting unne...
How to change orientation of the worksheets in Microsoft Excel ?
This blog post will show the steps that are needed to change the orientation of the current workshee...