When no 'Order by' is specified, what order does a query choose for your record set?
If you don't specify an ORDER BY
, then there is NO ORDER defined.
The results can be returned in an arbitrary order - and that might change over time, too.
There is no "natural order" or anything like that in a relational database (at least in all that I know of). The only way to get a reliable ordering is by explicitly specifying an ORDER BY
clause.
Update: for those who still don't believe me - here's two excellent blog posts that illustrate this point (with code samples!) :
- Conor Cunningham (Architect on the Core SQL Server Engine team): No Seatbelt - Expecting Order without ORDER BY
- Alexander Kuznetsov: Without ORDER BY, there is no default sort order (post in the Web Archive)
how does SELECT TOP works when no order by is specified?
There is no guarantee which two rows you get. It will just be the first two retrieved from the table scan.
The TOP
iterator in the execution plan will stop requesting rows once two have been returned.
Likely for a scan of a heap this will be the first two rows in allocation order but this is not guaranteed. For example SQL Server might use the advanced scanning feature which means that your scan will read pages recently read from another concurrent scan.
The order of a SQL Select statement without Order By clause
No, that behavior cannot be relied on. The order is determined by the way the query planner has decided to build up the result set. simple queries like select * from foo_table
are likely to be returned in the order they are stored on disk, which may be in primary key order or the order they were created, or some other random order. more complex queries, such as select * from foo where bar < 10
may instead be returned in order of a different column, based on an index read, or by the table order, for a table scan. even more elaborate queries, with multipe where
conditions, group by
clauses, union
s, will be in whatever order the planner decides is most efficient to generate.
The order could even change between two identical queries just because of data that has changed between those queries. a "where" clause may be satisfied with an index scan in one query, but later inserts could make that condition less selective, and the planner could decide to perform a subsequent query using a table scan.
To put a finer point on it. RDBMS systems have the mandate to give you exactly what you asked for, as efficiently as possible. That efficiency can take many forms, including minimizing IO (both to disk as well as over the network to send data to you), minimizing CPU and keeping the size of its working set small (using methods that require minimal temporary storage).
without an ORDER BY
clause, you will have not asked exactly for a particular order, and so the RDBMS will give you those rows in some order that (maybe) corresponds with some coincidental aspect of the query, based on whichever algorithm the RDBMS expects to produce the data the fastest.
If you care about efficiency, but not order, skip the ORDER BY
clause. If you care about the order but not efficiency, use the ORDER BY
clause.
Since you actually care about BOTH use ORDER BY
and then carefully tune your query and database so that it is efficient.
When joins are used in a query, will the order of records change each time we execute the query?
The best solution to your current problem is to not even consider relying on any perceived internal order to your SQL tables, because there isn't any such order. Instead, you should always use a proper ORDER BY
clause at the end of your query if you want to sort your data a certain way.
Regarding your direct question, I suppose it might be possible that in between your select queries, the index structure(s) of your table(s) could be reorganized such that the default ordering changes. Or another process could come in and insert/update/delete data. But again, don't even bother trying to plan for this, and instead use an ORDER BY
clause.
Default row order in SELECT query - SQL Server 2008 vs SQL 2012
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
SQL best practice to deal with default sort order
There is no default sort order. Even if the table has a clustered index, you are not guaranteed to get the results in that order. You must use an order by clause if you want a specific order.
Related Topics
What Is Self Join and When Would You Use It
How to Delete Duplicate Entries
How to Query a Value in SQL Server Xml Column
Generating a Random & Unique 8 Character String Using MySQL
MySQL Search and Replace Some Text in a Field
MySQL on Duplicate Key Update For Multiple Rows Insert in Single Query
What MySQL Database Tables and Relationships Would Support a Q&A Survey With Conditional Questions
Difference Between Natural Join and Inner Join
SQL How to Make Null Values Come Last When Sorting Ascending
Fastest Way to Remove Non-Numeric Characters from a Varchar in SQL Server
Using an Alias in SQL Calculations
How to Rewrite Is Distinct from and Is Not Distinct from in SQL Server 20008R2
SQL Server - How to Lock a Table Until a Stored Procedure Finishes
Why Does MySQL Report a Syntax Error on Full Outer Join
SQL Switch/Case in 'Where' Clause