Return to Answer

You should also use aliases and avoid attribute names that are also keywords.

You should also use schema prefixes and avoid keywords as attribute names.

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

Global Pushdown

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.

This rule is engaged with your rewrite.

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:

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.

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.

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.

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.

For your original query, the optimizer did consider the Merge Concatenation alternative, but the local aggregates meant that sorts were required to satisfy its required input properties:

The extra Sorts and higher Merge Concatenation cost meant the optimizer chose the cheaper plan option with a Concatenation and a single Sort. As always, these choices are driven from the cost model.

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

1. Global Pushdown

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.

This rule is engaged with your rewrite because the Metric attribute is disjoint and present in the grouping specification.

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
--OPTION (FORCE ORDER)
;

With the FORCE ORDER hint uncommented, the optimizer is prevented from moving the aggregate around:

Without the hint, the top-level aggregate can be moved and copied to each union input:

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

Like many optimizer explorations, GenLGAgg produces one or more alternatives that can be further explored by other rules. For example, the new local aggregates may be past joins or matched to an indexed view.

In your case, a rule named LocalAggBelowUniAll is employed to move the local aggregate below the UNION ALL. This is what was happening with the original query.

Importantly, the local aggregate is not quite 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.

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

This is mostly a consequence of implementation details and the need to preserve the connection between the local and global aggregates. Like many exploration rules, LocalAggBelowUniAll represents a query rewrite you could perform yourself, but you should not expect it to behave exactly the same in all respects as the closest T-SQL representation. It should also be said that the optimizer has the advantage of being able to dynamically decide which rewrite to use based on current statistics and metadata. This is not generally true for a manual rewrite.

Anyway, one of the consequences of a local aggregate being a bit different is that it 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 operator.

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, global ordering requirement, and any uniqueness guarantees available (see reference below).

For your original query, the optimizer did consider the Merge Concatenation alternative, but the local aggregate meant that sorts were required to satisfy the required input properties:

The extra Sorts and higher Merge Concatenation cost meant the optimizer chose the cheaper plan option with a Concatenation and a single Sort. As always, these choices are driven from the cost model.

Other Notes

With empty tables, the optimizer costs the Merge Concatenation option cheapest because it merges only one row from each input. The higher per-row cost of a Merge Concatenation does not offset the cost of the extra Sort needed in the Concatenation plan.

Your query should reject nulls, or the base tables should have an explicit NOT NULL on the Value attribute. This will simplify the final plan (and make any future indexed view matching easier). In particular, the Stream Aggregates will no longer have to compute a COUNT_BIG(Total) aggregate and the Compute Scalars will no longer be needed.

You should also use aliases and avoid attribute names that are also keywords.

SELECT 
    TotalSales = SUM(T.Sales), 
    T.[Date]
FROM 
(
    SELECT 
        Sales = ISNULL(SUM(VM.[Value]), 0.0), 
        VM.[Date]
    FROM dbo.ViewMetrics AS VM
    WHERE
        VM.[Value] IS NOT NULL
    GROUP BY
        VM.Metric, 
        VM.[Date]
) AS T
GROUP BY 
    T.[Date];

For your original query, the optimizer did consider the Merge Concatenation alternative, but the local aggregates meant that sorts were required to satisfy its required input properties:

The extra Sorts and higher Merge Concatenation cost meant the optimizer chose the cheaper plan option with a Concatenation and a single Sort. As always, these choices are driven from the cost model.

Further Reading

• SQL Server Optimizer Bug with JOIN and GROUP BY
  covers local, partial, and global aggregation
• Avoiding Sorts with Merge Join Concatenation
  details for merge concatenation sorts

Both written by me.