1

I have following queries to select latest order from each user. Is there any performance advantage in the query using CTE? For my current tables, both are giving same execution plan and time.

Note: I am using SQL Server 2012

DECLARE  @Person TABLE (PersonId INT, NameFirst VARCHAR(100), NameLast VARCHAR(100))
DECLARE @Order TABLE (OrderId INT, PersonId INT, OrderDateTime DATETIME)

--Query1
SELECT A.PersonID,A.NameFirst,A.NameLast, A.OrderID AS LastOrderID,A.OrderDateTime AS LastOrderDateTime
FROM
(
    SELECT P.PersonID,P.NameFirst,P.NameLast,O.OrderID,O.OrderDateTime, 
           ROW_NUMBER() OVER(PARTITION BY O.PersonID ORDER BY O.PersonID,O.OrderDateTime DESC) AS RowNumber
    FROM @Person P
    LEFT OUTER JOIN @Order O
        ON P.PersonId = O.PersonId
)A
WHERE RowNumber = 1
ORDER BY PersonID

--Query2
;WITH CTE AS
(
    SELECT  P.PersonID,P.NameFirst,NameLast,O.OrderID AS LastOrderID,O.OrderDateTime AS LastOrderDateTime,
            ROW_NUMBER() OVER(PARTITION BY O.PersonID ORDER BY O.OrderDateTime DESC) AS RowNumber
    FROM @Person P
    LEFT OUTER JOIN @Order O
        ON P.PersonId = O.PersonId
)
SELECT A.PersonID,A.NameFirst,A.NameLast, A.LastOrderID, A.LastOrderDateTime
FROM CTE A
WHERE RowNumber = 1
ORDER BY PersonID
  • 1
    The plans should be the same. – ypercubeᵀᴹ Oct 26 '16 at 14:10
  • 1
    The optimizer should break down queries into component parts, which will end up being logically equivalent, and therefore optimize the same. – Philᵀᴹ Oct 26 '16 at 14:32
  • 1
    Unless you need the same Derived Table more than once, a CTE is mainly syntactic sugar :) – dnoeth Oct 26 '16 at 15:00
3

The answer is in your question:

For my current tables, both are giving same execution plan and time.

When two plans are identical, neither of the queries that produced them can possibly have any performance advantage over the other. The times being in your case identical as well only confirms that.

The above should at least be true for actual (as opposed to estimated) plans, but I believe it is perfectly safe to assume that simply rewriting a derived table as a Common Table Expression without incurring any other change is not going to either speed up or slow down your query a tiniest bit in SQL Server.

That is not to say a CTE cannot have advantages of other nature. One of the most commonly (although, perhaps, still not universally) acknowledged advantages of a CTE over a derived table is readability. I will not go into much detail here as this is beyond the scope of the question but basically, a CTE lets you split your query into logical steps more cleanly, as well as enables you to follow the logic of an unfamiliar script more naturally.

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.