If you omit the ALL clause in an UNION statement, SQL Server must delete duplicate values, which in turn means that it has to sort the two sub-resultsets that have to be combined. Needless to say, this is a time-consuming operation.
In most cases, you decide whether to use the ALL clause depending on your application requirements, and therefore you have no choice. However, if you are 100% sure that the two sub-resultsets have no duplicate rows, you can sensibly speed up execution by adding an explicit ALL clause, as in:
SELECT Name, State FROM USCustomersUNION ALLSELECT Name, Country FROM AbroadCustomers
Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
