Unordered results in SQL

Question

I read it over and over again that SQL is essentially an unordered model. This means that executing the same SQL query several times can return the result in a different order, unless the "order by" clause is included. Can someone explain why the SQL query can return the result in a different order in different instances of the query start? This may not always be, but certainly possible.

Algorithmically, does the query plan really play any role in determining the order of the result set when there is no “order” clause? I mean, when there is a plan request for some request, how does the algorithm not always return data in the same order?

Note. I do not ask about the use of the order, I ask why there is no guarantee, as in an attempt to understand the problems, because of which there can be no guarantee.

Answer 1

Some examples of SQL Server where an exact execution plan may return different ordered results,

• An unordered index scan can be performed either in the placement order or in the order depending on the isolation level.
• The fun scan feature allows you to share scans between parallel requests.
• Parallel plans are often not deterministic, and the order of the results may depend on the degree of parallelism selected at run time and the simultaneous workload on the server.
• If the plan has nested loops with unordered prefetching, it allows the inside of the connection to continue working with data from what was done for I / O

Answer 2

Martin Smith has great examples, but the absolute dead simple way to demonstrate when SQL Server will change the plan used (and therefore the order in which the query will be used without ORDER BY, based on another plan) is to add a coverage index. Take this simple example:

CREATE TABLE dbo.floob ( blat INT PRIMARY KEY, x VARCHAR(32) ); INSERT dbo.floob VALUES(1,'zzz'),(2,'aaa'),(3,'mmm'); 

This will be ordered by clustered PK:

 SELECT x FROM dbo.floob; 

Results:

 x ---- zzz aaa mmm 

Now add an index that will cover the above query.

 CREATE INDEX x ON dbo.floob(x); 

The index causes a recompilation of the above query upon restarting; he is now ordering a new index because this index provides a more efficient way for SQL Server to return results to satisfy the query:

 SELECT x FROM dbo.floob; 

Results:

 x ---- aaa mmm zzz 

SQL Fiddle Demo

Take a look at the plans - they don’t have a sort operator, they simply - without any other order input - relying on the inherent order of the index, and they scan the entire index because they have to (and the cheapest SQL Server way to scan the index is ok) . (Of course, even in these simple cases, some of Martin's response factors may influence a different order, but this is true in the absence of any of these factors.)

According to others, ONLY FOR RELIGION ON THE ORDER - TO ORDER THE ORDER ON . Please write this somewhere. It doesn't matter how many scenarios exist where this belief can break; The fact that even one exists makes it useless to try and find some recommendations when you can be lazy and not use the ORDER BY clause. Just use it, always, or be prepared for the fact that data is not always returned in the same order.

Some related thoughts about this:

• http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior.aspx
• http://sqlblog.com/blogs/aaron_bertrand/archive/2012/02/28/why-people-think-some-sql-server-2000-behaviors-live-on-12-years-later.aspx

Answer 3

Quote from Wikipedia:

"Because SQL is a declarative programming language, SELECT queries define a result set, but do not determine how to compute it. The database translates the query into a" query plan " , which may vary depending on execution , database version, and database software. This functionality is called the "query optimizer" because it is responsible for finding the best possible plan for query execution within the applicable limits. "

It all depends on what the query optimizer chooses as a plan scan, index scan, index search, etc.

Other factors that may affect plan choices are table / index statistics and sniffing parameters, to name a few.

In short, order is never guaranteed without an ORDER BY clause.

Answer 4

It's simple: if you need ordered data, use ORDER BY. It's not hard!

This may not cause you problems today or next week, or even next month, but one day.

I was in a project where we needed to rewrite dozens (or maybe hundreds) of queries after upgrading to Oracle 10g so that GROUP BY was evaluated differently than in Oracle 9i, which means that the queries were not necessarily ordered by grouped columns . Not fun and just avoided.

Remember that SQL is a declarative language, so you tell the DBMS what you want, and then the DBMS develops how to get it. Each time it will return the same results, but each time it can evaluate differently: there are no guarantees.

Just one simple example of where this can cause problems is that new rows appear at the end of the table if you select from the table ... until they do, because you deleted several rows and the DBMS decides to fill the empty space.

There are an unrecognizable number of ways this might go wrong if you are not using ORDER BY.

Why does water boil at 100 ° C? Because the way it was defined.

Why are there no guarantees regarding ordering the result without ORDER BY? Because the way it was defined.

The next time, the DBMS is likely to use the same query plan, and this query plan is likely to return data in the same order: but this is not a guarantee, not even close to the guarantee.

Answer 5

If you do not specify ORDER BY, then the order will depend on the plan that it uses, for example, if the query scanned the table and did not use the index, then the result will be a "natural order" or PC order. However, if the plan defines the use of IndexA, which is based on column A, then the order will be in that order. It makes sense?