Michael La Voie - 2 years ago 116
SQL Question

# Find the smallest unused number in SQL Server

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

Find the first row where there does not exist a row with Id + 1

``````SELECT TOP 1 t1.Id+1
FROM table t1
WHERE NOT EXISTS(SELECT * FROM table t2 WHERE t2.Id = t1.Id + 1)
ORDER BY t1.Id
``````

Edit:

To handle the special case where the lowest existing id is not 1, here is a ugly solution:

``````SELECT TOP 1 * FROM (
SELECT t1.Id+1 AS Id
FROM table t1
WHERE NOT EXISTS(SELECT * FROM table t2 WHERE t2.Id = t1.Id + 1 )
UNION
SELECT 1 AS Id
WHERE NOT EXISTS (SELECT * FROM table t3 WHERE t3.Id = 1)) ot
ORDER BY 1
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download