SQL Server Performance Series – Union vs Union All

By Navratan Verma

Union Operator  is used to combine multiple result sets into one and also removes any duplicates rows that exist.  it gives distinct values across all columns in the result set.

UNION ALL OPERATOR:  is used to combine multiple result sets into one, but it does not remove any duplicate rows.  Since this does not remove duplicate rows the cost is less and performs faster, but if you don’t want duplicate records you will need to use the UNION operator instead.

Performance TIP:  Compared UNION ALL operator, UNION operator has the extra overhead of removing duplicate rows and sorting results. So, If we know that all the records returned by our query is unique from union then use UNION ALL operator instead of UNION Operator.

Download the script used here : Union

Here is the execution plan results First query is uses union and second union all.

UnionAll

Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *