Thursday, October 24, 2013

Union All and Union clause in SQL Server TSQL Development

Union all and union are two clauses which are part of TSQL development and used for performing multiple operation. Both perform same kind of activity with a marginal difference.
Union and Union all both are used to merge multiple record-set and transform it into a single record-set.
Let say there are record-set
select 1,1,1
select 1,1,1
then output would be
1,1,1
1,1,1
It has few condition to merge the record-set
No of columns in both record-sets should be the same
Th data types off these columns should be similar.

Now we need to understand what is the basic difference between the union and union all.
Union all merge both the record-set as it is while union produce only unique rows from both the data set.
Let say record-set 1 has 12 rows and record-set 2 has 15 rows and five rows are same in both record-set.
So in this case
Union all will return 12 + 15=27 rows.
Union will return 7 unique from record-set 1 +   10 unique from record-set 2 and 5 common rows in both record-set = 7+10+5=22 rows.
Performance:
Union all is faster then Union as Union removes duplicate rows. SQL server need to do some extra comparison and compare each row with every row in the record-set. if suppose there are millions of rows then using Union will be a huge toll on performance.
Avoid using union for better performance of query

Trick questions for Union and Union all.

No comments:

Post a Comment