How To Reset Identity Column Value in SQL Server Table - SQL Server / T-SQL Tutorial Part 43

Let's start with understanding the Identity property first and then we can proceed to step,how can we reset it? Create a SQL Server Table with Identity Column by using below script

USE TEST
GO

CREATE TABLE dbo.Person( ID INT IDENTITY(1,1), Name VARCHAR(10))

Let's insert below records by using below statements

INSERT INTO dbo.Person(name) 

VALUES ('Aamir')

INSERT INTO dbo.Person(Name)

VALUES ('Aamir Shahzad')

Only one record will be inserted and second record will fail as the size of string is more than the size of column data type

Fig 1-Insert records in SQL Server Table

Check the Identity Value of a Column:

To check the identity value of a column, we can use below statement

--Check the Identity Value of a Column

DBCC CHECKIDENT ('dbo.Person');

Fig 2- DBCC CHECKIDENT to see the Identity Value

Even the insert was failed for second record but the identity value is incremented. If we will insert next record, the identity value will be 3 for that as we can see in Fig 3.

Here are couple of ways to reset the identity value of a column after deleting data.

Reset Identity Value by Using TRUNCATE:

If we want to delete everything from table and table does not have any foreign Key relation, we can Truncate table and that will clean the data as well set the identity value

Fig 3-Use Truncate to clean table and reset Identity Value

Reset Identity Value By Using DBCC CHECKIDENT:

If the table is in relationship with any other table (Primary-Foreign Key) relationship, we will not be able to truncate the table. In that case we have to delete the values from table by using Delete statement and then set identity value.

DBCC CHECKIDENT ('dbo.Person', RESEED, 0);

 Fig 4. Delete data from Table and use DBCC CHECKIDENT to reseed identity value

Video Demo : How to reset Identity Column Value in SQL Server Table