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 column, Hide or Show the Solution File in Solution Explorer

• Tweet
• Share
• Share
• Share
• Share

About Admin

Beginner School is optimized for learning. This is useful for getting basic knowledge with easiest way. Tutorials, examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. With the help of other reference book and website, the motive to provide you easiest way to understand the coding, technology and other things.....

Related Post