Order for sets is undefined and undocumented. "Undocumented" in this case means two things:
- We won't be able to provide you with a specific link or quote detailing changes or what the new behavior will be, because it is explicitly not declared. This is by design, and in accordance with the concept of a relational "set".
- Because it's not documented, Microsoft is free to change the behavior at any time they like. An otherwise unremarkable patch delivered via Windows Update can change this (not that it has, but that it could without violating any policies). Even if you tease out a particular behavior today, you shouldn't rely on it, because it could change tomorrow without warning.
This freedom is important for database vendors like Microsoft. It gives them the ability to innovate and make performance-positive changes in their product.
That brings me to my next point. For a long time, there has a been an idea that queries without an ORDER BY clause will return results in accordance with the primary key (clustered index) of the main table in the query. This is false, and has always been false. Results often are returned this way, but only because that is usually the fastest way to serve the query. But there have always been exceptions to this: covering indexes, round-robin table scans, complicated joins and subqueries where the "main" table of the query may change based on what statistics were compiled this month, etc.
In recent years, new concepts like Recursive CTE's, Windowing Functions, and APPLY operations have created numerous new situations where the clustered index order is no longer the fastest way to serve a query. This impact goes beyond just queries that use these new features. As the query engine adds complexity in order to accommodate the new features, it gains new tools to support those features that are also useful in preparing plans for older queries, as well.
ORDER BY
clause to the query.