RESET identity Columns in SQL Server

Introduction:

During application development, we input dummy data into database for testing purposes. But then we come to the point where we want all records of the table to be deleted and also want to start the identity column values from 0. For this, we delete existing data using the Truncate command. This will delete data from table and also reset the identity column value to 0.


Solution:
Truncate table [table_name]
   for example:
         Truncate table ColonyName



But the Truncate command fails to delete the data if there is a relationship given to the table and the identity column is not reset.

In this case, first you need to delete data from the child and the master table.


After that, execute this command and it will reset your identity column to 0.

DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])
-- for example

DBCC CHECKIDENT('ColonyName', RESEED, 0)


Note:
In case of, If you want reset identity value with  no data loss:

No Truncate command is used.
Execute following command:

DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])
-- for example

Here  new_reseed_value should be the last identity column value:











here ColonyID is identity Column and I want reseed the value of this column using the following command:

DBCC CHECKIDENT('ColonyName', RESEED, 5)


Reset Identity columnHide or Show the Solution File in Solution Explorer
Oldest

2 comments

Click here for comments
22 September 2017 at 04:53 ×

No more live link in this comments field

Reply
avatar
soumya
admin
29 May 2018 at 00:12 ×

No more live link in this comments field

Reply
avatar

Conversion Conversion Emoticon Emoticon

:)
:(
=(
^_^
:D
=D
=)D
|o|
@@,
;)
:-bd
:-d
:p
:ng