Oh... we have to be careful here, Sam. My query is based on a "Triangular Join" and it relies on just the right index or it can be hundreds and, sometimes, thousands of times slower and more resource intensive than a While Loop. It turns out that this solution worked perfectly because of the indexing and data available in the columns but such is not always the case.

Also, a word of caution. The costs you find in execution plans, especially the % of batch cost are fickle lovers and they can lie like you wouldn't believe. While such costs are helpful in finding potential problems, they should never be used to make the "final decision".

For example, here's some very simple code to build 30 years worth of dates using two different methods. The details, of course, are in the comments in the code.

/****************************************************************************************

Purpose:

This code demonstrates that the estimated and actual execution plans in SQL Server can

be 100% INCORRECT and that the execution plan should only be relied on to provide hints

as to what may be wrong with a query rather than an absolute indication. This code runs

in SQL Server 2005 only.

The code creates 30 years worth of dates starting with 2000-01-01 using two different

methods. The first method uses a recursive CTE and the second method uses a "Tally"

table. The output of each method is directed to a "throw-away" variable to take

display delays out of the picture.

Please check both the actual and estimated execution plans and compare the % of batch.

Please see the following article on how to build a Tally table and how they can be used

to replace certain While Loops.

http://www.sqlservercentral.com/articles/T-SQL/62867/

****************************************************************************************/

SET NOCOUNT ON

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

-- Recursive method shown by (Name with-held)

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

PRINT '========== Recursive method =========='

--===== Turn on some performance counters ===============================================

SET STATISTICS IO ON

SET STATISTICS TIME ON

DECLARE @Bitbucket DATETIME --Holds display output so display times aren't measured.

--===== Execute the code being tested ===================================================

DECLARE @DateVal DATETIME

SET @DateVal = '2000-01-01'

;with mycte as

(

select @DateVal AS DateVal

union all

select DateVal + 1

from mycte

where DateVal + 1 < DATEADD(yy, 30, @DateVal)

)

select @Bitbucket = d.dateval

from mycte d

OPTION (MAXRECURSION 0)

--===== Turn off the performance counters and print a separator =========================

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

PRINT REPLICATE('=',90)

GO

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

-- Tally table method by Jeff Moden

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

PRINT '========== Tally table method =========='

--===== Turn on some performance counters ===============================================

SET STATISTICS IO ON

SET STATISTICS TIME ON

DECLARE @Bitbucket DATETIME --Holds display output so display times aren't measured.

--===== Execute the code being tested ===================================================

DECLARE @StartDate AS DATETIME

SET @StartDate = '2000-01-01'

SELECT TOP (DATEDIFF(dd,@StartDate,DATEADD(yy,30,@StartDate)))

@Bitbucket = @StartDate-1+t.N

FROM dbo.Tally t

ORDER BY N

--===== Turn off the performance counters and print a separator =========================

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

PRINT REPLICATE('=',90)

GO

Here are the execution plans for the code. Notice that the % of batch is 0% for the Recursive CTE and its 100% for the Tally Table method. One would look at this and insist that Recursive CTEs are much better than the Tally Table method for doing such a thing.

But, if we look at the performance of the code, we can see that the % of Batch is 100% incorrect. The Tally Table code is actually MUCH faster than the Recursive CTE.

========== Recursive method ==========

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Table 'Worktable'. Scan count 2, logical reads 65749, 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 = 391 ms, elapsed time = 449 ms.

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

========== Tally table method ==========

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Table 'Tally'. Scan count 1, logical reads 20, 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 = 31 ms, elapsed time = 27 ms.

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

Try your code and measure it for performance. Also, be careful about using SET STATISTICS. It can also lie (not in this case, though). See the following article about how SET STATISTICS can lie about performance.

http://www.sqlservercentral.com/articles/T-SQL/91724/