I use CTE queries a lot when building datasources for my reports. I prefer them over #temp tables and @tables (because there is more work involved creating those)

Once you go slightly overboard with CTEs you'll notice that technique isn't working for a specific problem. A CTE can be running fast, you make a slight change and it runs really really bad. That's where I usually start looking at a CTE > insert that set into a #temp table and finish it off with a SELECT or another CTE against the #temp table.

Those occasions are rare though.

Nice article, cheers.