0

I have a table with a column where the data happens to be the same, e.g.

Col | Value
-----------
 A  |   1
 A  |   2
 A  |   3

If I do a SELECT * FROM MY_TABLE ORDER BY Col, even though the data are the same, I have been getting consistent ordering, which I am not sure if this is just the way it is or pure luck.

My gut feeling is this is not reliable, but the reality showed consistency so far.

Question: is there any situation that the result ordering can return differently, provided that this table has been storing the same data, no insertion nor deletion nor update to it so far?

Obviously I can update the query to order by more columns to get reliable results, but I am just interested to know about this phenomenon, rather than asking for suggestion here. Thank you.

user1589188
  • 5,316
  • 17
  • 67
  • 130
  • Do `ORDER BY Col, Value` to get a consistent result. – jarlh Sep 30 '20 at 09:25
  • @jarlh Sure thanks, but thats not what my question is about. – user1589188 Sep 30 '20 at 09:26
  • 4
    It's luck. It shows consistency because the engine has no need for varying the results, but it *can* break. Most likely scenario where this would happen is if the table grows large and the query starts using parallelism. – Jeroen Mostert Sep 30 '20 at 09:28
  • 1
    Unless the `ORDER BY` creates a unique sort position for every row, then rows that have the same sort position will be given an arbitrary sort order, which can change **every** time the query is run. – Thom A Sep 30 '20 at 09:34
  • 1
    Getting a carousel scan (where your query uses an already-in-progress table scan for a different query) means that, yes, you can get different results even if no changes have been made to the table. There's nothing interesting here - tell the system *what you need* and don't assume it'll guarantee you *anything more*. – Damien_The_Unbeliever Sep 30 '20 at 09:42
  • 1
    seems a dupe of [The order of a SQL Select statement without Order By clause](https://stackoverflow.com/questions/10064532/the-order-of-a-sql-select-statement-without-order-by-clause) or many more – underscore_d Sep 30 '20 at 09:53

1 Answers1

0

From Microsoft documentation:

Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.

Any order not stated explicitly is subject to change.

casenonsensitive
  • 955
  • 2
  • 9
  • 18