generate 10000 consecutive integers

Question

Is there a better way to generate [0 ... 9999] than this:

SELECT
    (a3.id + a2.id + a1.id + a0.id) id
FROM
(   
    SELECT 0 id UNION ALL
    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 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9
) a0
CROSS JOIN
(
    SELECT 0 id UNION ALL
    SELECT 10 UNION ALL
    SELECT 20 UNION ALL
    SELECT 30 UNION ALL
    SELECT 40 UNION ALL
    SELECT 50 UNION ALL
    SELECT 60 UNION ALL
    SELECT 70 UNION ALL
    SELECT 80 UNION ALL
    SELECT 90
) a1
CROSS JOIN
(
    SELECT 0 id UNION ALL
    SELECT 100 UNION ALL
    SELECT 200 UNION ALL
    SELECT 300 UNION ALL
    SELECT 400 UNION ALL
    SELECT 500 UNION ALL
    SELECT 600 UNION ALL
    SELECT 700 UNION ALL
    SELECT 800 UNION ALL
    SELECT 900
) a2
CROSS JOIN
(
    SELECT 0 id UNION ALL
    SELECT 1000 UNION ALL
    SELECT 2000 UNION ALL
    SELECT 3000 UNION ALL
    SELECT 4000 UNION ALL
    SELECT 5000 UNION ALL
    SELECT 6000 UNION ALL
    SELECT 7000 UNION ALL
    SELECT 8000 UNION ALL
    SELECT 9000
) a3
ORDER BY id

Any feedback appreciated.

http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1 — Tim Schmelter, Feb 06 '14 at 09:34
possible duplicate of [SQL, Auxiliary table of numbers](http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers) — Mikael Eriksson, Feb 06 '14 at 10:14
I do not think that's true. I ask a very specific question whereas the other is very generic ... — cs0815, Feb 06 '14 at 10:35

t-clausen.dk · Accepted Answer · 2014-02-06T10:13:31.813

2

You could write it like this:

;WITH x as
(
  SELECT 0 id UNION ALL
  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 UNION ALL
  SELECT 8 UNION ALL
  SELECT 9
)
SELECT
    row_number() over (order by (select 1))-1 id
FROM x a0
CROSS JOIN x a1
CROSS JOIN x a2
CROSS JOIN x a3

By removing the order by you gained a little.

edited Feb 06 '14 at 10:13

answered Feb 06 '14 at 09:49

t-clausen.dk

43,517
12
59
92

score 0 · Answer 2 · answered Feb 06 '14 at 10:11

0

I am not sure why this answer was removed from POST, this also produced desired output

;WITH x as
(
    select id from 
    (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x(id)
)
SELECT
    (a3.id * 1000 + 
    a2.id * 100 + a1.id * 10 + a0.id) id
FROM x a2
CROSS JOIN x a0
CROSS JOIN x a1
CROSS JOIN x a3

answered Feb 06 '14 at 10:11

Naveen Kumar

1,541
10
12

I removed my post because it doesn't work in sql-server 2005. Rewrote it – t-clausen.dk Feb 06 '14 at 10:14
it works only with SQL 2008 & above for SQL 2005 use [LINK](http://stackoverflow.com/a/21599261/3198474) – Naveen Kumar Feb 06 '14 at 10:48

score 0 · Answer 3 · answered Feb 06 '14 at 13:27

0

WITH a AS (
SELECT 0 AS a1
UNION ALL
SELECT a1+1 FROM a WHERE a1+1<10000
)
SELECT * FROM a
OPTION (Maxrecursion 10000)

answered Feb 06 '14 at 13:27

Jayvee

10,670
3
29
40

If you look at @TimSchmelter's link. You will see that the performance for this is not that great (14 milisecond vs 1 milisecond) – t-clausen.dk Feb 06 '14 at 13:34

generate 10000 consecutive integers

3 Answers3