sql server - performance of union versus union all

Question

I have to run a select statement across several tables. I am sure the tables return different records. I am anyway using UNION ALL.

Is it better to use UNION or of UNION ALL in performance terms when I am sure the tables return different records?

Answer 1

UNION ALL will perform better than UNION when you're not concerned about eliminating duplicate records because you're avoiding an expensive distinct sort operation. See: SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison