SQL Server 2005 - Find minimum unused value within a range

Question

I have a situation similar to the following question:

Insert Data Into SQL Table

Where my scenario differs is that I have a non-auto-incrementing primary key field that can have a range between 1000 and 1999. We only have about a hundred values in it thus far, but the maximum value has already been taken (1999), and there are gaps in the numbering sequence. Thus, I need to find a value that is between 1000-1999 and not taken. For instance, if my current values are, for example, 1000, 1001, 1003, and 1999, I would want the query to return 1002.

Answer 1

try this:

declare @YourTable table (PK int)
insert @YourTable VALUES (1)
insert @YourTable VALUES (2)
insert @YourTable VALUES (4)
insert @YourTable VALUES (7)
insert @YourTable VALUES (8)


SELECT
    MIN(y.PK)+1
    FROM @YourTable                 y
        LEFT OUTER JOIN @YourTable y2 ON y.PK+1=y2.PK 
    WHERE y.PK>=1 AND y.PK<10 AND y2.PK IS NULL

OUTPUT:

-----------
3

(1 row(s) affected)

EDIT
this will give the same result:

;with N AS 
(SELECT TOP 1000 row_number() over(order by t1.object_id) as Number
     FROM sys.objects t1 
     CROSS JOIN sys.objects t2
)
SELECT
    MIN(Number) AS PK
    FROM N
        LEFT OUTER JOIN @YourTable y on n.Number=y.PK
    WHERE y.PK IS Null