UNION vs UNION ALL in SQL Server

UNION stacks rows. UNION ALL keeps duplicates. UNION distincts them, which means a sort. If the two piles cannot overlap, use ALL.

This week and a one-off extract

SELECT order_id, total_amount, 'live' AS source FROM Orders
WHERE order_date >= CAST(GETDATE() AS date)
UNION ALL
SELECT order_id, total_amount, 'archive' FROM OrdersArchive
WHERE order_date >= CAST(GETDATE() AS date);

Column count and types must match. Names come from the first SELECT.