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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


13
0
Sign in
to vote
ID 740437 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 5/3/2012 3:00:47 PM
Access Restriction Public

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.
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.