SQL Server – Why Inner Join Introduces Hidden Sort

join;sortingsql serversql server 2014

Consider the following example:

DECLARE @suppliedNumber MONEY
SET @suppliedNumber = 245656.00

SELECT 
    ah.FirstName, 
    ah.LastName
FROM 
    AccountHolders AS ah
JOIN 
    [dbo].[Accounts] AS a
ON 
    ah.Id = a.AccountHolderId
GROUP BY 
    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: )

Best Answer

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).

Related Question