How DBCC CHECKIDENT Works - How to Reset identity seed after deleting records in SQL Server

Scenario I faced

I have inserted records into a SQL Server database table. 

The table had a primary key defined and the auto increment identity seed is set to “Yes”.  So now whenever I do an insert in DB my primary key ID starts with number 1,2,3 and so on.

Now say I have total 100 records and I delete all the records, in this case when I insert new records its primary key ID starts with 101, where as I want it to reset to 1. 

So after deleting all records from SQL Server data table, I wanted to reset identity seed to 0, so my next insert should have primary key as 1,2,3 and so on.

Use following Command to Reset SEED

DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}]) [ WITH NO_INFOMSGS ]

Example : 

  DBCC CHECKIDENT ('[TableName]', RESEED, 0); GO

Above example will Reset Identity value to initial value i.e. new entry will start from 1

In case we want to Force Identity value to set to Some specific number we use it as follows DBCC 







CHECKIDENT ('[TableName]', RESEED, 10);

So what is the Seed value and when we cannot reset it

The seed value is the value inserted into an identity column for the first row loaded into the table. All subsequent rows contain the current identity value plus the increment value where current identity value is the last identity value generated for the table or view.

You can't use DBCC CHECKIDENT for the following tasks:

• We cannot Change the original seed value specified for an identity column when the table or view was created.

• Cannot be used to Reseed existing rows in a table or view.

To change the original seed value and reseed any existing rows, we need to drop the identity column and recreate it specifying the new seed value. When the table contains data, the identity numbers are added to the existing rows with the specified seed and increment values. The order in which the rows are updated isn't guaranteed.