Wednesday, October 11, 2017

Two ways to reset the identity of a table in sql server.

I know of the following two ways to reset the identity of a table in sql server.

You often tend to use dummy data during the course of your development.  If you have identity columns in your table, the identity number grows. 
So you might want to reset the identity to 0 before you go to production.

This can be done in two ways.

First way is to use the DBCC CHECKIDENT command.

To check what the next identity number is going to be   you can use the below command
DBCC CHECKIDENT (tablename)
This will give you a message – sample below

Checking identity information: current identity value '746', current column value '746'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


If you want to reset the identity number to 0 you can use the below command

DBCC CHECKIDENT (tablename, Reseed, 0)

Second way is to use the Truncate command.
We always tend to use the delete command for deleting records.  This is oggd when you have child relationships.

You can use Truncate table tablename command after a delete command.  Truncate command will ensure that the identity of the table is also reset.

Do you know of any other ways to achieve this ?

No comments:

Free Databases and Data Visualisations course for kids Week 6

Week 6 update: Yesterday the class was a bit different because it started off with a Database Fundamentals test.  There were 20 questions ...