Is there a generic way to generate an arbitrary linear sequence in SQL?

Question

Is there a SQL query I can do that will generate a linear sequence like

1, 2, 3, 4, 5, 6, 7 ... x+1

or

2, 7, 12, 17, 22 ... 2+5x

(where each number is an entry in a row of the resulting table)

Answer 1

SQL Server and Oracle now implement the ANSI standard ROW_NUMBER() windowing function, but you'd need a table to work off of:

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS __ROW, ID, Name
FROM SomethingWithANameAndAnID
ORDER BY __ROW;

Or you could use a recursive Common Table Expression in SQL Server (not sure if Oracle implements this yet):

WITH cte AS
(
    SELECT 1 AS num
    UNION ALL
    SELECT (num + 1) AS num FROM cte
    WHERE num < @SomeMaximum
)
SELECT * FROM cte OPTION (MAXRECURSION 0);

Note that without the MAXRECURSION option CTE recursion depth in MS SQL is limited to 100. (value of 0 disables the recursion limit)

Answer 2

If performance is your concern, have this UDF ready:

create function [dbo].[Numbers](@count bigint)
RETURNS TABLE RETURN
with byte (n) as ( select 1 from ( VALUES 
        (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
        ) x(n)  )
, byte2 (n) as ( select 1 from byte a, byte b)
, byte4 (n) as ( select 1 from byte2 a, byte2 b)
, byte8 (n) as ( select 1 from byte4 a, byte4 b)
select top(@count) n = ROW_NUMBER() over(order by n) from byte8

Apparently, recursion-only CTE generated number sequence works, but very slow. Here we trade some code volume for huge increase in performance. This one gives me over 30 million numbers in 8 seconds on my crappy overloaded PC. It can go as far as you want and can afford to the limit of max bigint.

It won't touch the disk IO unless the optimizer rules it out of memory (hardly ever for reasonable scenario). It will also avoid waits and deadlocks unlike solutions based on physical tables.

Use like this:

select 2 + n*5 from Numbers(100)

You should be able to create a view like this.

For those not requiring an actual number, just rows, removing the row_number stuff speeds it up twice.

Inspired by http://weblogs.sqlteam.com/jamesn/archive/2008/05/29/60612.aspx (Itzik Ben Gan mentioned by S. Neumann). This version comes with a simpler execution plan and makes bigints possible, that's about the advantages.

Answer 3

No. (Unless precreating a table of numbers counts as a generic way.)

In SQL Server this can be done with a recursive CTE or generating a sequence using ROW_NUMBER()

Answer 4

Use sequence

Answer 5

In Oracle you can do:

select ROWNUM linear_sequence from dual CONNECT BY LEVEL <= x;

where x is the end of the sequence.

Answer 6

You can specify an increment when creating a sequence :

CREATE SEQUENCE mysequence  INCREMENT BY 5 START WITH 2;