The SQL Server optimizer has two main ways to explore pushing an aggregate down past a union all.

### Global Pushdown

The first rule is `GbAggBelowUniAll`. It's a fairly straightforward transform that moves the aggregation onto each of the union inputs.

It can only do this safely if the union is disjoint—that is, if there's something that makes each input completely independent and that factor appears in the `GROUP BY` clause.

It doesn't have to be a literal value like in your case, but it does have to be something the optimizer can recognize as completely separating the sets, like non-overlapping ranges. As part of the transformation, the constant part of the grouping clause is removed.

This rule is engaged with your rewrite.

An example using the Adventure Works sample database:

```
-- Helpful indexes
CREATE INDEX i ON Production.TransactionHistory 
    (Quantity, TransactionDate);
CREATE INDEX i ON Production.TransactionHistoryArchive 
    (Quantity, TransactionDate);
```

```
SELECT 
    U.Quantity, 
    MAX(U.TransactionDate)
FROM 
(
    -- The predicates on quantity make the two union inputs disjoint
    SELECT TH.*
    FROM Production.TransactionHistory AS TH
    WHERE 
        TH.Quantity BETWEEN 50 AND 60

    UNION ALL

    SELECT THA.*
    FROM Production.TransactionHistoryArchive AS THA
    WHERE 
        THA.Quantity BETWEEN 10 AND 20
) AS U
-- Grouping by the disjoint element
GROUP BY 
    U.Quantity
ORDER BY 
    U.Quantity;
```

The plan shows the top-level aggregate has been copied to each union input:

[![Global aggregate][1]][1]

### Local Aggregation

The second transformation involves a couple of different rules. First, `GenLGAgg` splits an aggregate into two parts, a global aggregate and a local aggregate. For example, a `COUNT` aggregate would be split into a local `COUNT` aggregate and a global `SUM` aggregate that adds all the local contributions together to arrive at the correct result.

The general idea is used in both serial and parallel plans. Sometimes, the local aggregate computes a subtotal local to its own thread, sometimes it performs a subset of the work below a join. In any case, the general idea is the same: do some part of the overall aggregation task as early as possible.

Once the original logical aggregation has been split into global and local portions, another rule called `LocalAggBelowUniAll` can move the local aggregate below the union all. This is what was happening with the original query.

Importantly, the local aggregate is not a normal aggregate. It is performing only part of the calculation and may end up on one of many threads in a parallel plan. A global aggregate always runs on a single thread to ensure correct results.

In a parallel plan, a local aggregate *may* be physically implemented as a Hash Match Partial Aggregate. This operator only gets a small, fixed memory grant and never spills to *tempdb*. If it runs out of memory, it simply stops aggregating. Results will still be correct thanks to the global aggregate.

The caveats are not limited to this physical operator or parallel plans. In general, you should think of a local aggregate as being quite different from the normal kind you write in SQL.

One of the consequences is that a local aggregate does not come with a uniqueness guarantee associated with its grouping keys. This is important in many instances but particularly so with your desired Merge Concatenation.

### Merge Concatenation

As the plan operator's name suggests, Merge Concatenation is just a normal Merge Join operator running in a special mode. It requires input sorted on the 'join keys' though exactly which sort order is needed can be affected by the projection column list and any uniqueness guarantees available.

A normal aggregate that provides grouping key uniqueness guarantees can allow the Merge Concatenation to require less onerous sorting than is possible with input from a local aggregate.


  [1]: https://i.sstatic.net/jOjZv.png