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 insert bullets in to cells in Microsoft Excel ?
Do you want to insert bullets in cells of Microsoft Excel?. Here's how you can do it. How to insert ...
How to resize print area and fit to Single page in Excel 2016 ?
When you try to print a worksheet in Microsoft Excel 2016 , you may notice that the content might ov...
Event on Building intelligent bots with the Microsoft Bot Framework in Manchester
Want to learn about building intelligent bots using Microsoft's Bot Framework. Here's an interesting...