Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)
   JOIN cte c ...
   JOIN cte c ...

Can anyone shed any light on why this is happening?

share|improve this question
Also this answer and the comments: – Aaron Bertrand Jun 14 '12 at 4:00
See Provide a hint to force intermediate materialization of CTEs or derived tables that shows how this can be achieved using TOP ... ORDER BY and Quassnoi's blog that uses USE PLAN to achieve this. Whether that is relevant to you or not who knows without more details / an actual execution plan. – Martin Smith Jun 14 '12 at 6:53
up vote 4 down vote accepted

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?

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.