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 

```
Many people use an auto-incrementing integer or long value for the Primary Key of their tables, and it is often called `ID` or `MyEntityID` or something similar. This column, since it's just an auto-incrementing integer, often has nothing to do with the data being stored itself.

These types of "primary keys" are called surrogate keys. They have no meaning. Many people like these types of IDs to be sequential because it is "aesthetically pleasing", but this is a waste of time and resources. The database could care less about which IDs are being used and which are not.

I would highly suggest you forget trying to do this and just leave the ID column auto-increment. You should also create an index on your table that is made up of those (subset of) columns that can uniquely identify each record in the table (and even consider using this index as your primary key index). In rare cases where you would need to use all columns to accomplish that, that is where an auto-incrementing primary key ID is extremely useful—because it may not be performant to create an index over all columns in the table. Even so, the database engine could care less about this ID (e.g. which ones are in use, are not in use, etc.).

Also consider that an integer-based ID has a maximum total of 4.2 BILLION IDs. It is quite unlikely that you'll exhaust the supply of integer-based IDs in any short amount of time, which further bolsters the argument for why this sort of thing is a waste of time and resources.