Good article so far as the format, readability, and revelation go. The thing I'm having a problem with is the use of recursion of any kind to create a pseudo Tally table in the form of a CTE...

Let's strip everything away except creating the count and have a race between the recursive method and just a flat out lookup... the @Bitbucket variable is used to have a place to "dump" the count to without having the IO system or the display system get involved...

SET NOCOUNT ON

DECLARE @Bitbucket INT

DECLARE @Top INT

SET @Top = 100000

--=============================================================================

-- Recursive CTE does the count

--=============================================================================

PRINT '========== Recursive CTE =========='

SET STATISTICS IO ON

SET STATISTICS TIME ON

;WITH cteTally

AS (

SELECT 1 AS N

UNION ALL

SELECT N+1 FROM cteTally WHERE N < @Top

)

SELECT @Bitbucket = N

FROM cteTally

OPTION (MAXRECURSION 0)

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

PRINT REPLICATE('=',100)

--=============================================================================

-- ROW_NUMBER CTE does the count

--=============================================================================

PRINT '========== ROW_NUMBER CTE =========='

SET STATISTICS IO ON

SET STATISTICS TIME ON

;WITH cteTally

AS (

SELECT TOP(@Top) ROW_NUMBER() OVER(ORDER BY sc1.Type) AS N

FROM Master.dbo.spt_Values sc1,

Master.dbo.spt_Values sc2

)

SELECT @Bitbucket = N

FROM cteTally

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

PRINT REPLICATE('=',100)

On my humble 6 year old 1.8Ghz P5, that code returns the following results...

[font="Courier New"]========== Recursive CTE ==========

Table 'Worktable'. Scan count 2, logical reads 600001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 3375 ms, elapsed time = 4132 ms.

====================================================================================================

========== ROW_NUMBER CTE ==========

Table 'spt_values'. Scan count 2, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 63 ms, elapsed time = 67 ms.

====================================================================================================

[/font]

Based on that, I'd recommend not using the RBAR found in recursion for anything except the occasional hierarchy problem. 😀