Thought CTEs were syntactic sugar

Question

I was asked to test a change to a query, moving a Table Valued Function (TVF) into a CTE (non-recursive). My understanding was that each CTE reference is executed separately, so there was a possibility of the change leading to tempdb contention...

I was very surprised to see the query performance improve when using the CTE. IE:

 ;WITH cte AS (
    SELECT t.col FROM dbo.getValues() t)
 SELECT ...
   JOIN cte c ...
 UNION ALL
 SELECT ...
   JOIN cte c ...

Can anyone shed any light on why this is happening?

Answer 1

I think the behavior is that the CTE is "expected" to be evaluated multiple times, but it doesn't always work that way (e.g. it is not guaranteed). There are probably a lot of variables that dictate the behavior one way or the other. Can you share an actual execution plan?