I have to agree with Jason Adams on this topic. This article seems to miss one of the most important aspects of DBA work - proper indexing. I currently have the need in an environment to create trees recursively 700+ levels deep and with several million nodes. To perform this using a loop takes 10-15 times longer than to use CTEs. That is using the same indexing between the two sets of coding philosophies. I have seen the rare occasion where a CTE does not outperform the loop by that much, but have yet to see a scenario where the CTE is outperformed by a loop.

BTW, the loops had been optimized numerous times to gain every ms possible out of them - introduce the CTE's and we are leaps and bounds beyond historic SQL performance levels.