DBCC CHECKIDENT: Check and Fix the Next Identity Value

DBCC CHECKIDENT reports the next identity value of a table, and it can change that value. The command is short, and the results depend on one detail: whether the table was ever emptied with TRUNCATE.

What the Identity Value Is

An identity column gets its next number from a counter that SQL Server stores with the table. The counter is separate from the rows. Deleting rows does not move it back, and a failed insert still uses a number. Two values matter. The current identity value is the last number handed out. The current column value is the highest number found in the column.

The demo database holds a ticket table with a primary key. The first script creates it and inserts five rows. It is safe to run twice, and the cleanup at the end drops the database.

IF DB_ID(N'CheckIdentDemo') IS NULL CREATE DATABASE CheckIdentDemo;
GO
USE CheckIdentDemo;
GO
DROP TABLE IF EXISTS dbo.Tickets;
CREATE TABLE dbo.Tickets (
    TicketID int          IDENTITY(1,1) NOT NULL CONSTRAINT PK_Tickets PRIMARY KEY,
    Title    nvarchar(60) NOT NULL
);
INSERT INTO dbo.Tickets (Title)
VALUES (N'Reset password'), (N'Printer offline'), (N'New laptop'), (N'Badge request'), (N'VPN access');

NORESEED Reports and Changes Nothing

DBCC CHECKIDENT with NORESEED only reads. It prints both values and leaves the counter alone. Run it first, every time, before you decide to change anything.

DBCC CHECKIDENT (N'dbo.Tickets', NORESEED);

Both values are 5, so the next ticket gets number 6. Now delete the last two tickets and ask again.

DELETE FROM dbo.Tickets WHERE TicketID IN (4, 5);

DBCC CHECKIDENT (N'dbo.Tickets', NORESEED);

The identity value is still 5, and the highest ticket is now 3. The next ticket gets number 6, and numbers 4 and 5 stay unused. This gap is normal. It is not a fault, and DBCC CHECKIDENT does not fill it.

DBCC CHECKIDENT With No Option

Without an option, DBCC CHECKIDENT fixes the counter only when it is lower than the highest value in the column. The command then sets the counter to that highest value. When the counter is already higher, as it is here, nothing changes.

DBCC CHECKIDENT (N'dbo.Tickets');

The message shows the same 5 and 3. The next ticket still gets number 6.

INSERT INTO dbo.Tickets (Title) VALUES (N'Monitor flicker');
SELECT TicketID, Title FROM dbo.Tickets ORDER BY TicketID;

RESEED and the Duplicate Key Error

RESEED sets the counter to a value you choose. The next row gets that value plus the increment. With an increment of 5, that is the reseed value plus 5. A value below the highest ID is the classic mistake. The demo reseeds to 2, and the next insert collides with ticket 3.

DBCC CHECKIDENT (N'dbo.Tickets', RESEED, 2);
INSERT INTO dbo.Tickets (Title) VALUES (N'Too low');

Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK_Tickets'. Cannot insert duplicate key in object 'dbo.Tickets'. The duplicate key value is (3).
The statement has been terminated.

The primary key stopped the insert, which is what it is for. Without a key or a unique index, SQL Server accepts the duplicate. The failed insert still used number 3, so the counter now reads 3 while the highest ticket is 6. The version without an option repairs that.

DBCC CHECKIDENT (N'dbo.Tickets');
SELECT IDENT_CURRENT(N'dbo.Tickets') AS CounterAfterFix;

The Messages tab of the first statement shows what the repair found.

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

The function IDENT_CURRENT shows 6 after the fix, so the next ticket gets number 7. You can also give RESEED the highest value yourself. The no-option form needs no number, which makes it the safer choice.

DELETE Versus TRUNCATE

The two ways of emptying a table treat the counter differently. The older post SQL SERVER – DELETE, TRUNCATE and RESEED Identity covers the same ground. DELETE leaves it where it was. TRUNCATE TABLE sets it back to the seed. The next script empties one table of each kind and adds a row to both.

DROP TABLE IF EXISTS dbo.AfterDelete, dbo.AfterTruncate;
CREATE TABLE dbo.AfterDelete (ID int IDENTITY(1,1) PRIMARY KEY, Note nvarchar(20) NOT NULL);
CREATE TABLE dbo.AfterTruncate (ID int IDENTITY(1,1) PRIMARY KEY, Note nvarchar(20) NOT NULL);
INSERT INTO dbo.AfterDelete (Note) VALUES (N'a'), (N'b'), (N'c');
INSERT INTO dbo.AfterTruncate (Note) VALUES (N'a'), (N'b'), (N'c');
DELETE FROM dbo.AfterDelete;
TRUNCATE TABLE dbo.AfterTruncate;
INSERT INTO dbo.AfterDelete (Note) VALUES (N'new');
INSERT INTO dbo.AfterTruncate (Note) VALUES (N'new');
SELECT N'DELETE' AS EmptiedWith, ID AS FirstNewID FROM dbo.AfterDelete
UNION ALL
SELECT N'TRUNCATE', ID FROM dbo.AfterTruncate;

A reseed also behaves differently after a TRUNCATE. On a table that has held rows, the next row gets the reseed value plus the increment. On a table that was truncated and has no row since, the next row gets the reseed value itself. The next script empties both tables again and reseeds both to 50. WITH NO_INFOMSGS hides the status lines.

DELETE FROM dbo.AfterDelete;
TRUNCATE TABLE dbo.AfterTruncate;
DBCC CHECKIDENT (N'dbo.AfterDelete', RESEED, 50) WITH NO_INFOMSGS;
DBCC CHECKIDENT (N'dbo.AfterTruncate', RESEED, 50) WITH NO_INFOMSGS;
INSERT INTO dbo.AfterDelete (Note) VALUES (N'new');
INSERT INTO dbo.AfterTruncate (Note) VALUES (N'new');
SELECT N'DELETE' AS EmptiedWith, ID AS FirstNewID FROM dbo.AfterDelete
UNION ALL
SELECT N'TRUNCATE', ID FROM dbo.AfterTruncate;

The off-by-one is easy to miss. If the first row must be 1000 on a table that has held rows, reseed to 999.

Who Can Run DBCC CHECKIDENT

The command needs ownership of the table, or membership in db_owner, db_ddladmin or sysadmin. A user with only SELECT and INSERT is refused, even for the read-only form. This script creates a database user without a login and tries both forms.

CREATE USER ReportReader WITHOUT LOGIN;
GRANT SELECT, INSERT ON dbo.Tickets TO ReportReader;
EXECUTE AS USER = N'ReportReader';
DBCC CHECKIDENT (N'dbo.Tickets', NORESEED);
DBCC CHECKIDENT (N'dbo.Tickets', RESEED, 600);
REVERT;

Msg 2557, Level 14, State 5, Line 4
User 'ReportReader' does not have permission to run DBCC CHECKIDENT for object 'Tickets'.
Msg 2557, Level 14, State 5, Line 5
User 'ReportReader' does not have permission to run DBCC CHECKIDENT for object 'Tickets'.

Adding the user to db_ddladmin lets both commands run. A reseed changes how numbers are handed out for every later insert, so treat the permission with care.

Do You Need It at All?

You could argue that you never need DBCC CHECKIDENT. Gaps are harmless, and an identity value is not a count. That is true for most tables. The command earns its place in two cases. One is a counter that sits below the highest ID after a reseed. The other is a test table that you empty and want to start from 1 again.

Raising the counter needs no help. Inserting an explicit ID with SET IDENTITY_INSERT ON moves the counter up to that ID. An explicit ID below the counter does not lower it. In the demo, an explicit ID of 500 moved the counter to 500. A later explicit 10 left it there.

What to Remember

Run DBCC CHECKIDENT with NORESEED first. Compare the two values it prints. Use the no-option form when the counter is below the column. Use RESEED only when you know the number you want. Remember that a reseed after a TRUNCATE and a reseed after a DELETE start one apart.

Clean up when you finish. The script drops the whole demo database.

USE master;
GO
IF DB_ID(N'CheckIdentDemo') IS NOT NULL
BEGIN
    ALTER DATABASE CheckIdentDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE CheckIdentDemo;
END;

An identity gap is not a bug, it is a record of what was deleted.

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