I know you're only building it once but I'd never use the hidden RBAR of a recursive CTE to build an on-the-fly Tally Table for no other reason than someone may copy the code to do something larger and really end up with a performance problem.

I'd also use a clustered index on the table with a FILLFACTOR = 100 just in case someone wants to use the code on a server where the default FILLFACTOR has been changed to something other than 0 (100).

Here's what I mean... the following code includes several different methods including the original recursive CTE. Yeah... I even threw in a WHILE loop because it's a big surprise for a lot of folks when compared to a recursive CTE: (Didn't include index builds because they're all the same)

--===== Drop Tables ===========================================================================

DROP TABLE #Number, #Number1, #Number2, #Tally, #Tally1;

SET NOCOUNT ON;

DBCC FREEPROCCACHE;

WAITFOR DELAY '00:00:05'; --Let the system "settle" for several seconds

GO

--===== Recursive CTE w/Predefined Table (original)

create table #Number (number int);

with N4000 as (select 1 as Number union all select Number+1 from N4000 where Number <4000

)insert into #number select * from N4000 option (MAXRECURSION 4000);

GO

--===== Recursive CTE w/SELECT/INTO

with N4000 as (select ISNULL(1,0) as Number union all select Number+1 from N4000 where Number <4000

)select * INTO #Number1 from N4000 option (MAXRECURSION 4000);

GO

--===== WHILE Loop

CREATE TABLE #Number2 (Number INT NOT NULL);

DECLARE @Counter INT;

SELECT @Counter = 1;

BEGIN TRANSACTION;

WHILE @Counter <= 4000

BEGIN

INSERT INTO #Number2

(Number)

SELECT @Counter;

set @Counter = @Counter + 1;

END;

COMMIT;

GO

--===== Cross Join

SELECT TOP 4000

N = IDENTITY(INT,1,1)

INTO #Tally

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

GO

--===== Modified Itzek Method

WITH

E1(N) AS ( --=== Create Ten 1's

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 --10

),

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4)

SELECT N = ISNULL(N,0)

INTO #Tally1

FROM cteTally

WHERE N <= 4000

;

And, here are the results from SQL Profiler. Even the WHILE loop beat the recursive CTE! 😛