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 isnull 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 isnull 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 isNULL
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
 

 

5 comments:

  1. Hi,
    DELETE dbo.TestData WHERE ID IN (3,7,8,10,12,13)

    In the above statement if i include 15 say DELETE dbo.TestData WHERE ID IN (3,7,8,10,12,13,15)

    The query you used didn't find the 15...since the max value now get changed to 14... or if we delete last 5 records then also we are not able to trace out the records

    ReplyDelete
  2. Good catch Krathi... I guess it is a bug. You can try following queries to initialize @MaxID:


    DECLARE @MaxID INT = IDENT_CURRENT('dbo.TestData')
    SELECT @MaxID

    --OR--

    DECLARE @MaxID INT = (SELECT CAST(last_value AS INT) FROM sys.identity_columns WHERE object_id = object_id('dbo.TestData'))
    SELECT @MaxID

    ReplyDelete
  3. excellent piece of information, I had come to know about your website from my friend kishore, pune,i have read atleast 8 posts of yours by now, and let me tell you, your site gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanx a lot once again, Regards, Difference sql and tsql




    ReplyDelete

Note: Only a member of this blog may post a comment.