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:
- 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.
- DBCC CHECKIDENT ([Employee], reseed, 4)
![table]()
Adding a new record in the table.
- 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.