For me a CTE is a lot easier to follow. And the CTE is reusable within the same statement, where a derived table is not. (I love CTE btw) I also have seen indexes being used in a CTE when they were not used in a derived table (but that could have been a side effect of rewriting the query better I suppose?).
Changing the simple example given to use a CTE shows an identical execution plan for me.
;with Data as (
SELECT dept_id, count(*) AS n
FROM employee
GROUP BY dept_id
)
select top 1 dept_id, n
from Data
order by n DESC
And one little (overlooked?) thing about a multiple CTE, if a resultset is not referenced it appears to not be ran (according to the execution plan). Could I get some feedback on this?
Example, with this I only see TableB being shown in the execution plan :
;with
DataA as ( select a from TableA )
,DataB as ( select b from TableB )
,DataC as ( select c from TableC )
select
b
from DataB
Based on what I've coded, I would strongly urge the move to CTE instead of derived tables. 🙂
Tao Klerks (3/18/2008)
I see the value of CTEs, but I'm not sure I agree with the example... wouldn't it be better written (more efficient?) using a single aggregate query and a TOP clause?
SELECT TOP 1 dept_id, nFROM (
SELECT dept_id, count(*) AS n
FROM employee
GROUP BY dept_id
) AS a
ORDER BY n DESC
I guess the question for me is: while CTEs provide a nice syntax for repeated and recursive expressions, is the SQL optimizer actually able to use them as efficiently as a statement designed to avoid repetitive expressions in the first place?
I tried this on a very simple dataset (comparing all three statements), and found that the total cost of the queries, in all three cases, was the same. The query plans were slightly more complicated for the original statement and CTE statement, and slightly simpler for the statement above (single Sort/TopN Sort instead of Sort -> Filter -> Top) - unfortunately I don't have a large dataset to test on!
My instinct would be: Try to rewrite your statement to use joins and derived tables rather than subqueries, before moving to CTEs as a way of simplifying your subqueries.
Does anyone know better one way or the other?