Multiple Ways to Find Missing Serial Numbers in SQL

In my previous blogs, I had mentioned that there will be many ways to solve a problem. The below is one more example. 

Often, in some tables where identity column exists, will have some missing sequences due to some data fixes. Or we may have some sequential numbers or numeric ranges in a table from which we may need to find out the missing number or ranges.

Let’s create a temporary table with values before we look into the different methods to accomplish the goal:

TestData

 

/* SOLUTION-1 – Identify only missed numbers   */

DECLARE @minID INT
DECLARE @maxID INT

SELECT @minID = MIN(id) FROM #TempData
SELECT @maxID = MAX(id) FROM #TempData

CREATE TABLE #IDSeq(id INT)

WHILE @minID < @maxID
BEGIN
    INSERT INTO #IDSeq VALUES(@minID)
    SET @minID = @minID + 1
END

SELECT S.id FROM #idseq S
LEFT JOIN #TempData TD (NOLOCK) ON S.id = TD.ID
WHERE TD.id IS NULL
ORDER BY s.id

DROP TABLE #IDSeq

Sol1

/* SOLUTION-2 – Identify the range that has missed values */
SELECT id, NextVal FROM(
SELECT id, LEAD(id) OVER (ORDER BY id) NextVal FROM #TempData)X
WHERE id+1 <> NextVal

Sol2

 

/* SOLUTION-3 – Identify the range of missed values */
Credits: Marc Gravell

SELECT (
                        SELECT ISNULL(MAX(id)+1,1) FROM #TempData WHERE id < T.id
                        )
             AS [RangeFrom],
     T.id- 1 AS [RangeTo]
FROM #TempData T WHERE T.id != 1 AND NOT EXISTS
        (
                        SELECT 1 FROM #TempData md2 where T.id = T.id- 1
        )

Sol3

/* SOLUTION-4 – Identify the missed values                 */
Credits Suprotim Agarwal

DECLARE @minimum INT
SELECT @minimum = MIN(id) FROM #TempData

;WITH Missing (missnum, maxid)
AS
(
 SELECT @minimum AS missnum, (select max(id) from #TempData)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN #TempData tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0);

Sol4


If you find any other method, please do share in the comments section.


2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s