Reseed an Identity Column in SQL Server: SQL in Sixty Seconds #051

To reseed an identity column, run DBCC CHECKIDENT with the RESEED option and the value you want. Episode 51 of SQL in Sixty Seconds shows the command. It changes the value the column uses next. It does not renumber the rows that already exist.

How an Identity Column Hands Out Numbers

An identity column has a seed and an increment. The seed is the first number, and the increment is the step between numbers. The table remembers the last number it handed out. That memory is the value you change when you reseed an identity column.

Reseed an Identity Column Step by Step

First read the current value. NORESEED reports it and changes nothing. Then reseed. The script below uses a temporary table, so it leaves nothing behind. Run the whole script in one query window.

CREATE TABLE #IdentityDemo
(
    ID int IDENTITY(1,1) PRIMARY KEY,
    Col1 varchar(100)
);
INSERT INTO #IdentityDemo (Col1)
VALUES ('First'), ('Second'), ('Third'), ('Fourth'), ('Fifth');
DBCC CHECKIDENT ('#IdentityDemo', NORESEED);
DBCC CHECKIDENT ('#IdentityDemo', RESEED, 11);
INSERT INTO #IdentityDemo (Col1)
VALUES ('First'), ('Second'), ('Third'), ('Fourth'), ('Fifth');
SELECT ID, Col1 FROM #IdentityDemo ORDER BY ID;
DROP TABLE #IdentityDemo;

The first DBCC message reads current identity value ‘5’, current column value ‘5’. The final query returns ten rows.

The old rows kept IDs 1 to 5. The new rows start at 12, which is the reseed value 11 plus the increment of 1.

An Empty Table Behaves Differently

The next value depends on how the table became empty. I filled two tables with three rows each. I emptied one with DELETE and the other with TRUNCATE TABLE, then reseeded both to 100. A third table never had a row and was reseeded to 1000.

DELETE leaves the old counter in place, so the next ID is the reseed value plus the increment. TRUNCATE resets the counter to the seed. A table with no history uses the reseed value itself.

What Goes Wrong When You Reseed Too Low

I reseeded a table that held IDs 1, 2 and 3 back to 1. The next insert needed ID 2, and the primary key refused it with Msg 2627. The message ends with The duplicate key value is (2). The failed insert still used up the value 2.

Without a primary key or unique index, the same steps run without any error. That table ended up with two rows that had ID 2. Duplicate IDs are worse than the gaps you wanted to remove.

Read the Current Value Without DBCC

You do not need DBCC to look at the counter. IDENT_CURRENT returns the last value generated for a table. The view sys.identity_columns shows the seed, the increment and the last value together.

SELECT IDENT_CURRENT('dbo.IdentityDemo') AS LastValue;
SELECT name, seed_value, increment_value, last_value
FROM sys.identity_columns
WHERE object_id = OBJECT_ID('dbo.IdentityDemo');

For the table in the first example, both queries returned 16. After a TRUNCATE, last_value in sys.identity_columns is NULL until the first insert.

Find a Safe Value Before You Reseed

On a table that holds data, look at the largest ID first. Reseed to that number or higher. The next ID is then above every ID in the table, unless something inserts explicit IDs later.

SELECT MAX(ID) AS LargestId FROM dbo.IdentityDemo;

Reseed Only When You Know Why

Gaps in an identity column are normal. Rolled-back inserts and deletes leave them. SQL Server 2017 and later also has the IDENTITY_CACHE setting, which is on by default (the value reads 1). Documentation names it as a reason numbers can skip after a restart. Reseeding closes none of these gaps.

A plain DBCC CHECKIDENT ('table') only moves the value up. In my test the current value was 4 and the largest ID was 2. The command changed nothing, and the next insert got ID 5. To go lower, name the value with RESEED.

Good reasons to reseed are test data you reload and a table you emptied on purpose. On a table with data, check the maximum ID first and never go below it.

Related Reading

• DELETE, TRUNCATE and reseeding identity
• Resetting identity values for all tables
• Identity values after TRUNCATE
• Why identity values skip numbers
• Negative identity seeds and increments

Reseeding is not a way to renumber a table, it is a way to choose the next value.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.