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

Hmmm. My first question would be "what is WHERE clause?".
And if there is one I would check which fields are involved there?
But believe me, the COLLATION answer never comes to my mind :-)
(as you saw when Michale asks about some SQL procedures and he has case-sensitive SQL Server).
BTW did you saw anybody that case-sensitive COLLATION comes to his/her mind?

Just because I am NOT a DBA I expect everything is OK on that server or at least for MY Database.
When it is NOT... (you don't want to hear the words I spell...)