t-sql find min value

Question

How can I find the minimum value among the values ​​not filled or not exists

for example

001  
002  
003
013
015

result must return 004

select min

Answer 1

declare @T table(Number int)

insert into @T values (1),(2),(3),(13),(15)

select top 1 Number + 1
from @T
where Number + 1 not in (select Number from @T)
order by Number

Update:

A version using char(3) zero padded.

declare @T table(ID char(3))

insert into @T values ('001'),('002'),('003'),('013'),('015')

select top 1 right(1001 + Id, 3)
from @T
where Id + 1 not in (select Id from @T)
order by Id

Answer 2

Assuming your sequence in the table YourNumbersTable Try this (SQL Server 2005+):

declare @min int, @max int
select @min = MIN(Id), @max = MAX(Id) from YourNumbersTable

;WITH numbers(id) as
(
    SELECT @min id
    UNION ALL 
    SELECT id+1
    FROM numbers
    WHERE id <= @max
)

SELECT MIN(Numbers.id)
FROM Numbers
     LEFT JOIN YourNumbersTable ON Numbers.Id = YourNumbersTable.Id
WHERE YourNumbersTable.Id IS NULL
OPTION(MAXRECURSION 0)