How to Reset IDENTITY Column Values in SQL Server

Do you want to reset an identity column in your table in SQL Server so the numbers are in order?

And you want to do it without recreating the table?

In this article, I’ll show you how you can do this, and a few things to be aware of.

Let’s take a look.

Table of Contents

What is an Identity Column?

An identity column is a feature in SQL Server that lets you auto-increment the column’s value. This is helpful for primary keys, where you don’t care what the number is, as long as it’s unique.

You can specify the word IDENTITY as a property after the data type when creating or altering a table.

For example:

CREATE TABLE product (
  product_id INT IDENTITY,
  product_name VARCHAR(200),
  price INT
);

This statement will create a new table called product.

The product_id has the word IDENTITY after it, which means new records will have an automatically generated value.

Let’s see an example of this.

INSERT INTO product (product_name, price)
VALUES ('Chair', 100);

We can check the value was inserted by selecting data from the table.

SELECT product_id, product_name, price
FROM product;

product_id	product_name	price
1	Chair	100

We can insert a second row into the table to see what the product_id identity column will be set to.

INSERT INTO product (product_name, price)
VALUES ('Desk', 250);

To see the records in the table, we can select from it.

SELECT product_id, product_name, price
FROM product;

product_id	product_name	price
1	Chair	100
2	Desk	250

Without specifying the product_id in the INSERT statements, the IDENTITY feature has generated a number and populated the row. A new number is created for each row.

Why Reset an Identity Column?

So what’s the issue? You may want to reset an identity column if you delete records from the table, or if you get an error when inserting a row.

Let’s delete a record and insert a new one.

DELETE FROM product
WHERE product_id = 2;

INSERT INTO product (product_name, price)
VALUES ('Large Desk', 300);

Once we delete a row and insert a new one, here’s what our table looks like.

SELECT product_id, product_name, price
FROM product;

product_id	product_name	price
1	Chair	100
3	Large Desk	300

We can see that the new row has a product_id of 3, and not 2.

Our ID values are not in order.

So, if you want them to be in order, you’ll have to reset the identity column.

While you’re here, if you want an easy-to-use list of the main features in SQL Server, get my SQL Cheat Sheet here:

Identity Values Don’t Matter

If you’re here because you want your primary key identity values to be in numerical order without any gaps, then I would suggest it’s not necessary.

A primary key value should hold no significance to any user or application outside of the database. Its only purpose is to uniquely identify a row and therefore be used to relate to other rows in other tables.

It doesn’t matter to the database if the values are in the order of 1, 2, 3, or if there are missing values like 1, 4, 8. As long as they are unique, the database will still operate in the same way.

If someone looks at an output of a database (e.g. a report that has IDs) and wonders why they are not in order or that there are gaps, then either:

you can explain to them that the ID is OK to have gaps
if they want numbers with no gaps, you can change the query to generate a row number instead

If someone is using the identity values to determine how many rows are in a table, and the column is “skipping” values like this, then they may think there are more rows in the table than there actually are.

However, the right way to count records is to use the COUNT function, not by looking at the identity column as a row count.

So, to summarise, it doesn’t matter what the value of the identity column is. As long as it’s unique, it’s OK.

People don’t need to see the value.

Having said that, if you really want to reset the identity values (e.g. for a test you’re doing or for a university project), then you can do it in SQL Server.

Check the Current Value Using the DBCC CHECKIDENT Procedure

You can run the DBCC CHECKIDENT procedure in SQL Server to see what the current value of the identity column is. You just specify the name of the table as the parameter.

Here’s an example:

DBCC CHECKIDENT('product');

This will show you the current value of this table’s identity column.

Checking identity information: current identity value '3', current column value '3'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

The value of 3 is the most recent value of the identity column. The next row that’s inserted will have the value of 4.

So, how do we reset the value?

Reset the Identity Value Using the DBCC CHECKIDENT Procedure

If you want to reset the identity column in SQL Server, you can use the DBCC CHECKIDENT procedure with extra parameters:

DBCC CHECKIDENT ('table_name', RESEED, new_value);

Resetting our produce table to use a value of 1 is done using this command:

DBCC CHECKIDENT ('product', RESEED, 0);

However, there are existing records in the table, so if we reset it and insert a new record, there will be an error.

So, what do we do? We need to:

Delete all data from the table
Reset the identity
Re-insert all data

Our script would look like this:

DELETE FROM product;
DBCC CHECKIDENT ('product', RESEED, 0);

INSERT INTO product (product_name, price)
VALUES ('Chair', 100);

INSERT INTO product (product_name, price)
VALUES ('Large Desk', 300);

This will reset the identity value to 0, causing the two inserted rows to have an ID of 1 and 2.

The result is:

product_id	product_name	price
1	Chair	100
2	Large Desk	300

So that’s how you can reset an identity column in SQL Server.

Reset the Identity Value and Keep Table Data

What if you have a large table? It can be hard or impossible to re-insert all of the values from a script.

One way you can do this is to store the values in another table. The process would be:

Create a new table using the values from the real table
Delete the data from the real table
Reset the identity
Re-insert from the new table

Your script could look like this:

CREATE TABLE product_backup AS
SELECT product_id, product_name, price
FROM product;

DELETE FROM product;

DBCC CHECKIDENT ('product', RESEED, 0);

INSERT INTO product (product_name, price)
SELECT product_name, price
FROM product_backup
ORDER BY product_id ASC;

There are a few things to note here. The product_backup table stores the product_id, which has the gaps, so it can be used in the order by later in the script. This is important if you want the data in the same order. This may not actually be important as you can just order your data in any future queries, but it’s useful if that’s what you want.

Also, in the INSERT statement, you select from the product_backup table, but you don’t insert the product_id from that table. The identity column is generated.

Your data will look the same as the earlier example:

product_id	product_name	price
1	Chair	100
2	Large Desk	300

Reset the Identity Value: Delete vs Truncate

One issue with using this method is that the value that the CHECKIDENT procedure requires is different depending on if you TRUNCATE or DELETE.

This article here details some tests to explain this.

In short:

When you use DELETE to delete all rows in the table, the next assigned value for the identity column is the new reseed value + the current increment
When you use TRUNCATE, the next assigned value for the identity column is the initial seed value.

Take a look at the article above for more details on the tests that were run.

Conclusion

Using an identity column for an auto-generating primary key is a great feature. The values of the identity column shouldn’t matter so it doesn’t matter if they are not continuous. But if you really need to make them continuous, you can reset them without dropping and recreating the table using the steps in this article.