>SQL Server: UNION ALL or UNION


>

I have seen many database developers using UNION, when they need to merge result sets of two queries, also when they don’t need unique results out of input SELECT queries. The UNION clause processes the result set from the two SELECT statements, removing duplicates from the final result set and effectively running DISTINCT on each query.
Though query optimizer is smart enough, that when it detects that both of your SELECT queries contains distinct rows, it will automatically choose same execution plan as UNION ALL operation will. Still as good query designer, we should always use UNION ALL instead of UNION when
  • Duplicate rows in final result sets are allowed
  • Result sets of the SELECT statements participating in the UNION clause are exclusive to each other
Advertisements

Posted on January 25, 2011, in Performance Tuning, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

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: