Gaps in an Identity Column: Find Missing Identity Values

Gaps in an identity column are normal, and one window function finds every one of them. The harder part is knowing why the numbers vanish. A gap does not mean the data is damaged.

Why Identity Numbers Go Missing

An identity value is handed out before the row is safe. If the row never arrives, the number is not given back. Four things leave a gap. A delete removes the row but not its number. A rolled back insert burns a number. An insert that fails, for example on a unique key, burns one too. And SQL Server caches blocks of values in memory. An unexpected stop or a failover can skip the rest of a block.

The first three are easy to reproduce. The demo below makes all of them on a small ticket table. The unique constraint on Code gives the failed insert something to fail on.

IF DB_ID(N'GapFinderDemo') IS NULL CREATE DATABASE GapFinderDemo;
GO
USE GapFinderDemo;
GO
DROP TABLE IF EXISTS dbo.Tickets;
CREATE TABLE dbo.Tickets (TicketID int IDENTITY(1,1) PRIMARY KEY, Title nvarchar(40) NOT NULL, Code char(4) NOT NULL, CONSTRAINT UQ_Tickets_Code UNIQUE (Code));
INSERT INTO dbo.Tickets (Title, Code)
SELECT TOP (20) N'Ticket ' + CAST(n AS nvarchar(10)), CAST(1000 + n AS char(4))
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects) AS x;
GO
DELETE FROM dbo.Tickets WHERE TicketID IN (1, 6, 7, 8, 14);
BEGIN TRANSACTION;
INSERT INTO dbo.Tickets (Title, Code) VALUES (N'Rolled back', N'9001');
ROLLBACK TRANSACTION;
GO
INSERT INTO dbo.Tickets (Title, Code) VALUES (N'Duplicate code', N'1002');
GO
INSERT INTO dbo.Tickets (Title, Code) VALUES (N'Kept', N'9002');
GO
BEGIN TRANSACTION;
INSERT INTO dbo.Tickets (Title, Code) VALUES (N'Rolled back again', N'9003');
ROLLBACK TRANSACTION;

The duplicate insert fails with this message. The statement is rejected, and its identity number is gone.

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

Now the table holds ticket numbers 2 to 5, 9 to 13, 15 to 20 and 23. The numbers 21 and 22 went to the rolled back and the failed insert. Number 24 went to the last rollback.

Find the Gaps as Ranges

To find gaps in an identity column, the LEAD function puts each ticket number next to the one after it. When the difference is more than one, the numbers between them are missing. The query reports each run of missing numbers as one row. A sentinel row with the value 0 catches a gap at the start. The first ticket could have been deleted.

SELECT t.TicketID + 1 AS GapStart, t.NextID - 1 AS GapEnd, t.NextID - t.TicketID - 1 AS MissingCount
FROM (SELECT TicketID, LEAD(TicketID) OVER (ORDER BY TicketID) AS NextID
      FROM (SELECT TicketID FROM dbo.Tickets UNION ALL SELECT 0) AS ids) AS t
WHERE t.NextID - t.TicketID > 1
ORDER BY GapStart;

Four gaps, as expected. The sentinel works for an identity that starts at 1. For another seed, use the seed minus the increment. The query reads the table once, and the primary key supplies the order. That keeps it cheap on a large table.

Do Not Forget the Tail

A gap after the highest stored number has no next row to compare with, so LEAD cannot see it. Compare the last number issued with the highest number stored.

SELECT IDENT_CURRENT(N'dbo.Tickets') AS LastIdentityIssued, MAX(TicketID) AS HighestStored FROM dbo.Tickets;

The table issued 24 and stored 23, so number 24 is a gap in the identity column at the tail.

List Every Missing Number

Sometimes you need each value and not a range. GENERATE_SERIES builds a list of numbers from 1 to the last one issued. A left join then keeps the numbers with no matching ticket. This function needs SQL Server 2022 and a database at compatibility level 160 or higher.

SELECT g.value AS MissingID
FROM GENERATE_SERIES(1, CONVERT(int, IDENT_CURRENT(N'dbo.Tickets'))) AS g
LEFT JOIN dbo.Tickets AS t ON t.TicketID = g.value
WHERE t.TicketID IS NULL
ORDER BY g.value;

The list holds eight numbers, and it includes the tail. On an older version, use the range query and expand the ranges yourself. A popular older method cross joins sys.columns with itself and numbers the rows. That list has a fixed size. In this demo database it has about 1.6 million rows, and the exact number depends on the build. The method cannot find gaps above that number. The range query has no such limit.

Why a Jump of 1000 Appears

A big jump after a restart, such as 1,000 or more, comes from the identity cache. SQL Server keeps a block of values ready, and an unexpected stop or a failover can lose the unused part. SQL Server 2017 added a database setting to turn the cache off. The statement below only changes the demo database, and it takes effect at once.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF;
SELECT name, value FROM sys.database_scoped_configurations WHERE name = N'IDENTITY_CACHE';

The query returns the value 0 for the setting. That removes the cache gaps, and every other gap stays. Rollbacks and failed inserts still burn numbers. Turning the cache off also can cost insert speed on busy tables, so test it first.

You could argue that you should close the gaps. Do not. An identity column is a row label, not a count, and renumbering rows breaks every table that points to them. Fix the gaps only when a business rule demands numbers without holes. Then use a sequence table with its own locking, and accept the cost. DBCC CHECKIDENT: Check and Fix the Next Identity Value covers the counter behind these gaps and the reseed trap.

What to Remember

Find gaps in an identity column with LEAD for the ranges and IDENT_CURRENT for the tail. Use GENERATE_SERIES when you need single values on SQL Server 2022. Expect gaps from deletes, rollbacks, failed inserts and the cache, and treat them as normal. Remove the demo database when you finish.

USE master;
GO
ALTER DATABASE GapFinderDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE GapFinderDemo;

A gap in an identity column is not damage, it is a number that was spoken for and never used.

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