Numbers Table vs. Recursive CTE

NumbersTable.sql

USE bizdev
--
GO
--
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
--
DECLARE @max INT = 40000;
--
SELECT  GETDATE() - n.Number
FROM    dbo.Numbers n
WHERE   n.Number <= @max;
--
/*

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(40001 row(s) affected)
Table 'Numbers'. Scan count 1, logical reads 67, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
                               ^^^^^^^^^^^^^^^
(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 120 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

*/

RecursiveCTE.sql

USE bizdev
--
GO
--
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
--
DECLARE @max INT = 40000;
--
WITH    cte
          AS ( SELECT   0 AS num
               UNION ALL
               SELECT   ( num + 1 ) AS num
               FROM     cte
               WHERE    num < @max
             )
    SELECT  GETDATE() - c.num
    FROM    cte c
OPTION  ( MAXRECURSION 0 );
--
/*

 SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(40001 row(s) affected)
Table 'Worktable'. Scan count 2, logical reads 240007, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
                                 ^^^^^^^^^^^^^^^^^^^^     
(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 547 ms,  elapsed time = 620 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

*/

Numbers Table Execution Plan

Recursive CTE Execution Plan