declare @t table(id int) insert into @t select 1 union all select 2 union all select 3 union all select 5 union all select 6 union all select 7 union all select 9 union all select 10select min(id) from (select (a.id+1) as id from @t a where not exists(select 1 from @t where id=a.id+1) union select 1 where not exists(select 1 from @t where id=1)) b
Create Table TEST(ID Int,Name Varchar(10)) Insert TEST Select 1,'aa' Union All Select 2,'bb' Union All Select 3,'cc' Union All Select 5,'dd' Union All Select 6,'cc' Union All Select 8,'cc' GO Select TOP 1 ID+1 From TEST A Where Not Exists(Select * From TEST Where ID=A.ID +1) Order By ID Insert TEST Select 4,'kk' Select TOP 1 ID+1 From TEST A Where Not Exists(Select * From TEST Where ID=A.ID +1) Order By ID GO Drop Table TEST --Result /* 47 */
CREATE FUNCTION nocon( @oid int )RETURNS int AS BEGIN declare @id int set @id=0 select @id=count(1) from t where oid=@oid+1 if @id=0 set @id=1 RETURN @id END go create table t (oid int) insert into t(oid) select 1 union all select 2 union all select 3 union all select 5 union all select 6 union all select 8 union all select 10select oid+1 from t where dbo.nocon(oid)=1 and oid+1 not in(select oid from t) and oid+1<(select max(oid) from t) order by oiddrop table t
insert into @t
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 6 union all
select 7 union all
select 9 union all
select 10select min(id) from
(select (a.id+1) as id from @t a where not exists(select 1 from @t where id=a.id+1)
union
select 1 where not exists(select 1 from @t where id=1)) b
Create Table TEST(ID Int,Name Varchar(10))
Insert TEST Select 1,'aa'
Union All Select 2,'bb'
Union All Select 3,'cc'
Union All Select 5,'dd'
Union All Select 6,'cc'
Union All Select 8,'cc'
GO
Select TOP 1 ID+1 From TEST A Where Not Exists(Select * From TEST Where ID=A.ID +1) Order By ID
Insert TEST Select 4,'kk'
Select TOP 1 ID+1 From TEST A Where Not Exists(Select * From TEST Where ID=A.ID +1) Order By ID
GO
Drop Table TEST
--Result
/*
47
*/
@oid int
)RETURNS int
AS
BEGIN
declare @id int
set @id=0
select @id=count(1) from t where oid=@oid+1
if @id=0
set @id=1
RETURN @id
END
go
create table t
(oid int)
insert into t(oid)
select 1
union all
select 2
union all
select 3
union all
select 5
union all
select 6
union all
select 8
union all
select 10select oid+1
from t
where dbo.nocon(oid)=1 and oid+1 not in(select oid from t) and oid+1<(select max(oid) from t)
order by oiddrop table t