Reset Identity value of a column in SQL Server

Reset Identity value of a column in SQL Server

You can reset the identity of a column in SQL Server table. Identity column means whose value increase automatically whenever you insert a record. But when you delete all the records from the table and insert a new record then value of identity column will be one(increment seed) more than the last value not starting from one. In this situation we want to start value from 1 or any another number after deleting the data. To reset the identity value use the below command

Suppose I have a table whose name is Login then to reset identity I can use this command

DBCC CHECKIDENT ([Login], RESEED, 0)

To Check identity value use the below command

DBCC CHECKIDENT ([Login], NORESEED)

If you are using an identity column in SQL Server tables, you can set the next insert value to whatever value you want.

To set the value of the next ID to be 100, you can use this command:

DBCC CHECKIDENT ([Login], RESEED, 99)

Note that the next value will be whatever you reseed with here we use + 1, so in this case I set it to 99 so that the next value will be 100.