Generating a sequence in sql server

Question

I am working on a function that will take a low number and a high number as paramaters and returns a table containing everything between (and including).

I know I could use a cursor and increment a variable adding it to a scope based table every iteration, but I would prefer to avoid a cursor if possible. Does anyone else have a suggestion for a way to do this? (As i'm typing this im thinking possibly a CTE, which I will go investigate).

SQL Server 2011 ("Denali") will have [proper sequences](http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/a-first-look-at-sequences-in-sql-server) when it comes out ... — marc_s, Mar 12 '11 at 08:39
@marc_s - Can they be used as an adhoc auxiliary numbers table? — Martin Smith, Mar 12 '11 at 13:33

score 4 · Answer 1 · answered Mar 11 '11 at 22:33

4

Yes, you can use a recursive CTE to do this. For example to generate numbers between 10 and 20 inclusive:

WITH f AS
(
    SELECT 10 AS x
    UNION ALL
    SELECT x + 1 FROM f WHERE x < 20
)
SELECT * FROM f

answered Mar 11 '11 at 22:33

Mark Byers

811,555
193
1,581
1,452

1

Great solution, but be careful that the maxium number of results is set in 100 by default, and you can increment it just until 32767 by using OPTION (maxrecursion 32767); – pcofre Mar 11 '11 at 22:57
1

@pcofre - You can use `OPTION (MAXRECURSION 0)` for unlimited but performance could be an issue. – Martin Smith Mar 11 '11 at 23:01
I like this solution... is there any negative performance implications to using this method? – longda May 18 '11 at 18:14
+1 nice clean answer ....I thought I remembered a messier alternative answer to this where one of the master tables is used? – whytheq Aug 23 '13 at 09:51

score 2 · Accepted Answer · edited May 23 '17 at 12:04

Just create an indexed permanent auxiliary numbers table and be done with it. This will out perform any other method.

See Jeff Moden's answer here for more details and a script to populate such a table. if for some reason that isn't an option this should beat the recursive CTE according to the performance tests in the linked answer.

   WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
   SELECT N FROM cteTally
   WHERE N BETWEEN 10 AND 20

I was searching for an equivalent of PostgreSQL generate_series(). First I tried recursive CTE but it has too much lag of time for 1M records compared to this one. — Waqar, Dec 23 '14 at 13:47

Generating a sequence in sql server

2 Answers2

Linked