Do Stream Aggregate Operator Always Need Sort Operator? – Interview Question of the Week #240

Question: Does Stream Aggregate always need a Sort operator? No. Grouped stream aggregation needs appropriately ordered groups, but the order can come from another operator. A scalar aggregate over the whole input has no grouping order to establish.

After my Stream Aggregate versus Gather Streams article, a health-check client questioned my phrase that Stream Aggregate is often seen with Sort. Could it appear without one? Absolutely.

SELECT MAX(i.BillToCustomerID) AS TotalRows
FROM WideWorldImporters.Sales.Invoices AS i;
SELECT SUM(i.BillToCustomerID) AS TotalRows
FROM WideWorldImporters.Sales.Invoices AS i;
SELECT AVG(i.BillToCustomerID) AS TotalRows
FROM WideWorldImporters.Sales.Invoices AS i;

These examples have no GROUP BY. Every input row belongs to one overall aggregate, so neither SUM nor AVG needs the input sorted by customer ID. The original explanation attributed all three missing sorts to already ordered input; the scalar-aggregate distinction is the stronger answer.

For GROUP BY BillToCustomerID, Stream Aggregate needs each customer’s rows together. An ordered index access may provide that grouping order without a separate Sort. If the plan can’t obtain it that way, it may sort first or choose a different aggregation algorithm.

MAX can sometimes use an ordered access plus Top to avoid reading every row, as in the historical first plan. The aggregate names and TotalRows aliases are retained to match the original capture, but these values are not row counts.

Your server’s plan may differ with indexes, compatibility level and data. Read the actual operators rather than treating this capture as a guaranteed plan shape. Also, a Stream Aggregate does not promise the final result ordering; use ORDER BY when that is required.

Related: CASE in WHERE.

Want to try this yourself? Here is how to install WideWorldImporters.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.