Post
Ask Question

How to Reset Identity Column Values in Sql Server

Here is the sample demonstration for reset identity column value:

Step 1: Create 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 above query the second insert statement will failed because of varchar(10) length.

Step 3: Check the identity column value.
  1. DBCC CHECKIDENT ('Emp')  
error
Even second insert was failed but the identity value is increased .if we insert the 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
Next Recommended Reading Reset Identity Column Value in SQL Server