Question: I am trying to assign ID numbers to records that are being inserted into an SQL Server 2005 database table. Since these records can be deleted, I would like these records to be assigned the first available ID in the table. For example, if I have the table below, I would like the next record to be entered at ID 4 as it is the first available. 

```
    | ID | Data |
    | 1  | ...  |
    | 2  | ...  |
    | 3  | ...  |
    | 5  | ...  |

```

The way that I would prefer this to be done is to build up a list of available ID's via an SQL query. From there, I can do all the checks within the code of my application. 

So, in summary, I would like an SQL query that retrieves all available ID's between 1 and 99999 from a specific table column.

Answer: 
First build a table of all N IDs.

```
declare @allPossibleIds table (id integer)
declare @currentId integer

select @currentId = 1
while @currentId < 1000000
begin
    insert into @allPossibleIds
    select @currentId

    select @currentId = @currentId+1
end

```

Then, left join that table to your real table. You can select MIN if you want, or you could limit your allPossibleIDs to be less than the max table id

```
select a.id 
from @allPossibleIds a  
left outer join YourTable t
on a.id = t.Id
where t.id is null 

```
Why do you need a table from 1..999999 all information you need is in your source table. Here is a query which give you minimal ID to insert in gaps. 
It works for all combinations: 

```
(2,3,4,5) - > 1
(1,2,3,5) - > 4
(1,2,3,4) - > 5

```

[SQLFiddle demo](http://sqlfiddle.com/#!6/78b36/6)

```
select min(t1.id)+1 from 
(
  select id from t 
  union 
  select 0
 )

t1 
left join t as t2 on t1.id=t2.id-1
where t2.id is null

```