Create Number Table T-SQLPosted: February 11, 2014
You can find many ways to generate an auxiliary number table. Here is a link:
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)