DISTINCT keyword eliminates the duplicates in the result set. However, it uses a lot of computer resources.
With comparison of the above two screenshots, the Clustered Index Seek costs the same. It is only 25% of the total cost with DISTINCT; whereas it costs 100% of the total cost without DISTINCT.
Observation: if you don't care about the duplicates in your result set, or you know there is no duplicates in your data, you should not use the DISTINCT keyword.
UNION statement specifies that multiple result sets are to be combined and returned as a single result set. By default, the DISTINCT will be used in the result set.
UNION ALL will boost the performance--no DISTINCT SORT operator cost.
Observation: UNION ALL should be used when duplication is not an issue in your result set.
