<p>I recently inherited a database on which one of the tables has the primary key composed of encoded values (Part1*1000 + Part2).<br>
I normalized that column, but I cannot change the old values.
So now I have</p>

<pre><code>select ID from table order by ID
ID
100001
100002
101001
...
</code></pre>

<p>I want to find the "holes" in the table (more precisely, the first "hole" after 100000) for new rows.<br>
I'm using the following select, but is there a better way to do that?</p>

<pre><code>select /* top 1 */ ID+1 as newID from table
where ID &gt; 100000 and
ID + 1 not in (select ID from table)
order by ID

newID
100003
101029
...
</code></pre>

<p>The database is Microsoft SQL Server 2000. I'm ok with using SQL extensions.</p>

```
```
SELECT (ID+1) FROM table AS t1
LEFT JOIN table as t2
ON t1.ID+1 = t2.ID
WHERE t2.ID IS NULL
```
```