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

3 comments:

  1. how to reset the identity without deleting the records, so when a transuction 2 failed with identity 2, the next row have right order and inserted with identty 2

    ReplyDelete
  2. Thank you for your great work. I found the answer to my question

    ReplyDelete
  3. I remain convinced you do not discover your identity in as much as evolve into it. As you discard the image of the formed self, you allow the authentic self to emerge. According to author Neale Donald Walsch, "Your soul is who you are. Your body and your mind are what you use to experience who you are in the Realm of the Relative." fake id

    ReplyDelete