SQL Server: UNION ALL or UNION

I have seen many database developers using UNION, when they need to merge result sets of two queries, also when they don’t need unique results out of input SELECT queries. The UNION clause processes the result set from the two SELECT statements, removing duplicates from the final result set and effectively running DISTINCT on each query.

Though query optimizer is smart enough, that when it detects that both of your SELECT queries contains distinct rows, it will automatically choose same execution plan as UNION ALL operation will. Still as good query designer, we should always use UNION ALL instead of UNION when

• Duplicate rows in final result sets are allowed
• Result sets of the SELECT statements participating in the UNION clause are exclusive to each other