# Generate N numbers

• harsha.majety

SSCommitted

Points: 1527

Comments posted to this topic are about the item Generate N numbers

• Steve JP

Ten Centuries

Points: 1069

Interesting.

Or old school:

``` select distinct Number from master.dbo.spt_values where number between 0 and 1000 ```

Another one I have seen is:

``` ;With CTE ( [Number] ) as ( select Number = '0' union select Number = '1' union select Number = '2' union select Number = '3' union select Number = '4' union select Number = '5' union select Number = '6' union select Number = '7' union select Number = '8' union select Number = '9' ) Select [Number] from ( select [Number] = cast([3sp].[Number] + [2sp].[Number] + [1sp].[Number] as varchar(10)) from CTE as [1sp] cross join CTE as [2sp] cross join CTE as [3sp] ) as x where [Number] between 0 and 1000 order by 1 ```

But the best in a way is a tally table which can be indexed and used in a query plan.

A slight problem that you might have with a recursive CTE is that the ".....the MAXRECURSION option exceeds the allowed maximum of 32767"

• Jeff Moden

SSC Guru

Points: 996640

@harsha-2 (and ayoe else interested),

Thanks for taking the time to post a script but you have to be very careful with recursive CTEs. Please read the following article for why you should almost never use a recursive CTE to count. To summarize and contrary to what you published, they are far from "quick" an shouldn't be used even for the smallest of counts.

http://www.sqlservercentral.com/articles/T-SQL/74118/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"