How to find the missing numbers in a series?

To demonstrate this, we will first create a test data number sequence from 1 to 25 with some missing numbers in the sequence.

--create test data
CREATE TABLE NUMBERS (
   ID INTEGER
)
GO

INSERT INTO NUMBERS VALUES(1);
INSERT INTO NUMBERS VALUES(2);
INSERT INTO NUMBERS VALUES(3);
INSERT INTO NUMBERS VALUES(5);
INSERT INTO NUMBERS VALUES(8);
INSERT INTO NUMBERS VALUES(11);
INSERT INTO NUMBERS VALUES(12);
INSERT INTO NUMBERS VALUES(17);
INSERT INTO NUMBERS VALUES(19);
INSERT INTO NUMBERS VALUES(20);
INSERT INTO NUMBERS VALUES(21);
INSERT INTO NUMBERS VALUES(24);
INSERT INTO NUMBERS VALUES(25);
GO

Solution: The first step is to generate all the numbers from the lowest number till the highest number given in the series. There are several ways to generate the sequence numbers. We will use the recursive CTE to generate the number sequence for this example. Then find the missing number in the sequence by doing an except.

DECLARE @min_num INT = (SELECT MIN(ID) FROM NUMBERS)
DECLARE @max_num INT = (SELECT MAX(ID) FROM NUMBERS)

;WITH SERIES(NUM) AS (
    SELECT @min_num
    UNION ALL
    SELECT NUM+1
    FROM SERIES 
    WHERE NUM <= @max_num
)
SELECT NUM FROM SERIES
EXCEPT
SELECT ID FROM NUMBERS ORDER BY NUM
GO

Output: