Friday 13 April 2012

Sql Server : How to Reset the Identity Column of a table in Sql Server

Sometimes we have situation in which we want delete all records of table and that table containts identity column as primary key and we want again insert some new records in this table ,
so in this case identity column does not start with 1 but it start with last number that we had before deleted records.
To avoid this situation we need to reset identity column.
After reset identity column it will start with 1.

Here are sample example for that :

We have one table "product_master" , in this table we have one identity column "product_id".
Now we inserted 3 records into this , so the table entries look like this :


After Delete all records using this query :
     delete from product_master

Now table is empty.

After delete now if we insert new record. The newly added record have product_id value 4. because identity column does not reset.You can see this in below image


To reset Identity column Here are Syntax:
Syntax :
    DBCC CHECKIDENT('TableName', RESEED, 0)

Now again delete all records and execute this command :
    DBCC CHECKIDENT('product_master', RESEED, 0)

This command resent the identity of given table.

Now again you insert one record this record has product_id value 1. You can see this in below image.

FDTBBTJNWV8H

1 comment:

  1. Very handy, especially for automated testing.

    ReplyDelete