0

My company recently migrated a .NET 1.1/SQL Server 2000 web application to SQL Server 2012. We've had a couple of support calls relating to unusual behaviour - particularly with stored procedure queries without ORDER BY clauses.

I appreciate having functionality relying on the ordering of these types of queries is bad practice - but does anyone know if there is official Microsoft documentation on the default ordering changes between SQL Server versions?

1
  • It is highly likely that the changes in the query optimizer from 2000 to 2012 has chosen a more optimal path to satisfy the query in the stored proc. The more optimal path likely involves using an index that is resulting in the rows being returned in a different order. The solution is and only ever will be to add an ORDER BY clause to the query. Dec 17, 2015 at 22:08

3 Answers 3

5

SQL Server 2012 does not guarantee the order of rows returned in SQL 2012. Neither did 2000, for that matter. (The citation I found for that is actually 2005, but close enough.)

Essentially, the SQL Server Query Optimizer is guaranteeing that the internal operator in the query tree will process its input in a particular order. There is no corresponding guarantee that the output of that operator will imply that the next operator in the query tree is performed in that order. The reordering rules can and will violate this assumption (and do so when it is inconvenient to you, the developer ;). Please understand that when we reorder operations to find a more efficient plan, we can cause the ordering behavior to change for intermediate nodes in the tree. If you’ve put an operation in the tree that assumes a particular intermediate ordering, it can break.

Basically: You got lucky up until now and it finally bit you.

This article by Itzik Ben-Gan may help you. A sample:

One of the most important aspects of sets is implied in Cantor’s definition. He doesn’t mention the order of the elements in a set because the order isn’t important. That’s one of the most difficult concepts for people to grasp when querying a table—namely, understanding that there are no assurances that the data being queried will be consumed in a particular order. For example, some people assume that when they query a table that has a clustered index, they’ll get the data back in clustered index order. From the language perspective, that’s not guaranteed because what you’re querying is a set and what you’re getting back is a set. SQL Server knows that the language doesn’t provide any ordering assurances, so it scans the data in any order that it likes—including not in clustered index order. People often use techniques that violate set-based and relational concepts in the sense that the results’ correctness relies on the data being consumed in index order, which SQL Server has never guaranteed. I cover a couple of classic examples in my web-exclusive articles “Multi-Row Variable Assignment and ORDER BY” and “Ordered UPDATE and Set-Based Solutions to Running Aggregates”.

Ben-Gan wrote the SQL Server 70-461 training book and emphasizes that point in there as well.

I wish I had something better to tell you, but... no, there is no "default order" and therefore you shouldn't rely on it and it's not documented.

0
2

Order for sets is undefined and undocumented. "Undocumented" in this case means two things:

  1. 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".
  2. 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.

0

Just to add more On sql 2k8 enterprise there is a new feature called mery go around.
This feature in the essence reusing a table scan query from other thread.
http://www.sanssql.com/2013/07/merry-go-round-scans-in-sql-server.html

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .