3

I was talking with the bossman, and he prefers subqueries over CTE's. Personally, I loathe subqueries. He mentioned that subqueries can be faster, but I am not convinced. I ran this short test:

    with classes as 
    (select top 10 Classkey from dimclass
    group by classkey
    order by count(1) desc),
    policies as (
    select CarrierKey, policykey, periodeffectivedate from dimpolicy),
    exposure as (
    select policykey, classkey  from DimExposure)


    select * from policies p
    inner join exposure x on p.PolicyKey = x.PolicyKey
    inner join classes c on x.ClassKey = c.Classkey

has an excution plan of:

https://www.brentozar.com/pastetheplan/?id=SJYJUZNHS

    select p.CarrierKey, p.PolicyKey, p.periodeffectivedate from dimpolicy p
    inner join (select policykey, classkey  from DimExposure) x on p.PolicyKey = x.PolicyKey
    inner join (select top 10 Classkey from dimclass
                group by classkey
                order by count(1) desc) c on x.ClassKey = c.Classkey

has the same execution plan:

https://www.brentozar.com/pastetheplan/?id=rJs_LbVSr

Question: Is this always the case? In the weird and wonderful world of SQL Server, the answer always seems to be it depends.

6

As is, I'd argue that the question isn't answerable. It's impossible to prove a negative and you won't find a guarantee in the product documentation. If you'd like an example of a technical difference between the two approaches, watch a few minutes of Paul White's Query Optimizer Deep Dive session. It is not clear how someone could translate that into a performance best practice.

I suggest approaching the issue as a matter of coding style instead of trying to find a performance best practice. Switching out a CTE for a derived table or a derived table for a CTE is not a meaningful way to rewrite a query.

4

In general, it depends. One case where a CTE is nicer than a derived table is when you need to reference it several times in the query. A silly example:

SELECT x,y
FROM (
    SELECT x,y FROM T WHERE p
) AS A
WHERE x = (SELECT MAX(x) FROM T WHERE p)

vs

WITH CTE (x,y) as ( 
    SELECT x,y FROM T WHERE p 
)
SELECT x,y FROM CTE WHERE x = (SELECT MAX(x) FROM CTE)

Different database engines treat multiple references to a CTE in different ways. In SQL Server, the CTE will generally be fully evaluated for each reference.

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.