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]()