SQL Pivot Table: Selecting the next available number

I have the need to find the next available number in a set:

select min([pivot])
from   [pivot]
where not exists (
      select null as nothing
      fr开发者_JAVA百科om   product
      where  product.id = [pivot].[pivot])

However, this app will be used for a very long time and the [pivot] field is an integer. I don't really want to create 2,147,483,647 [pivot] records (sequential numbers from zero to a big number in a table). Creating a view takes too long.

Is there some function in T-SQL (Microsoft SQL Server 2005 / 2008) which can provide a [pivot] table without actually creating one. Creating a [pivot] view is bad because it takes a lot of time to access the view.

See if something like this will work for you. In my example, #Test has a hole at 5 that should be returned, the second (#Test2) has no holes, so we expect a new ID to be returned. It's done by self-joining on itself. I'm not sure why you've got a pivot there, so I may be misunderstanding your problem.

CREATE TABLE #test
(
    num int
)

CREATE TABLE #test2
(
    num int
)

INSERT INTO #test (num)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 6 UNION ALL
SELECT 7 

INSERT INTO #test2 (num)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL 
SELECT 7

SELECT MIN(t1.num)+1 FROM #test t1
    LEFT JOIN #test t2 ON t1.num+1 = t2.num
    WHERE t2.num IS NULL

SELECT MIN(t1.num)+1 FROM #test2 t1
    LEFT JOIN #test2 t2 ON t1.num+1 = t2.num
    WHERE t2.num IS NULL

I'm not sure if this is the best way to do it, but it answers the main question about automatically creating a table:

WITH myValueTable AS (
    SELECT 1 AS 'val'
    UNION ALL
    SELECT val + 1 FROM myValueTable WHERE val < 1000
)
SELECT * FROM myValueTable OPTION (MAXRECURSION 1000)

...which will return a single column table with the values 1 to 1000. However, if you're going to do this regularly, it will be quicker (for the overall query) to create a static table. If you can pin the values down to a given range, e.g., start at 124 and generate the next 10, then that'd be an improvement.

But without knowing more about the query, it's hard to know if this is the best solution for you with the job at hand. If you can provide some sample data of what you've got and what you want to get out, it would help.

Edit... If you're trying to find a "gap" in a set of numbers, you could try:

WITH mycte AS (
    SELECT product.id, ROW_NUMBER() OVER (ORDER BY product.id) AS 'rownum' FROM product
    UNION
    SELECT MAX(product.id), MAX(product.id) + 1 FROM product
)
SELECT MIN(rownum) FROM mycte WHERE rownum <> id

...again can't guarantee performance however, but may give you ideas to play with.

select min([pivot]) + 1 as next_num

?