1

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.

  1. Why is the bottom Nested Loops operator required?
  2. 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?
  3. 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?
  4. The Compute Scalar operator only seem to have Expr1001 twice in the Output List, isn't the CustomerId necessary to identify the segment/customer?
3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.