求单表序数字段中不连续的最小值

drop table cemp;

create table cemp(
Id int primary key,
Name nchar(50));

insert into cemp values
(1, 'Chris'),
(2, 'Alvin'),
(3, 'Lena'),
(4, 'Sherman'),
(7, 'Joanna'),
(8, 'Josie'),
(11, 'Lucy'),
(14, 'Luke');

--自连接法
select min(c2.Id+1) TargetNum
 from cemp c1
  right join cemp c2 on c1.Id=c2.Id+1
where c1.Id is null

--递归法
with temp as 
( 
select 1 as Num 
  union all 
select Num+1 
 from temp 
where Num<100
),
temp2 as 
(
select * 
 from temp t 
  left join cemp c on t.Num=c.Id
)
select Min(Num) TargetNum 
 from temp2
where Id is null
OPTION(MAXRECURSION 0);

--最优解，窗口函数法
with temp as 
(
select RN=ROW_NUMBER() over(order by c.Id ),c.Id
 from cemp c
)
select Min(RN) TargetNum
 from temp t
where t.RN<>t.Id