SQL Server UNION vs UNION ALL

SQL Server UNION vs UNION ALL

Hi Friends,

Probably, a zillion blog posts AND articles must have been written ON SQL Server UNION vs UNION ALL AND the performance consideration BETWEEN them.

Let this one be one OF them too. AND treat this post AS a reminder :)

I was at a customer site today reviewing SOME T-SQL code AND I came across a couple OF scripts that used UNION operator instead OF UNION ALL.

FROM MSDN:

Combines the results OF two OR more queries INTO a single RESULT SET that includes ALL the ROWS that belong TO ALL queries IN the UNION. The UNION operation IS different FROM USING joins that combine COLUMNS FROM two TABLES.

The following are basic rules FOR combining the RESULT sets OF two queries BY USING UNION:

    The NUMBER AND the ORDER OF the COLUMNS must be the same IN ALL queries.
    The DATA types must be compatible.

Further,

UNION

    Specifies that multiple RESULT sets are TO be combined AND returned AS a single RESULT SET.

ALL

    Incorporates ALL ROWS INTO the results. This includes duplicates. IF NOT specified, duplicate ROWS are removed.

WHEN I asked the developer, why has he used UNION AND IF he specifically wanted the duplicates TO be removed, he looked at me AS IF I just spoke IN Greek. I was stunned too.

So WITH this simple example I explained him that UNION ALL performs faster than UNION, simply because UNION ALL does NOT remove duplicates. So IF has no need OF explicitly removing duplicates, he can USE UNION ALL instead.
Link OF picture OF the example
http://adf.ly/s8YIf