Exploring Window Functions Execution Plans

SQL Server
There are quite a few different ways that you're likely to see window functions evidence themselves within your execution plan. Let's take a look at one example. Window Functions For our example, I've got a pretty simple query: SELECT soh.CustomerID, soh.SubTotal, ROW_NUMBER() OVER (PARTITION BY soh.CustomerID ORDER BY soh.OrderDate ASC) AS RowNum, Soh.OrderDate FROM Sales.SalesOrderHeader AS soh WHERE soh.OrderDate BETWEEN '1/1/2013' AND '7/1/2013' ORDER BY RowNum DESC, soh.OrderDate; Nothing to it really. What kind of execution plan does this generate? Here's the plan with runtime metrics (aka, an actual plan): I showed the missing index suggestion (and let's remember, they're just suggestions) just for completion and to show I'm not hiding anything. Potentially, adding an index could speed up the query. However, that doesn't affect what we're going to talk…
Read More