How To Reset Identity Column Values 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. 

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.

Name VARCHAR(10)  

Step 2

Insert some sample data.

INSERT INTO dbo.Emp(name)   
VALUES ('Rakesh')  
INSERT INTO dbo.Emp(Name)  
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.



Even though the second insert failed but the identity value is increased, if we insert another record, the identity value is 3.

INSERT INTO dbo.Emp(Name)  
VALUES ('Kalluri')  

id name

Step 4

Reset the identity column value.

INSERT INTO dbo.Emp(Name)  
VALUES ('Kalluri')  

id name table