-1

This question already has an answer here:

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 …

marked as duplicate by Brent Ozar sql-server Sep 26 '17 at 14:31

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

10

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.

2

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

Article

I've learned something here. I always thought it was by the clustered index

  • 4
    While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – hot2use Sep 26 '17 at 10:39
  • Noted. Will amend when back at PC – SEarle1986 Sep 26 '17 at 10:53
2

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.