SQL Server - How to find missing Identity/Sequence numbers using T-SQL?

SQL Developers, did you ever come across a situation where you need to find missing identity or sequence number for a given table? For instance, someone deleted few records from a table which has an IDENTITY column. Have you wondered how to find those missing rows? In this blogpost, I am going to explain different ways to find missing identity or sequence numbers.

First I will create some sample data for demonstration purpose.

-- Prepare Test data --
SET NOCOUNT ON
IF OBJECT_ID('dbo.TestData') IS NOT NULL    DROP TABLE dbo.TestData

CREATE TABLE dbo.TestData (

    ID INT IDENTITY(1, 1) NOT NULL
)

DECLARE @Counter INT = 1
WHILE @Counter <= 15
BEGIN
    INSERT dbo.TestData DEFAULT VALUES
    SET @Counter += 1
END
 

SELECT ID FROM dbo.TestData
 

 

 

 

  

Now lets delete some rows manualy:
-- Now delete some records (IDs)
DELETE dbo.TestData WHERE ID IN (3,7,8,10,12,13)
 
--Verifiy the data
SELECT ID FROM dbo.TestData

 

Below are three different ways to identity missing values.

 

Find missing sequence numbers using Ranking Function:

-------------------------------------------

-- Option 1: Using Ranking function

-------------------------------------------
DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

SELECT SeqID AS MissingSeqID

FROM (SELECT ROW_NUMBER() OVER (ORDER BY column_id) SeqID from sys.columns)LkUp
LEFT JOIN dbo.TestData t ON t.ID = LkUp.SeqID

WHERE t.ID is null and SeqID < @MaxID

Here is the output:

 

 -- If there are less records in sys.columns and

-- you need need larger result then use CROSS JOIN

SELECT SeqID AS MissingSeqID

FROM (SELECT ROW_NUMBER() OVER (ORDER BY c1.column_id) SeqID

FROM sys.columns c1

CROSS JOIN sys.columns c2) LkUp

LEFT JOIN dbo.TestData t ON t.ID = LkUp.SeqID

WHERE t.ID is null and SeqID < @MaxID

 

Find missing sequence numbers using CTE:

-------------------------------------------

-- Option 2: Using Common Table Expression

-------------------------------------------

DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

;WITH CTE (MissingSeqID, MaxID)

AS (

    SELECT 1 AS MissingSeqID, @MaxID

    UNION ALL

    SELECT MissingSeqID + 1, MaxID FROM CTE WHERE MissingSeqID < MaxID

    )

SELECT MissingSeqID FROM CTE

LEFT JOIN dbo.TestData t on t.ID = CTE.MissingSeqID

WHERE t.ID is NULL

GO

 

 

Find missing sequence numbers using Tally Table:
This is the most prefered way out of all the above options.

-------------------------------------------

-- Option 3: Using Tally Table

-------------------------------------------

DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

SELECT t.ID  MissingSeqID FROM dbo.Tally t

LEFT JOIN dbo.TestData td

ON td.ID = t.ID

WHERE td.ID IS NULL

AND t.ID < @MaxID