I'm reading "SQL Server Execution Plans, Third Edition, by Grant Fritchey". It includes the following query and the execution plan (queried against the Adventure Works database).
SELECT soh.CustomerID, soh.SubTotal, AVG (soh.SubTotal) OVER (PARTITION BY soh.CustomerID) AS AverageSubTotal, ROW_NUMBER() OVER (PARTITION BY soh.CustomerID ORDER BY soh. OrderDate ASC) AS RowNum FROM Sales.SalesOrderHeader AS soh WHERE soh.OrderDate BETWEEN '20130101' AND '20130701';
Execution plan: https://www.brentozar.com/pastetheplan/?id=r14f1CaNK
I have few questions about the plan.
- Why is the bottom Nested Loops operator required?
- If it is there to get CustomerId and SubTotal after aggregation, can we not get it from the outer input of the other Nested Loops operator?
- We can't expect more than one row from the outer input of the bottom Nested Loops operator (since we aggregate the segment and produce a single row), can we?
- The Compute Scalar operator only seem to have Expr1001 twice in the Output List, isn't the CustomerId necessary to identify the segment/customer?