SQL Server UNION - What is the default ORDER BY Behaviour

There is no default order.

Without an Order By clause the order returned is undefined. That means SQL Server can bring them back in any order it likes.

EDIT:Based on what I have seen, without an Order By, the order that the results come back in depends on the query plan. So if there is an index that it is using, the result may come back in that order but again there is no guarantee.

In regards to adding an ORDER BY clause:

This is probably elementary to most here but I thought I add this.Sometimes you don't want the results mixed, so you want the first query's results then the second and so on. To do that I just add a dummy first column and order by that. Because of possible issues with forgetting to alias a column in unions, I usually use ordinals in the order by clause, not column names.

For example:

SELECT 1, * FROM xxx WHERE z = 'abc'UNION ALLSELECT 2, * FROM xxx WHERE z = 'def'UNION ALLSELECT 3, * FROM xxx WHERE z = 'ghi'ORDER BY 1

The dummy ordinal column is also useful for times when I'm going to run two queries and I know only one is going to return any results. Then I can just check the ordinal of the returned results. This saves me from having to do multiple database calls and most empty resultset checking.

Just found the actual answer.

Because UNION removes duplicates it does a DISTINCT SORT. This is done before all the UNION statements are concatenated (check out the execution plan).

To stop a sort, do a UNION ALL and this will also not remove duplicates.