There are several cases where you may write algorithms only to find missing numbers in a sequence. There is an efficient way to do this. Let’s say you have a table like this:
Mynum-----1346791016Here is the script to create the table and insert sample data.
--DROP TABLE #testCREATE TABLE #test(mynum INT)INSERT INTO #test VALUES (1)INSERT INTO #test VALUES (3)INSERT INTO #test VALUES (4)INSERT INTO #test VALUES (6)INSERT INTO #test VALUES (7)INSERT INTO #test VALUES (9)INSERT INTO #test VALUES (10)INSERT INTO #test VALUES (16)--SELECT * FROM #testSELECT A.mynum+1 AS 'MissingFROM', MIN(B.mynum)-1 AS 'TO'FROM #test A, #test B WHERE A.mynum < B.mynum GROUP BY A.mynum HAVING A.mynum + 1 < MIN(B.mynum) ORDER BY 1
