SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * into #temp from (
select 1 as seqno
  union all 
select 3
  union all 
select 4
  union all 
select 7
  union all 
select 8
  union all 
select 10
  )a 
6 rows affected

DECLARE @minimum INT
SELECT @minimum = MIN(seqno) FROM #temp

;WITH Missing (missnum, maxid)
AS
(
 SELECT @minimum AS missnum, (select max(seqno) from #temp)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN #temp tt on tt.seqno = Missing.missnum
WHERE tt.seqno is NULL
OPTION (MAXRECURSION 0);


missnum


2

5

6

9


declare @t as table(seqno int);
declare @n as int, @s as int;
set @s = 1;
select @n = max(seqno) from #temp;
while(@s <= @n)
begin
  insert into @t
  select @s;
  set @s += 1;
end

select * from @t
where seqno not in (select seqno from #temp);


seqno


2

5

6

9