Interview Question of the Week #019 – How to Reset Identity of Table

Here is the question which I received in email:

Question: I have a table which has an identity column. I have recently deleted old and archive it to another place. Now I want to reset the identity of the table to original value of 1. How should I do that?

Answer: It is very easy to do so – you can use the command DBCC CHECKIDENT to do the same.

If a table has to start with an identity of 1 with the next insert then the table should be reseeded with the identity to 0. If identity seed is set below values that currently are in the table, it will violate the uniqueness constraint as soon as the values start to duplicate and will generate error.

DBCC CHECKIDENT (yourtable, reseed, 0)


Here is the quick video I have created to demonstrate the same example.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Script: Current IO Related Waits on SQL Server
Next Post
SQL SERVER – Introduction to Database Engine Tuning Advisor (a.k.a. DTA)

Related Posts

Leave a Reply

Menu