Advantages of Using UNION ALL Over UNION in SQL Server

Most of them working in development or SQL environment would have come across UNION and UNION ALL. But, when we look into the cause of using UNION and UNION ALL, it’s pretty interesting not just in terms of results, but also in terms of performance.

Basic difference between UNION and UNION ALL is that, UNION ALL results in almost all the duplicates between the tables, whereas UNION omits from being resulting the duplicate values.

Secondary difference and advantage would be getting the results at faster rate. The results will be generated at a pretty faster rate for UNION ALL when compared to UNION. However, the UNION ALL generating the duplicate results can eventually lead to long time querying.

For example, let’s take count of a table name CUS from two databases, one without duplicates and the other with duplicate counts. Take a look at the script below:

FOR UNION:

SELECT '-----CUSName',0
UNION
      SELECT 'Customer1',COUNT(*) FROM CUS
UNION  
      SELECT 'Customer1',COUNT(*) FROM CUS



Another scenario is to use UNION ALL instead of UNION in the statements to see a different result in the output set. 

UNION ALL:

SELECT '-----CUSName',0
UNION ALL
      SELECT 'Customer1',COUNT(*) FROM CUS
UNION ALL
      SELECT 'Customer1',COUNT(*) FROM CUS
From the above outputs, it is clear that UNION ALL functions in selecting even the duplicate counts. Seeing the example, Customer2 consists of 10 duplicate records, which was put forward along as a redundancy.

You may also need: "10 New Features SQL Server 2016 Does Better than SQL Server 2014"

0 comments: