title: How to find "holes" in a table
body: <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>