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.

Same benefit as with many other programming paradigms: divide and conquer. Have a look over at the data site. Some people make nice use of CTEs to make queries a lot more readable. e.g. http://odata.stackexchange.com/stackoverflow/s/299/how-long-until-i-get-the-generalist-badge — Jacob, Oct 27 '10 at 21:03
Just an update, I now use CTEs a lot. Being able to break a process into smaller steps makes a complex query much easier to read. It saves either nesting the code within the final SQL statement or having to create and reference external views. — Stephen Turner, Jun 13 '11 at 11:47

score 27 · Accepted Answer · answered Oct 27 '10 at 20:53

27

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.

answered Oct 27 '10 at 20:53

KeithS

70,210
21
112
164

1

Despite not getting any votes, this answer sums up the points raised in the other answers, so it gets the tick :-) – Stephen Turner Oct 29 '10 at 18:46

score 20 · Answer 2 · edited May 23 '17 at 12:33

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

+1 just having these two examples side by side has given me a great deal of insight int CTEs — Stephen Turner, Oct 27 '10 at 21:57

score 4 · Answer 3 · answered Oct 27 '10 at 20:54

4

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.

answered Oct 27 '10 at 20:54

OMG Ponies

325,700
82
523
502

I can't find Emtucifor's answer that shows the difference between various approaches for generating lists :( – OMG Ponies Oct 27 '10 at 21:06
Exactly my point, not everything should use CTEs, recursive or otherwise. Perhaps a good exercise would be to write the solution both ways so performance can be measured and readability can be judged. – Stephen Turner Oct 27 '10 at 22:06
@webturner: Emtucifor's answer did exactly that, for date range generation. Also, CTEs aren't materialized -- they can be on Oracle 10g+ though. – OMG Ponies Oct 27 '10 at 23:37
@OMG Ponies [This one?](http://stackoverflow.com/questions/3743508/tsql-finding-order-that-occurred-in-3-consecutive-months/3755124#3755124) – Stephen Turner Oct 28 '10 at 09:39
@webturner: No, that's not it -- it specifically compared recursive CTE to others (NUMBERS, cursor). If I could see comments addressed to me from him, I could find it - he'd given me a link directly to it. – OMG Ponies Oct 28 '10 at 15:49
@OMG Ponies: I can't find a link [using this query](http://odata.stackexchange.com/stackoverflow/q/13439/find-emtucifors-comments-to-omg-ponies). – Stephen Turner Oct 30 '10 at 14:15

score 2 · Answer 4 · answered Jul 21 '12 at 14:50

CTE yields a faster result in a recursive scenario. The result of CTE is repeatedly used to get the final resultset. So since you have taken your where clause or subquery in CTE, definitely it is going to show performance improvement.
Reference : http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Just a note, in many scenarios, temp tables gives better performance then CTE also, so you should give a try to temp tables as well.
Reference : http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d040d19d-016e-4a21-bf44-a0359fb3c7fb

To CTE or not to CTE

4 Answers4