How to Reseed Identity Column in SQL Server

If you wish to reseed an identity column of a table in SQL Server then this will help you in reseeding your table’s identity column. Suppose you have a table, Employee, having three records and you have to truncate the table at this stage. The identity field resets to Zero now, once you try to insert a new record EmpID will be assigned a value of 1. But what if you want your next record to have an EmpID as 4?

In such scenario you can use the following query:

  1. DBCC CHECKIDENT (tablename, reseed, 4)  

It has three records with last EmpID as 3. Let us truncate the table.

If we try to insert a new record in this table then EmpID would have a value as 1. We will reseed it to 4 or any other number using the following SQL snippet.
  1. DBCC CHECKIDENT ([Employee], reseed, 4)  
Adding a new record in the table.
  1. Insert into Employee values('Nitin',26,'IT','Mumbai')  
We will check the records in the table now. Execute the following query.

We have the new record having EmpID as 4 instead of 1. This is how we can reseed identity column in SQL Server.



Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now