sql server 2005 - SQL Pivot Table: Selecting the next available number -

i have need find next available number in set:

select min([pivot])   [pivot] not exists (       select null nothing         product        product.id = [pivot].[pivot]) 

however, app used long time , [pivot] field integer. don't want create 2,147,483,647 [pivot] records (sequential numbers 0 big number in table). creating view takes long.

is there function in t-sql (microsoft sql server 2005 / 2008) can provide [pivot] table without creating one. creating [pivot] view bad because takes lot of time access view.

see if work you. in example, #test has hole @ 5 should returned, second (#test2) has no holes, expect new id returned. it's done self-joining on itself. i'm not sure why you've got pivot there, may misunderstanding problem.

create table #test (     num int )  create table #test2 (     num int )  insert #test (num) select 1 union select 2 union select 3 union select 4 union select 6 union select 7   insert #test2 (num) select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union  select 7  select min(t1.num)+1 #test t1     left join #test t2 on t1.num+1 = t2.num     t2.num null  select min(t1.num)+1 #test2 t1     left join #test2 t2 on t1.num+1 = t2.num     t2.num null