Reset or reseed seed value of Identity column in SQL Server

DBCC CHECKIDENT is used to reseed value of Identity column.

1.   Create a table and insert few values.

CREATE TABLE Test_Identity
(
   Id INT IDENTITY(1,1),
   value INT
)
GO
INSERT INTO Test_Identity(value) VALUES(123),(456),(789)

2.    See the result in table

SELECT * FROM Test_Identity

As you see three rows are inserted. Now, lets delete this rows. Note that deleting rows from table does not reset identity value.

DELETE Test_Identity

3.     Now, lets reseed the seed value

DBCC CHECKIDENT("Test_Identity",RESEED,0)

First parameter is the table name, Second parameter is RESEED since we want to reset the seed value, and third parameter is the new seed value. Since value is 0, new row inserted in the table will have value of 1.

Lets perform an insert and check it.

INSERT INTO Test_Identity(value) VALUES(123),(456),(789)
GO
SELECT * FROM Test_Identity

As you can see the seed value is reset.