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