;WITH R_CTE_1(i) AS
(SELECT 1 UNION ALL
 SELECT i + 1
 FROM R_CTE_1
 WHERE i < 100),R_CTE_2(i) AS
(SELECT 1 UNION ALL
 SELECT i + 1
 FROM R_CTE_2
 WHERE i < 100),R_CTE_3(i) AS
(SELECT 1 UNION ALL
 SELECT i + 1
 FROM R_CTE_3
 WHERE i < 100)
 SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n 
 FROM R_CTE_1
      CROSS JOIN R_CTE_2
      CROSS JOIN R_CTE_3
OPTION (MAXRECURSION 0);

SET STATISTICS TIME ON;
DECLARE @N INT
;WITH 
Tens (N)     AS (SELECT 0 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), 
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3), 
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2), 
Tally (N)    AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)

SELECT  TOP (1000000)
        @N = N
FROM    Tally;

quote:

---

Originally posted by Lamprey

I forget the author of this and I think I got it from SqlServerCentral, but it is supposed to be one of the fastest inline number/tally table around. But, I haven't done a lot of testing because I don't use inline stuff like this in production.

SET STATISTICS TIME ON;
DECLARE @N INT
;WITH 
Tens (N)     AS (SELECT 0 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), 
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3), 
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2), 
Tally (N)    AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)

SELECT  TOP (1000000)
        @N = N
FROM    Tally;

---