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:

Power BI Did You Know -- Series 1 (Contact Us button)

Did you know that you can add a contact us button in your Power BI reports ? Here is how you add a contact us button in your power bi rep...