By using the seed value for every new record Microsoft SQL Server's identity column generates sequential values. In this post we will learn how to reseed an identity column.

Following is the command to reset the identity property :

DBCC CHECKIDENT (tablename, RESEED, reseed_value)

For example if we want to reset column identity to 10 for table Employee we will run the following command:

DBCC CHECKIDENT (Employee, RESEED, 10)

In case we want to delete rows from Employee table and restore the value to the previous value, we need to run the below query:

DELETE
FROM Employee
WHERE Employee_id >= 10 ;

DECLARE @Seed NUMERIC(10)
SELECT @Seed = MAX(Employee_id) FROM Employee ;

DBCC CHECKIDENT (Employee, RESEED, @Seed)

While using the above approach we should be careful not to set a new seed value below existing values as it will generate an error if column is a primary key.