Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
People often forget to use UNION ALL when they can. How is it different from UNION and why should you use it instead if you can?
When you do a UNION, SQL Server will eliminate any records that are duplicated from one SELECT statement to the other. It does this, in effect, by adding a DISTINCT clause to the query. This causes the query to take more time to run. However, many times the person writing the query knows each SELECT block will be mutually exclusive. For instance if the first SELECT picks out data about active contracts and the second SELECT picks out data about inactive contracts (and you know a contract cannot ever be in both states simultaneously), then the DISTINCT is not needed. In a case like this UNION ALL should be used as it does not attempt to grab distinct records and thus runs faster, often considerably faster.
If you have a slow running UNION statement, try UNION ALL and see if the same number of records are being returned. But don't use this as your only basis for changing, just because there are no duplicate records now, doesn't mean there won't be any. It just means you should examine the conditions on the query to see if they really are mutually exclusive. Of course if the number of records is different, you know the different SELECT statements are not mutually exclusive and UNION ALL is not the right choice unless you want to see the duplicates.
It is good to test the other way as well, if you get fewer records with UNION than UNION ALL when you expected them to be mutually exclusive, you may have either a data problem, a database design problem (likely a missing unique index), or a problem with how the SELECT statements are written.