Microsoft SQL Server Query Hints UNION hints

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

When you use UNION operator on two query results, Query optimizer (QO) can use following operators to create a union of two result sets:

  • Merge (Union)
  • Concat (Union)
  • Hash Match (Union)

You can explicitly specify what operator should be used using OPTION() hint:

select OrderID, OrderDate, ExpectedDeliveryDate, Comments
from Sales.Orders
where OrderDate > DATEADD(day, -1, getdate())
UNION
select PurchaseOrderID as OrderID, OrderDate, ExpectedDeliveryDate, Comments
from Purchasing.PurchaseOrders
where OrderDate > DATEADD(day, -1, getdate())
OPTION(HASH UNION)
-- or OPTION(CONCAT UNION)
-- or OPTION(MERGE UNION)


Got any Microsoft SQL Server Question?