How To Reset Identity Column Values In SQL Server

Rakesh Kalluri

6 Comments

Join the conversation! Your thoughts help the community grow.

Sibi Babu3y ago
In your solution where is the solution for resetting the Identity? You have shown every query for insert and delete. No solution for resetting the count from 1
hicham essaidi5y ago
Some thing like this will do the job, just make sure to use it as a trigger or something: delete from EMP where ID > 3; if exists (select * from EMP) begin declare @lastID int; set @lastID = (select top 1 ID from EMP order by ID desc); end else begin set @lastID = 1; end DBCC CHECKIDENT ('EMP', RESEED, @lastID)
Christian Campos7y ago
The problem is that SQL Server adds 1000 (or 10000 on bigint) in int identity column when the service restart.
Ismail Hakki Sen12y ago
thanks but what if we have thousand records? we can't delete every rows and insert them again!
- Diomedes Domínguez7y ago
  I suggest you to create a temporal table with all the values of your main table except for the Identity field, truncate the main table and re-insert the values from your temporal one.
- Diomedes Domínguez7y ago
  Also, if you cannot truncate your main table, delete it and reseed it to 0 before reinserting the data.

Introduction

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.

CREATE TABLE dbo.Emp  
(  
ID INT IDENTITY(1,1),  
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.

DBCC CHECKIDENT ('Emp')

error

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

INSERT INTO dbo.Emp(Name)  
VALUES ('Kalluri')  
  
SELECT * FROM Emp

id name

Step 4. Reset the identity column value.

DELETE FROM EMP WHERE ID=3  
  
DBCC CHECKIDENT ('Emp', RESEED, 1)  
  
INSERT INTO dbo.Emp(Name)  
VALUES ('Kalluri')  
  
SELECT * FROM Emp

id name table