Find first free number

I have a table

Col1 Col2

1 1000

2 1001

4 1003

5 1004

7 1006

I want to find the first free number from first column.

Now It should return 3.

After inserting a row with col1 = 3 it should return 6 and after inserting a row with col1 = 6 it should return 8.

Is it posible ?

In general, you need to relate the table to itself looking for Col1 + 1. There are lots of variations on that theme. The challenge is to get a meaningful result from an empty table.

Here are a couple of examples, you will likely get others as well.

Select Top 1 Col1

From MyTable

Where Col1 + 1 Not In (Select Col1 From MyTable)

Order by Col1

Select Min(Col1)

From MyTable t1 Left Join MyTable t2

On t2.col1 = t1.col1 + 1

Having t2.col1 Is Null

|||

use a pivot table to compare which Col1 are not sequentially present. this code sample assumes your schema (called TestTable) and a Pivot table with one column (i, range from 1 - 999)

select p.i

from Pivot p

where i between 1 and @.whateverRange and

notexists(

select t.Col1

from TestTable t

where t.id = p.i)

this is a nice solution when you're looking for sequential integers