Home > SQL Server > SQL SERVER – UNION Vs UNION ALL with execution plan – Query Tuning @SQLSERVER

SQL SERVER – UNION Vs UNION ALL with execution plan – Query Tuning @SQLSERVER


Union and Union All set operators are used to combine two or more similar datasets into one resultset. It means, each dataset must contain equal number of columns with same column order and it should be having same data type.

In this blog post, I am going to talk about UNION vs UNION ALL set operators to show how is works as a query and exploring its execution plan to understand the operation it performs.

UNION ALL – Combine all dataset include duplicate record if any.

UNION   – Combine all datasets and produce unique records (exclude duplicate record)

Example

I am using [AdventureWorks2012] database for this demo.

  • Executing the query to performs UNION ALL operation.

pic1Union all operation has just performed read and concatenation operation to produce the result set.

  • Executing the query to performs UNION operation.

pic2UNION operator uses Merge Join to compare each dataset and produce a unique resultset. Merge Join is efficient when the dataset is sorted otherwise query optimizer includes expensive sort operation in to the execution plan. In this case, query optimizer used the non-clustered index and it is already sorted.

Indexed column

pic3I am going to disable this index now and executing the same query with UNION operator and let see the execution plan.

ALTER INDEX [AK_CountryRegion_Name] ON [Person].[CountryRegion] DISABLE
GO

SELECT [Name] FROM [Person].[CountryRegion]
UNION
SELECT [Name] FROM [Person].[CountryRegion]
UNION
SELECT [Name] FROM [Person].[CountryRegion]

pic4Now, the query optimizer includes an expensive sort operation in the plan.

I hope that you have understood the difference between UNION and UNION ALL operators and its execution.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: