Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Default row order in SELECT query - SQL Server 2008 vs SQL 2012

Our team recently upgraded our databases from SQL Server 2008 to SQL Server 2012. One breaking change we noticed was in the default order of rows returned by the SELECT statement, i.e. when an explicit ORDER BY clause is not specified.

As per MSDN, SQL Server 2012 does not gaurantee the order of the rows returned unless an ORDER BY clause is specified.

We have 2500+ stored procedures across 5 databases that have SELECT statements without an ORDER BY clause and it will be a sizeable effort to add the ORDER BY clause manually to match the behavior in SQL Server 2008. Is there a setting or faster way of doing this?

The other option, which hasn't been explored, is to downgrade to SQL Server 2008. How difficult would this be?

like image 614
Channs Avatar asked Oct 07 '14 12:10

Channs


People also ask

Which is the default sorting order in SELECT?

This column is also specified in the column list of Select statement. It uses a default sort method (ascending) because we have not specified any sort order in this query. If we want to sort out results in descending order on birthdate column, we can specify DESC in order by clause.

What is the default storing order in SQL?

Note: The default ORDER is ascending order and result-set is sorted in ascending order based on the field that is specified in the SQL query.

What is the order of SQL SELECT?

Six Operations to Order: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. By using examples, we will explain the execution order of the six most common operations or pieces in an SQL query. Because the database executes query components in a specific order, it's helpful for the developer to know this order.

Are SQL rows ordered?

SQL results NEVER have an order you can rely on without using an ORDER BY clause. SQL is built around set theory.


1 Answers

You need to go back and add ORDER BY clauses to your code because without them the order is never guaranteed. You were "lucky" in the past that you always got the same order but it wasn't because SQL Server 2008 guaranteed it in anyway. It most likely had to do with your indexes or how the data was being stored on the disk.

If you moved to a new host when you upgraded the difference in hardware configuration alone could have changed the way your queries execute. Not to mention the fact that the new server would have recalculated statistics on the tables and the SQL Server 2012 query optimizer probably does things a bit differently than the one in SQL Server 2008.

It is a fallacy that you can rely on the order of a result set in SQL without explicitly stating the order you want it in. SQL results NEVER have an order you can rely on without using an ORDER BY clause. SQL is built around set theory. Query results are basically sets (or multi-sets).

Itzik Ben-Gan gives a good description of set theory in relation to SQL in his book Microsoft SQL Server 2012 T-SQL Fundamentals

Set theory, which originated with the mathematician Georg Cantor, is one of the mathematical branches on which the relational model is based. Cantor's definition of a set follows:

By a "set" we mean any collection M into a whole of definite, distinct objects m (which are called the "elements" of M) of our perception or of our thought. - Joseph W. Dauben and Georg Cantor (Princeton University Press, 1990)

After a thorough explanation of the terms in the definition Itzik then goes on to say:

What Cantor's definition of a set leaves out is probably as important as what it includes. Notice that the definition doesn't mention any order among the set elements. The order in which set elements are listed is not imporant. The formal notation for listing set elements uses curly brackets: {a, b, c}. Because order has no relevance you can express the same set as {b, a, c} or {b, c, a}. Jumping ahead to the set of attributes (called columns in SQL) that make up the header of a relation (called a table in SQL), an element is supposed to be identified by name - not ordinal position. Similarly, consider the set of tuples (called rows by SQL) that make up the body of the relation; an element is identified by its key values - not by position. Many programmers have a hard time adapting to the idea that, with respect to querying tables, there is no order among the rows. In other words, a query against a table can return rows in any order unless you explicitly request that the data be sorted in a specific way, perhaps for presentation purposes.

But regardless of the academic definition of a set even the implementation in SQL server has never guaranteed any order in the results. This MSDN blog post from 2005 by a member of the query optimizer team states that you should not rely on the order from intermediate operations at all.

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.

This blog post by Conor Cunningham (Architect, SQL Server Core Engine) "No Seatbelt - Expecting Order without ORDER BY" is about SQL Server 2008. He has a table with 20k rows in it with a single index that appears to always return rows in the same order. Adding an ORDER BY to the query doesn't even change the execution plan, so it isn't like adding one in makes the query more expensive if the optimizer realizes it doesn't need it. But once he adds another 20k rows to the table suddenly the query plan changes and now it uses parallelism and the results are no longer ordered!

The hard part here is that there is no reasonable way for any external user to know when a plan will change . The space of all plans is huge and hurts your head to ponder. SQL Server's optimizer will change plans, even for simple queries, if enough of the parameters change. You may get lucky and not have a plan change, or you can just not think about this problem and add an ORDER BY.

If you need more convincing just read these posts:

  • Without ORDER BY, there is no default sort order. - Alexander Kuznetsov
  • Order in the court! - Thomas Kyte
  • Order of a Result Set in SQL - Timothy Wiseman
like image 160
Mike D. Avatar answered Sep 29 '22 23:09

Mike D.