>>It all depends on his requirements. BTW, I had an SQL Server interview question regarding difference between UNION and UNION ALL.
>>
>>The further question on this was what is usually faster (first or second) and is there a situation when UNION ALL will be slower than UNION?
>
>In my observations (not proper tests) UNION ALL is fasted that just UNION. Just because UNION is something like:
>

>SELECT Fld1, Fld2 FROM Table1;
>UNION ALL;
>SELECT Fld1, Fld2 FROM Table2;
>LEFT JOIN (SELECT Fld1, Fld2 FROM Table1) Tbl1;
>     ON Table2.Fld1 = Tbl1.Fld1;
>        Table2.Fld2 = Tbl1.Fld2;
>WHERE Tbl1.Fld1 IS NULL
>

>This is of course very rough example. But when you use UNION instead of UNION ALL you HAVE to filtert and that filter takes time :-).
>
>About when UNION ALL is slower than UNION kill me I don't know. My first answer is "NEVER" but I suspect that if there is such a question maybe there are occasions when that statements is true. Maybe for huge tables and proper indexes the filtering could be faster than just APPEND, but as I said I am not sure at all.

Could it be that the definition of faster includes the time to transmit the resulting data set? In that case, if UNION ALL results in (say) 1,000,000 records while UNION results in only 5000 the transmission time for the 995000 extra records is more than the time to filter them?