:::: MENU ::::

Choosing between UNION and UNION ALL

  • Apr 29 / 2009
  • 0
dbDigger, Performance Tunning and Optimization, T-SQL Interview Questions

Choosing between UNION and UNION ALL

When using the UNION statement, the fact should be considered that, by default, it performs the equivalent of a SELECT DISTINCT on the final result set. In other words, UNION takes the results of two like record sets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final record set.
If you know that there are duplicate records, and this presents a problem for your application, then by all means use the UNION statement to eliminate the duplicate rows.
On the other hand, if you know that there will never be any duplicate rows, or if there are, and this presents no problem to your application, then you should use the UNION ALL statement instead of the UNION statement.

Advantage of UNION ALL

The advantage of the UNION ALL is that is does not perform the SELECT DISTINCT function, which saves a lot of unnecessary SQL Server resources from being using. The better performance of UNION ALL in suitable cases can be verified through execution plans.

Consult us to explore the Databases. Contact us