Find the smallest unused number in SQL Server

Question

How do you find the smallest unused number in a SQL Server column?

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.

Is there a way to do this through plain SQL or is this a problem for TSQL/code?

Thanks!

EDIT

Special thanks to WW 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.

LINQ

FYI - this can be accomplished via LINQ with the following code:

var nums = new [] { 1,2,3,4,6,7,9,10};

int nextNewNum = (
    from n in nums
    where !nums.Select(nu => nu).Contains(n + 1)
    orderby n
    select n + 1
).First();

nextNewNum == 5

Answer 1

You really should try to convert the column to IDENTITY. BACKUP first then use ROW_NUMBER to update the document ID so they start from 1 and up to the document count. You should do it in a WHILE one at the time because if the number column is used as reference in other tables (foreign keys) SQL Server will try to update the foreign keys and maybe fail because of conflicts. In the end just enable identity specifications for the column.

:) It's more work now but it will save you a lot of trouble later.