Create Number Table T-SQL


You can find many ways to generate an auxiliary number table. Here is a link:

http://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table

I modify one method to get a number table pretty fast within  the maximum recursion 100 for a recursive CTE for the first dataset and cross join this dataaset and use a ROW_Number function to generate the final number column from 1 through 10201.


;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
----1-10201

select n from Nums

But my recent test has shown the recursive CTE is faster:


;WITH Nums0(Num) AS
(SELECT 1 AS Num
 UNION ALL
 SELECT Num+1 FROM Nums0 where Num<20000
)

select Num from Nums0
 option(maxrecursion 20000)

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s