>>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.

I also answered that UNION ALL is faster and also was puzzled where UNION ALL would be slower. The answer was when UNION would return a huge result set the UNION ALL might be slower, I never verified this, BTW.

There was another interesting question on the same interview - two SQL server databases with very similar table structures return different result sets - how would you figure out why. I was not able to come up with satisfactory answer, but I'm thinking now it may be a difference in COLLATE settings for the database.