Monday, October 1, 2012

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
 

 

3 comments:

Here are few FREE resources you may find helpful.