To CTE or not to CTE

Question

Having been stuck with SQL2000 for far too long, I've not really had a lot of exposure to Common Table Expressions.

The answers I've given here (#4025380) and here (#4018793) have gone against the flow in that they didn't use a CTE.

I appreciate that for recursion they are the beez kneez, and there are a few queries that can be greatly simplified by their use, but at what point is their use just frivolous? Do they have a great performance benefit over a subquery or a join? Do they really simplify code and make it more maintainable?

In short, when is it good practice to use a CTE over a 'lesser' syntax.

Answer 1

You should generally use a CTE over a normal subquery if:

• Your query requires recursion (as you noted)
• The subquery is large or complex
• The containing query is large or complex
• The subquery is repeated (or at least several subqueries can be simplified by performing different simple operations on a common subquery)

In short, yes they do make queries more readable when well-used.