----Declare local variable-----
DECLARE @MinValue INT, @MaxValue INT
----Drop temp table if alread created-----
DROP TABLE IF EXISTS #TmpData
----Create temp table-----------
CREATE TABLE #TmpData(Id INT)
----Insert id in temp table---------
INSERT INTO #TmpData(Id) VALUES(1),(3),(5),(8),(10)
-----Get min and max id from temp table-----
SELECT @MinValue = MIN(Id), @MaxValue = MAX(Id) FROM #TmpData
----generate serial number using recursive ctc-----
;WITH NumberSequence(Number) AS
(
SELECT @MinValue AS Number
UNION ALL
SELECT Number + 1 FROM NumberSequence WHERE Number < @MaxValue
)
------Get all missing Id's -------
SELECT Number, IIF(T.Id IS NULL, 'Missing', 'Not Missing') AS IsMissing
FROM NumberSequence A
LEFT JOIN #TmpData T ON A.Number = T.Id