Is there a default SELECT order? [duplicate]

Question

I need to clear up what I mean by order here. I am simply asking about what order the data is fetched in.

If I simply SELECT * FROM table; in SQL Server without an ORDER BY clause, I find that the results always come in the same order. That certainly is not the case with some other database servers, such as PostgreSQL.

Is the observed behaviour actually happening? I presume that the results will be returned in the order the rows were added.

I have not tried with very large tables, but it certainly appears to be the case for the tables I have worked with.

Note:

This question relates only to SQL Server. I quite understand that SQL itself does not have a preferred order, and that is my experience with other databases.

The point is that SQL Server appears to prefer the allocation order, and I wondered whether that's true.

Note 2:

As regards this question’s being a possible duplicate, I have no interest in why rows are being added anywhere in particular. I just want to know whether they are normally returned in allocation order …

Answer 1

_{Community wiki answer:}

There is no "default order". You will only get a specific sort order if you use order by.

If there is no order by, the database is free to return the rows in any order it thinks is most efficient.

See, for example, No Seatbelt – Expecting Order without ORDER BY by Conor Cunningham (Software Architect, SQL Server Engine at Microsoft).

---

I find that the results always come in the same order.

You may observe this, but it is not guaranteed. The behaviour you observe may change at any time, even between executions of the same query on the same database, a few milliseconds apart.

I presume that the results will be returned in the order the rows were added.

No, this is not a safe presumption. Again, you may observe it in a certain test (you don't provide such a test, so it is hard to know what you're referring to exactly). It may happen, for example with a small number of rows inserted into a row store heap, but this is purely an implementation detail, which you cannot rely on remaining constant.

I have not tried with very large tables, but it certainly appears to be the case for the tables I have worked with.

Ok. You may well see different results with larger tables (or different storage engines, or different server configuration settings, or ...)

The point is that SQL Server appears to prefer the allocation order, and I wondered whether that's true.

Nope. Scanning pages in allocation order is one option available to the execution engines, but it is not 'preferred' in any active or design sense. Certainly it may make sense for the engine to traverse a b-tree index in sequence, but this guarantees nothing about the presentation order. There is an example of this in one of the answers to one of your previous questions.

I just want to know whether they are normally returned in allocation order …

If by "allocation order" you mean the order in which rows were inserted, yes this may be common, for very simple queries on very simple tables than have never seen anything except inserts.

If by "allocation order" you mean page ID order (the normal sense of the phrase for SQL Server) then no: non-allocation-order scans are the more common type. But again, all these observations are variables depending on the test conditions, and some of these concepts only exist for the row-mode engine.

Answer 2

It is determined by the query optimizer dependant on the query given.

Article:

SQL Server only guarantees that results are ordered per the columns you specify in an ORDER BY clause

There is no “default” ordering that a query will fall back on outside of an ORDER BY clause.

Results may come back in the order of the clustered index, or they may not Even if results come back in the order of the clustered index on one run of the query, they may not come back in the same order if you run it again If you need results to come back in a specific order, you must be explicit about it in the ORDER BY clause of the query.

Answer 3

SQL standard does not specify in which order the rows are returned, if the query has no ORDER BY.

Some implementations return rows in an order which might seem deterministic, but unless some order is guaranteed in documentation, you can't trust next version of same software or a different data set would behave same way.