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

September 1, 2019

Question: Do Stream Aggregate Operator Always Need Sort Operator?

Answer: Absolutely No.

Before I explain this question a bit more in detail, I suggest you read my earlier blog post here SQL SERVER – Is Stream Aggregate is Same as Gather Streams of Parallelism? This blog post discussed Stream Aggregate Operator in details and will give you more context about the question which we are discussing in this blog post.

In the earlier blog post, I made a statement – “As stream aggregate requires sorted data as input, it is often spotted with Sort operation.” After reading the statement, I received an email from my client Comprehensive Database Performance Health Check about does Stream Aggregate Operator always need a sort operator or is it possible for it to exists without it too. Yes, they do exists without the sort operator.

Here are few of the queries when you run each of them you will only see in the execution plan aggregate operator.

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

When you run the above three statements in SQL Server Management Studio (SSMS), you will find following three execution plan. In each of the execution plan, you can notice that there only stream aggregate operators. When the input of that operator is already sorted, there is no need for any additional operator.

Do Stream Aggregate Operator Always Need Sort Operator? - Interview Question of the Week #240 stream aggregate

Well, I hope you find this blog post helpful. If you have any such question, please do not hesitate to share with me and I will be happy to answer them on the blog.

Here are a few previous blog post which you may find interesting:

Reference: Pinal Dave (https://blog.sqlauthority.com)