Find the Missing Sequential Number From Table

DECLARE @Minval INT ,@Maxval INT
DECLARE @Temp TABLE (ID SMALLINT ,Name VARCHAR(128))
INSERT INTO @Temp ( ID ,Name )
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C' UNION ALL
SELECT 4,'D' UNION ALL
SELECT 7,'E' UNION ALL
SELECT 8,'F' UNION ALL
SELECT 10,'G' UNION ALL
SELECT 12,'H' UNION ALL
SELECT 13,'I' UNION ALL
SELECT 16,'J' UNION ALL
SELECT 17,'K' UNION ALL
SELECT 18,'L' UNION ALL
SELECT 20,'M' UNION ALL
SELECT 21,'N' UNION ALL
SELECT 22,'O' UNION ALL
SELECT 23,'P' UNION ALL
SELECT 24,'Q' UNION ALL
SELECT 25,'R' UNION ALL
SELECT 27,'S' UNION ALL
SELECT 28,'T' UNION ALL
SELECT 29,'U' UNION ALL
SELECT 30,'V' UNION ALL
SELECT 31,'W' UNION ALL
SELECT 33,'X' UNION ALL
SELECT 34,'Y' UNION ALL
SELECT 36,'Z'
SELECT @Minval=MIN(ID) ,@Maxval=MAX(ID) FROM @Temp
;WITH GenNumber AS (
SELECT @Minval AS num
UNION ALL
SELECT num+1 FROM GenNumber WHERE num+1<=@Maxval
)
SELECT num FROM GenNumber X WHERE NOT EXISTS (SELECT * FROM @Temp Y WHERE X.num=Y.ID)
OPTION (MAXRECURSION 10000)

Up Next