Home Dashboard Directory Help
Search

Avoid extra Sort in queries with multiple OVER clauses by Hugo Kornelis


Status: 

Active


13
0
Sign in
to vote
Type: Suggestion
ID: 740437
Opened: 5/3/2012 3:00:47 PM
Access Restriction: Public
0
Workaround(s)
view

Description

On both SQL Server 2012 and SQL Server 2008R2, the two queries below produce different execution plans:

USE AdventureWorks;
go
SELECT TerritoryID,
         Name,
         SalesLastYear,
         SalesYTD,
         RANK() OVER (ORDER BY SalesLastYear) AS Rank1,
         RANK() OVER (ORDER BY SalesYTD) AS Rank2
FROM     Sales.SalesTerritory
ORDER BY SalesLastYear;

SELECT TerritoryID,
         Name,
         SalesLastYear,
         SalesYTD,
         RANK() OVER (ORDER BY SalesYTD) AS Rank2,
         RANK() OVER (ORDER BY SalesLastYear) AS Rank1
FROM     Sales.SalesTerritory
ORDER BY SalesLastYear;

Note that the only difference is the order of the two RANK() expressions.
Apparently, the optimizer always evaluates functions with an OVER clause in the order in which they appear in the SELECT list. Depending on that order, this may require an extra Sort operator to be added to the plan.
Details
Sign in to post a comment.
Posted by Microsoft on 5/11/2012 at 9:57 AM
Dear Hugo,

Thanks for the feedback. We will consider this for a future release.

Best regards,
Eric Hanson
Program Manager, SQL Server
Posted by Paul White NZ on 5/3/2012 at 4:16 PM
Please extend optimizer support for sequences in general.
Sign in to post a workaround.