Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Numbers Table vs. Recursive CTE
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.
*/
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.
*/
@nathan-russell
Copy link
Author

nathan-russell commented Jan 12, 2016

Numbers Table Execution Plan

numberstable-executionplan

Recursive CTE Execution Plan

recursivecte-executionplan

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment