Consider the following example:

DECLARE @suppliedNumber MONEY
SET @suppliedNumber = 245656.00

    AccountHolders AS ah
    [dbo].[Accounts] AS a
    ah.Id = a.AccountHolderId
    ah.FirstName, ah.LastName
HAVING SUM(a.Balance) > @suppliedNumber

As you can see there is no ordering of any kind in the query. But looking in the execution plan I see implicit sorting:

SSMS Execution plan

Why is this happening? What's the technical reason behind this?

I'm using Microsoft SQL Server 2014 (RTM-CU14) (KB3158271) - 12.0.2569.0 (X64) May 27 2016 15:06:08 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 (Build 14393: )

  • 2
    You are using Group By which this sort is helping with. – Sean Gallardy - Retired User Feb 13 '17 at 13:25
  • 1
    The Stream Aggregate operator requires sorted data. It's most likely for that. – Erik Darling Feb 13 '17 at 13:26

As @sp_BlitzErik mentioned in his comment, the stream aggregate operator requires input to be sorted:

The Stream Aggregate operator groups rows by one or more columns and then calculates one or more aggregate expressions returned by the query. The output of this operator can be referenced by later operators in the query, returned to the client, or both. The Stream Aggregate operator requires input ordered by the columns within its groups. The optimizer will use a Sort operator prior to this operator if the data is not already sorted due to a prior Sort operator or due to an ordered index seek or scan. In the SHOWPLAN_ALL statement or the graphical execution plan in SQL Server Management Studio, the columns in the GROUP BY predicate are listed in the Argument column, and the aggregate expressions are listed in the Defined Values column.

If you add an index on FirstName, LastName you may see the sort go away (if the query optimizer decides to use the index).


I don't think the join is producing the sort

The group by sum is producing the sort
See the order by in the query plan

This might be faster
It breaks if the purpose is multiple accounts with same name

SELECT ah.Id, ah.FirstName, ah.LastName
    AccountHolders AS ah
    [dbo].[Accounts] AS a
    ah.Id = a.AccountHolderId
    ah.Id, ah.FirstName, ah.LastName
HAVING SUM(a.Balance) > @suppliedNumber 


SELECT ah.Id, ah.FirstName, ah.LastName
FROM AccountHolders AS ah
JOIN ( select AccountHolderId
       from [dbo].[Accounts] A
       GROUP BY Id 
       HAVING SUM(Balance) > @suppliedNumber 
     ) a
 on a.AccountHolderId = ah.ID  
  • Can you provide external document with detailed explanation on sorting & group by? – iPath ツ Feb 13 '17 at 13:41
  • @iPathツ It is not intuitive to you that group by would likely use sort? Take the group by and sum out and see if the sort goes away. – paparazzo Feb 13 '17 at 13:46
  • Yes, it is, but I need to provide that info to my teacher. He likes documents more than my intuition :) That's why I'm asking for external paper/blog that explains the situation in details. – iPath ツ Feb 13 '17 at 14:19

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.