In SQL2005 and beyond, you can use CTEs or you can continue to use subqueries.
What are the pros and cons of each?
I think the biggest benefit for using CTEs is readability. It makes it much easier to see what queries are being used as subqueries, and then it's easy to join them into a query, much like a view.
CTEs do allow recursion, which can solve some interesting problems, or get you into trouble.
The only disadvantage I see with CTEs is that they require the keyword WITH. This forces the previous statement to end with a semi-colon, which causes headaches for some developers.
Otherwise, I see CTEs as a way of using table-expressions in a much more readable way. I wish that all sub-queries could be defined this way, including scalar subqueries, IN lists and EXISTS queries. They should be parameterisable and able to be used in a lambda-style way.
I think the guys here have pretty much nailed it in terms of the key benefit being readability. However, I would just like to point out a common misconception - which is that a CTE is 'run' and then the rest of the query uses it.
It doesn't work like that - a CTE will work as you would think sub-queries would, and will be materialised once for each time it is used in the query.
For single materialisation, a temporary storage mechanism (temp table or table variable) can still offer efficiencies over both methods.
3 People are following this question.