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

I know this answer is late but you can find the smallest unused number by using a recursive table expression:

CREATE TABLE Test
(
    ID int NOT NULL
)

--Insert values here

;WITH CTE AS
(
    --This is called once to get the minimum and maximum values
    SELECT nMin = 1, MAX(ID) + 1 as 'nMax' 
    FROM Test
    UNION ALL
    --This is called multiple times until the condition is met
    SELECT nMin + 1, nMax 
    FROM CTE
    WHERE nMin < nMax
)

--Retrieves all the missing values in the table. Removing TOP 1 will
--list all the unused numbers up to Max + 1
SELECT TOP 1 nMin
FROM CTE
WHERE NOT EXISTS
(
    SELECT ID
    FROM Test
    WHERE nMin = ID
)

Answer 2

I faced a similar problem and came up with this:

Select Top 1 IdGapCheck
From (Select Id, ROW_NUMBER() Over (Order By Id Asc) AS IdGapCheck
    From dbo.table) F
Where Id > IdGapCheck
Order By Id Asc

Answer 3

Here is a simple approach. It may no be fast. It will not find missing numbers at the beginning.

SELECT MIN(MT1.MyInt+1)
FROM MyTable MT1
LEFT OUTER JOIN MyTable MT2 ON (MT1.MyInt+1)=MT2.MyInt
WHERE MT2.MyInt Is Null

Answer 4

select
    MIN(NextID) NextUsableID
from (
    select (case when c1 = c2 then 0 
            else c1 end) NextID 
    from (  select ROW_NUMBER() over (order by record_id) c1, 
                   record_id c2
            from   myTable)
)
where NextID > 0

Answer 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

Answer 6

No mention of locking or concurrency in any of the answers so far.

Consider these two users adding a document at nearly the same time:-

User 1                User 2
Find Id               
                      Find Id
Id = 42               
                      Id = 42
Insert (42..)  
                      Insert (42..)
                      Error!

You either need to: a) Handle that error and go around the loop again looking for the next available Id, OR b) Take a lock out at the start of the process so only 1 user is looking for Ids at a particular time