Two queries can return the same columns and still promise different row counts. UNION vs UNION ALL decides whether duplicates are removed or kept. That choice changes both the result contract and the operators in the plan.
Begin With the Result Contract
If a report needs each row exactly once, UNION can be correct. If two branches represent different periods or entities that cannot overlap, UNION ALL expresses that fact and avoids duplicate removal. Do not change one to the other solely because the plan looks faster. First prove whether duplicate rows are possible and what the consuming application expects.
SELECT 1 AS CustomerID, 'A' AS Source
UNION
SELECT 1 AS CustomerID, 'A' AS Source;
SELECT 1 AS CustomerID, 'A' AS Source
UNION ALL
SELECT 1 AS CustomerID, 'A' AS Source;In this UNION vs UNION ALL test, the first returns one row; the second returns two. Duplicate comparison covers every selected column, not only a business key. Adding a source label can make otherwise matching business rows distinct. I write down the expected cardinality before looking at a plan. Which row identity matters to the report?
Compare UNION vs UNION ALL Plans on Real Data
Run the same two branches with each operator, enable actual plans and STATISTICS IO, and compare output row counts. The example below uses a current and history table. Substitute a test schema with representative data; both queries are intentionally identical except for the set operator.
SET STATISTICS IO, TIME ON;
SELECT CustomerID,OrderID,Amount FROM dbo.CurrentOrders
UNION
SELECT CustomerID,OrderID,Amount FROM dbo.HistoryOrders;
SELECT CustomerID,OrderID,Amount FROM dbo.CurrentOrders
UNION ALL
SELECT CustomerID,OrderID,Amount FROM dbo.HistoryOrders;
SET STATISTICS IO, TIME OFF;The UNION ALL plan commonly concatenates branch outputs. The UNION plan needs a way to establish distinct rows, such as Sort Distinct, Hash Match Aggregate, or a Hash Match whose logical operation is Union. In my test, the UNION plan used Hash Match (Union) and the other plan used Concatenation. The exact operator depends on existing order, estimates, indexes, and optimizer choice. Record the actual plan rather than promising one fixed icon.
Inspect Memory and Spills
Duplicate removal can require a memory grant. Open plan properties for the Sort or Hash Match and read estimated and actual rows, granted memory, and spill warnings. A low row estimate can produce a spill to tempdb. A high estimate can reserve more memory than the query needs and affect concurrency. The extra cost grows with row count and row width.
I test with production-sized branches, because a ten-row example barely shows the difference. Capture CPU, elapsed time, tempdb spill evidence, and memory grant alongside reads. Keeping duplicates sends more rows to the client; that transfer cost belongs in the comparison too. A smaller server plan is not automatically a faster end-to-end report.
Read UNION vs UNION ALL Branch Access Costs
STATISTICS IO reports logical reads for the source tables. Both operators can scan the same sources, so the main difference can be after the scans. If reads differ, inspect plan changes such as index selection, pushdown, or parallelism. Do not attribute every changed read count to the duplicate operator alone.
SELECT COUNT_BIG(*) AS current_count FROM dbo.CurrentOrders;
SELECT COUNT_BIG(*) AS history_count FROM dbo.HistoryOrders;Counts establish scale, not overlap. To test overlap, compare the selected columns or a known unique business key under the same filters. I sample overlap separately, then confirm constraints and load rules that guarantee it cannot appear later. A one-time result of zero duplicates is weaker than an enforced rule.
Prove Branches Are Disjoint Before UNION ALL
If current and history are divided at a clear date boundary, write non-overlapping predicates using half-open intervals. Trusted CHECK constraints on the underlying tables can document valid ranges for the optimizer; verify that they are trusted and match the predicates. A distinct row key or partition rule can give stronger proof than a convention in an ETL script.
SELECT CustomerID,OrderID,Amount
FROM dbo.Orders
WHERE OrderDate < '20250101'
UNION ALL
SELECT CustomerID,OrderID,Amount
FROM dbo.Orders
WHERE OrderDate >= '20250101';These predicates are disjoint by construction. If the same physical row appears in both branches because filters were changed later, the rule is broken and the duplicate shows up in the output. Where the branches are demonstrably disjoint, use UNION ALL directly. Do not rely on the optimizer to remove a distinct step merely because a person knows the ranges do not overlap. Inspect the actual plan after adding constraints.
Know When UNION Beats UNION ALL
Keep UNION when duplicate rows from separate sources are possible and the result contract requires one copy. Explicit DISTINCT within each branch can add more work and still require a final distinct operation. A business-key deduplication rule can need ROW_NUMBER and a precedence rule instead of UNION, because UNION compares full output rows. Choose the operator that implements the desired identity.
A duplicate can signal a data quality issue. UNION can conceal it by silently removing a row. I ask whether the source system should permit the overlap at all. If not, fix the load or key constraint and monitor duplicates rather than using a set operator as a cleanup bandage.
Separate the Final Sort From Duplicate Removal
Be careful with ORDER BY. A final sort can appear in both plans because the result contract requests ordering; do not mistake that sort for UNION duplicate removal. Inspect the operator properties and its location. Likewise, a Merge Join can participate in duplicate processing when inputs are ordered, so compare the complete data flow. The plan cost percentage printed beside an operator is an estimate, while actual spills and elapsed time show runtime work.
When the branch outputs are wide, project only the columns the consumer needs before combining them. Extra columns increase row width in the distinct operation and can change which rows count as duplicates. I test the result set itself after such a projection change because performance and semantics move together.
Recheck UNION vs UNION ALL After Data Growth
Plans can change when one branch becomes much larger. Watch the distinct operator's memory grant and spills over time. Query Store can show runtime changes, while actual plans reveal whether a Sort turned into a Hash Match or began spilling. Keep a benchmark with realistic row width, since wide text columns make duplicate comparison more expensive.
I preserve the semantic test alongside the performance test. If a new history feed starts overlapping current data, the faster query without duplicate removal can become wrong even while its plan stays attractive. Correct row identity comes first; measured operator cost guides the implementation once that identity is settled.
Related reading on this blog: Interview Question of the Week #017: Performance Comparison of Union vs Union All and Remove Duplicate Rows Using UNION Operator.
UNION ALL is not a faster UNION by definition, it is the right operator when duplicates belong.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
