title: Find the smallest unused number in SQL Server
body: <p>How do you find the smallest unused number in a SQL Server column?</p>
  
  <p>I am about to import a large number of manually recorded records from Excel into a SQL Server table. They all have a numeric ID (called document number), but they weren't assigned sequentially for reasons that no longer apply, meaning from now on when my web site records a new record, it needs to assign it the smallest possible document number (greater than zero) that has not already been taken.</p>
  
  <p>Is there a way to do this through plain SQL or is this a problem for TSQL/code?</p>
  
  <p>Thanks!</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Special thanks to <a href="https://stackoverflow.com/questions/684106/find-the-smallest-unused-number-in-ms-sql/684176#684176">WW</a> for raising the issue of concurrency.  Given that this is a web app, it is multi-threaded by definition and anyone faced with this same problem should consider either a code or DB level lock to prevent a conflict.</p>
  
  <p><strong>LINQ</strong></p>
  
  <p>FYI - this can be accomplished via LINQ with the following code:</p>
  
  <pre><code>var nums = new [] { 1,2,3,4,6,7,9,10};
  
  int nextNewNum = (
      from n in nums
      where !nums.Select(nu =&gt; nu).Contains(n + 1)
      orderby n
      select n + 1
  ).First();
  </code></pre>
  
  <p>nextNewNum == 5</p>