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.

Answer 2

They are syntactic sugar, with the exception of hierarchical/recursive queries.

However, not everything that can be done recursively should be - date generation via recursive CTE was barely better than a cursor -- the NUMBERS table trick scaled much better.

Answer 3

Personally, once I got comfortable using them, I think that they produce cleaner, more readable code. As an example, compare your answer to mine on #4018793. We essentially did the same thing; I used a CTE and you didn't.

Your answer without CTE:

SELECT
    course,
    section,
    grade,
    gradeCount
FROM
    table

        INNER JOIN
    (SELECT
        grade,
        Max(gradeCount) as MaxGradeCount
    FROM
        table
    ) MaxGrades
        ON  table.grade = MaxGrades.grade
            AND table.gradeCount = MaxGrades.MaxGradeCount
ORDER BY 
    table.grade

My answer with CTE:

;with cteMaxGradeCount as (
    select grade, max(gradeCount) as MaxGradeCount
        from @Test
        group by grade
)
select t.course, t.SECTION, t.grade, t.gradeCount
    from cteMaxGradeCount c
        inner join @Test t
            on c.grade = t.grade
                and c.MaxGradeCount = t.gradeCount
    order by t.grade