OPTIMAL PERFORMANCE: UNION VS UNION ALL IN SQL SERVER

Introduction

From time to time and there is a need to join information from numerous tables or perspectives into one extensive data set. This might be for tables with comparable data inside a similar database or perhaps there is a need to consolidate comparative data crosswise over databases or even across servers. In this article we will analyse how to use UNION versus UNION ALL commands and how they contrast. In my previous article, I explained differences between a clustered and a non-clustered index.

Optimal Performance Comparison Union vs. Union All

The UNION command is utilised to choose the related data from two tables, which resembles a JOIN command. In any case, when utilising UNION order, all the chose columns should be of similar data type. With UNION, just distinct values are chosen.
UNION ALL command is equivalent to UNION command, aside from that UNION ALL chooses every values. Hence, it does not remove any duplicate values.
Since this does not evacuate duplicate values this procedure is speedier, but rather on the off chance that you don't need duplicate records you should utilise the UNION command.
The decision is easy if you know the answer to the Question "Would i like to take out duplicate rows when I join 2 result sets with this operation?”
On the off chance that yes, at that point use UNION . In any case, know that a UNION will make the additional stride of checking the outcome set and giving you ONLY unmistakable rows. So it will perform slower on the grounds that it needs to sort the result set.
On the off chance that its ensured that your two result sets which you need to join won't have any overlapping rows, or you couldn't care less if there are duplicate/over lapping rows, at that point you should use UNION ALL. Since UNION ALL does not need to make the additional stride of checking the result sets and separating just the unique values, so UNION ALL is generally preferable performing over UNION, since it doesn't need to sort the result set.

Video: UNION VS UNION ALL IN SQL SERVER

Creating Database Tables For Check Comparison

create table compareUnionvUnionaAll (col varchar(10))

insert into compareUnionvUnionaAll values ('technology')
insert into compareUnionvUnionaAll values ('crowds')
insert into compareUnionvUnionaAll values ('sql')
insert into compareUnionvUnionaAll values ('.net')

create table compareUnionvUnionaAll1 (col varchar(10))

insert into compareUnionvUnionaAll1 values ('android')
insert into compareUnionvUnionaAll1 values ('MEAN')
insert into compareUnionvUnionaAll1 values ('sql')
insert into compareUnionvUnionaAll1 values ('.net')

List Records From Both Tables

SELECT * FROM compareUnionvUnionaAll;
SELECT * FROM compareUnionvUnionaAll1;

Results Overview

Lets Execute Union All Command

select * from compareUnionvUnionaAll
Union ALL
select * from compareUnionvUnionaAll1

Result Overview of Union ALL Command

Lets Check Execution Plan For Union ALL Optimal Comparison

UNION Command Results Overview

select * from compareUnionvUnionaAll
Union
select * from compareUnionvUnionaAll1

Lets Check For Union Optimal Comparison

I think you may notice that UNION query cost is more than UNION ALL which is marked red (63%).

Conclusion

UNION and UNION ALL collated as they are completely apart from each other, they both have different purposes.

Relevant Reading

• Saving Changes not permitted in SQL Server
• Understanding The Relationships in SQL Server
• How to avoid duplicate records in SQL view using Entity Framework