How to reset Identity column in SQL Server

1.      Background
The purpose of this article is to describe how to reset the identity column of a table in SQL Server.
There are two ways of resetting the value of identity column in SQL Server:

·         TRUNCATE TABLE

·         DBCC CHECKIDENT WITH RESEED

 

2.     Steps to reset the Identity column:

                                i.            Reset Identity with TRUNCATE-TABLE:
TRUNCATE TABLE quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired. Hence SQL Server does not maintain identity values of a table after truncating the table.

Below is the example:

·         Table Schema:

CREATE TABLE [dbo].[City](

      [ID] [int] IDENTITY(1,1) NOT NULL,

      [City] [nvarchar](250) NULL

      ) ON [PRIMARY]

      GO

·         Data Insertion:

                  INSERT INTO City

      VALUES('Bhopal'),('Indore'),('Mumbai'),

            ('Hyderabad'),('Jabalpur'     )

·         See the data in table:
SELECT * FROM City

·         Delete the data using DELETE command:
DELETE FROM City


all the five records deleted.

·         Re- Insert the same data:
INSERT INTO City

      VALUES('Bhopal'),('Indore'),('Mumbai'),

            ('Hyderabad'),('Jabalpur'     )

·         SELECT the data:
SELECT * FROM City


Here we can see Identity value increasing after deletion.

·         Now TRUNCATE the table:
TRUNCATE TABLE City

·         Now Re-insert the same data:

·         Now check the data:

                              ii.            Reset Identity by DBCC CHECKIDENT WITH RESEED option
In case, you can’t use TRUNCATE command then you can go with DELETE command and DBCC CKECLIDENT with RESEED option.
 

·         DELETE data from table
DELETE FROM City

 

·         Now use DBCC CHECKIDENT with RESEED option to reset the identity:
DBCC CHECKIDENT ('City', RESEED, 0)

 

•  Now insert the data into table and see the result

Now Identity has been reset.
 

3.     Conclusion

          By using TRUNCATE TABLE and DBCC CHECKIDENT with RESEED, we can reset the value 
          of Identity column of a table in SQL Server.