SQL Server Performance – Using CTE for ROW_NUMBER Query

sql serversql-server-2012

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

Best Answer

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.

Related Question