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)  

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

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)  
table
Adding a new record in the table.
  1. Insert into Employee values('Nitin',26,'IT','Mumbai')  
table
We will check the records in the table now. Execute the following query.

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

 

X

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

Start Learning Now