From msdn :

Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

I'm using CTEs quite a lot, but I've never thought deeply about the benefits of using them.

If I reference a CTE multiple times in the same query:

  • Is there any performance benefit?
  • If I'm doing a self join, will SQL Server scan the target tables twice?
  • 2
    Profiler should tell you if it scans twice. IMHO, CTE's are awesome for recursion. – Dan Andrews Mar 6 '12 at 16:26
  • 3
    There are no hard answers when the query optimizer is in play. Some queries will see performance benefits, some won't. Sometimes using a temp table instead of a CTE will be faster, sometimes it won't. – Journey Mar 6 '12 at 16:38

As a rule, a CTE will NEVER improve performance.

A CTE is essentially a disposable view. There are no additional statistics stored, no indexes, etc. It functions as a shorthand for a subquery.

In my opinion they can be EASILY overused (I see a lot of overuse in code in my job). Some good answers are here, but if you need to refer to something more than once, or it's more than a few hundred thousand rows, put it into a #temp table instead and index it.

  • 3
    Agree. Except for recursive CTEs, then they simply assist readability – gbn Mar 6 '12 at 18:43
  • What if the CTE returns only a few rows (so they can be kept in memory) which are expensive to calculate (aggregation on a large table) and that result is used more than once? That should improve performance, shouldn't it? (at least that's my experience with PostgreSQL and Oracle where temp table are used very rarely) – a_horse_with_no_name Mar 6 '12 at 19:32
  • 2
    @a_horse_with_no_name - it would be equivalent to just making it a subquery. If the result is used more than once in a single query, it'll be reused and not recalculated. If it's used in more than one query, then a CTE is a bad choice since the results are discarded after the first query. – JNK Mar 6 '12 at 19:35
  • @JNK: thanks. Seems SQL Server behaves differently here. – a_horse_with_no_name Mar 6 '12 at 20:02
  • Some people find CTE's more readable in certain circumstances FWIW stackoverflow.com/a/11170918/32453 – rogerdpack Apr 19 '19 at 15:15

One place besides recursion where I find CTEs incredibly useful is when creating complex reporting queries. I use a series of CTEs to get chunks of the data I need and then combine in the final select. I find they are easier to maintain than doing the same thing with a lot of derived tables or 20 joins and I find that I can be more assured that it returns the correct data with no effect of multiple records due to the one-many relationships in all the different joins. Let me give a quick example:

;WITH Conferences (Conference_id)
(select  m.Conference_id
FROM mydb.dbo.Conference m 
WHERE client_id = 10
    and Conference_id in 
            (select Conference_id from mydb.dbo.Expense 
            where amount <>0
            and amount is not null)
--select * from Conferences
,MealEaters(NumberMealEaters, Conference_id, AttendeeType)
(Select count(*) as NumberMealEaters, m.Conference_id,  AttendeeType 
from mydb.dbo.attendance ma 
join Conferences m on m.Conference_id = ma.Conference_id
where (ma.meals_consumed>0 or meals_consumed is null)and attended = 1
group by m.Conference_id)
--select * from MealEaters

,Expenses (Conference_id,expense_date, expenseDescription,  RecordIdentifier,amount)
(select Conference_id,max(expense_date) as Expense_date, expenseDescription,  RecordIdentifier,sum(amount) as amount
        (SELECT Conference_id,expense_date,  amount, RecordIdentifier
        FROM mydb.dbo.Expense
        WHERE  amount <> 0 
            and Conference_id IN 
            (SELECT  Conference_id
            FROM mydb.dbo.Conferences ) 
        group by Conference_id, RecordIdentifier) a
--select * from Expenses
Select m.Conference_id,me.NumberMealEaters, me.AttendeeType, e.expense_date,         e.RecordIdentifier,amount
from Conferences m
join mealeaters me on m.Conference_id = me.Conference_id
join expenses e on e.Conference_id = m.Conference_id

So by separating out the different chunks of information you want, you can check each part individually (using the commented out selects, by uncommenting each one individually and only running as far as that select) and if you needed to make a change to the expense calculation (in this example), it is easier to find than when they are all mixed together into one massive query. Of course the actual reporting queries I use this for are generally much more complicated than the example.

  • 1
    Just for reporting queries? The systems I work on every day have transaction queries that are that complicated. Oddly our reporting queries are often some of our simplier ones. (Exlcuding trivial joinless CRUD queries of course). – Kevin Cathcart Mar 6 '12 at 21:12
  • I used that as the example because those typically are the most complicated here – HLGEM Mar 6 '12 at 22:19
  • +1 sometimes a more logical (human readable) query is preferable to a potentially better performing one. – onedaywhen Mar 7 '12 at 11:17
  • Yes. Given that a CTE will usually produce the same resulting plan, I see no reason to create horribly nested, multi-subquery monstrosities - when we could instead lay out each component visually in the order they're needed. I import XML files and do various acrobatics to get the data into the right form, which would be unbearable to write/read without CTEs. (Some of my old code probably has horrible subqueries all round!) – underscore_d Sep 9 '15 at 9:59

As always it depends but there are cases where the performance is greatly improved. I see it with INSERT INTO SELECT statements where you use a CTE for the select and then use that in the INSERT INTO. It may have to do with RCSI being set on for the database but for those times when very little is selected it can help quite a bit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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