the scripts are unavailable.
I'd sure like to see the queries before I can completely agree with the conclusions.
The scripts were all submitted to SQLCentral.com with the article. I am not sure why the link was broken.
To Charles Kincaid:
What I meant in "The plans do not give any clue to their performance difference." is that the CTE execution plan doesn't give the details in the recursive execution. The row number (the thickness of the arrow) is actually the final output. SQL server generates only one execution plan for a CTE.
The execution plan of the loop method showed in the article is for the one execution of the looping statement. You know, SQL server generates one execution plan for every execution of the same statement in the loop. So the arrow (row number) is thinner in each plan.
I just sent an email to Steve about the broken of the link to the scripts.
If it's not possible to fix the link, I can send the scripts to your guys later.