performance of union versus union all

sql-server performance union union-all
LaBracca picture LaBracca · Sep 2, 2010 · Viewed 23.7k times · Source

I have to run a select statement across several tables. I am sure the tables return different records. I am anyway using UNION ALL.

Is it better to use UNION or of UNION ALL in performance terms when I am sure the tables return different records?

Answer

Joe Stefanelli picture Joe Stefanelli · Sep 2, 2010

UNION ALL will perform better than UNION when you're not concerned about eliminating duplicate records because you're avoiding an expensive distinct sort operation. See: SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison

Related questions

LIKE vs CONTAINS on SQL Server

Which one of the following queries is faster (LIKE vs CONTAINS)? SELECT * FROM table WHERE Column LIKE '%test%'; or SELECT * FROM table WHERE Contains(Column, "test");

Read More
What do Clustered and Non clustered index actually mean?

I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes. I googled and what I found was : A clustered index is a special type …

Read More
When should I use cross apply over inner join?

What is the main purpose of using CROSS APPLY? I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind) I …

Read More