How To Reset Identity Column Value In SQL Server

We can use the identity column to generate an autogenerated and identity value in SQL Server. It has 2 values: seed and increment. Now, the new values will be autogenerated based on these values. The syntax is like below.

IDENTITY [ (seed , increment) ]

Now, Let’s understand this concept with an example of when the reset identity column is required and how to do that.

1. Let’s create a table named Product using the below query.

CREATE TABLE Product (
Product_ID INT IDENTITY,
Product_Name VARCHAR(100),
);

2. Now, let’s insert some data into the product table using the SQL queries below.

INSERT INTO Product (Product_Name) VALUES ('DatabaseBlogs');
INSERT INTO Product (Product_Name) VALUES ('AzureLessons');
INSERT INTO Product (Product_Name) VALUES ('DocsTips');

Check: How To Check Identity Column In SQL Server

3. Now, retrieve the records using the below select query.

SELECT * FROM Product;

4. As a next step, let’s delete one record from the Product table using the below query.

DELETE FROM Product WHERE Product_ID = 2;

5. Let’s retrieve the record using the below Select query. Check out the screenshot below.

SELECT * FROM Product;

If you look closely, you will see that the Product_ID column has a gap between 1 and 3, which doesn’t look good. If we insert a new record, the Product_ID will be 4. This is where a reset of the Identity column is required so that the records we insert will be continuous without any gap.

Check: How to turn off the identity column in the SQL Server

6. Now, let us delete the odd record with Product_ID = 3, reset the IDENTITY column, and Reinsert this record.

DELETE FROM Product WHERE Product_ID = 3;

7. We need to use the DBCC CHECKIDENT command to rest the IDENTITY column value.

Syntax

DBCC CHECKIDENT ('table name', Reseed, new value);

Let us execute the below query. You can check out the below screenshot to see the output.

DBCC CHECKIDENT ('Product', RESEED, 1)

8. Now, let us insert a record using the below query.

INSERT INTO Product (Product_Name) VALUES ('DocsTips');

9. Finally, let us execute the below Select statement to retrieve the record. We got the expected output, and the Product_ID is continuous, as shown in the screenshot below.

SELECT * FROM Product;

Conclusion

Resetting identity column values in SQL Server is a powerful but potentially quite risky operation that you must carefully consider and execute. The primary method is to use the DBCC CHECKIDENT. Note that resetting an identity column impacts the data integrity and relationships in your database, so use this command with caution and take a proper backup of the data before making the changes.

You may also like following the articles below

• How to insert values into a table with an identity column in SQL Server
• How to add an identity column to an existing table in SQL Server