How To Reset Identity Column Values In SQL Server

In this blog, you will learn how to reset an Identity Column Value in SQL Server.

An Identity column in SQL Server can be used for generating identity values of a table. SQL IDENTITY property on a column is used to generate an IDENTITY column. The Identity column takes two values, seed and increment. Each new value is generated based on the current seed & increment. 
  1. IDENTITY [ (seed , increment) ]  
Here seed is the value that is used for the very first row loaded into the table and increment is the incremental value that is added to the identity value of the previous row that was loaded.
 
Here is the sample demonstration for resetting identity column values in SQL Server.

Step 1
 
Create a table.
  1. CREATE TABLE dbo.Emp  
  2. (  
  3. ID INT IDENTITY(1,1),  
  4. Name VARCHAR(10)  
  5. )  
Step 2
 
Insert some sample data.
  1. INSERT INTO dbo.Emp(name)   
  2. VALUES ('Rakesh')  
  3. INSERT INTO dbo.Emp(Name)  
  4. VALUES ('Rakesh Kalluri')  
build status
When we run the above query, the second Insert statement will fail because of the varchar(10) length.

Step 3
 
Check the identity column value.
  1. DBCC CHECKIDENT ('Emp')  
error
Even though the second insert failed but the identity value is increased, if we insert another record, the identity value is 3.
  1. INSERT INTO dbo.Emp(Name)  
  2. VALUES ('Kalluri')  
  3.   
  4. SELECT * FROM Emp  
id name

Step 4
 
Reset the identity column value.
  1. DELETE FROM EMP WHERE ID=3  
  2.   
  3. DBCC CHECKIDENT ('Emp', RESEED, 1)  
  4.   
  5. INSERT INTO dbo.Emp(Name)  
  6. VALUES ('Kalluri')  
  7.   
  8. SELECT * FROM Emp  
id name table