Get empty values from database table

Question

I have a database table which has columns with values 1 till 999 But it has some spaces e.g. 1,2,3,4,5,6,11,15 etc...

What would be the best to get the "next number" from this table?

Thanks in advance for your help

Answer 1

one way to do this is to get for every row the prior row, and then check where you are making a step.
This will not perform great, and it is NOT SAFE when more then 1 user is adding new rows !

declare @t table (number int)
insert into @t values (1), (2), (3), (4), (5), (6), (11), (12)

select top 1 
       (select top 1 t2.number + 1 from @t t2 where t2.number < t.number order by t2.number desc) as prior  
from   @t t
where  number <> (select top 1 t2.number + 1 from @t t2 where t2.number < t.number order by t2.number desc)
order by t.number

The result would be 7

Another option is this

select top 1
       t.number + 1 
from   @t t
  left join @t t2 on t.number = t2.number - 1
where  t2.number is null
order by t.number

This method might even be faster then the solution of Robin

EDIT
As Daniel pointed out in a comment, this will never return 1 in case the gap happens to be the first row.
To fix this, we can retrieve a value for the first missing row, and add it to our result by use of a union.

select top 1 number 
from   ( select top 1
                t.number + 1 as number
         from   @t t
           left join @t t2 on t.number = t2.number - 1
         where  t2.number is null

         union

         select 1 as number
         from   @t t
         where not exists (select 1 from @t t3 where t3.number = 1)
       ) t 
order by t.number

Since the extra query can only retrieve exact one row by an index, this should not affect performance much

Answer 2

You can use a CTE to generate the numbers and then get the first one that does not match with a record....

This work fine as you mentioned that it is not a large table

I have a datatable which has columns with values 1 till 999

Regard the other answers, both are too much faster than this with large tables, but none of them will return the correct value (1) if your input starts on 2 or greater.

I don't know the purpose of this request, but be aware that calculating values this way two users working at same time can get the same value. It can be an issue specially if you want to use this value to be part of a primary key or unique index

;with numbers as (
    SELECT 1 as nrstart, MAX(yourcolumn) as nrend FROM yourTable
    UNION ALL
    SELECT nrstart+1, nrend FROM numbers
    WHERE nrstart <= nrend
)
SELECT TOP 1 nrstart
FROM numbers 
WHERE NOT EXISTS (SELECT 1 FROM yourTable WHERE yourcolumn = numbers.nrstart)
ORDER BY nrStart
OPTION (MAXRECURSION 0); 

Answer 3

You want the first number, where that number plus one is not in the table.

SELECT TOP 1 (Number + 1) FROM myTable a WHERE NOT EXISTS 
    (SELECT * FROM myTable b WHERE b.Number = a.Number + 1) 
ORDER By Number

As mentioned in various comments, this sort of thing should be done in a transaction if there's any risk of a second user filling the gap while the first is looking for it.