0

Suppose I have a table with 2 columns and 3 rows:

A|B
1|1
1|2
1|3

And I write

SELECT TOP 1 B FROM MyTable ORDER BY A

SQL Server will arbitrarily pick a value to return. But it seems to consistently return the same arbitrary value no matter how many times I run it. Is there any way to figure out which value it will pick? Is there any way to know if it will continue to pick the same value in the future? Is there any way to know if it picked the same value in the past?

I am asking because I want to guess the past behavior of some ugly legacy code.

Brad
  • 1,360
  • 4
  • 18
  • 27
  • 2
    I would assume (which I probably shouldn't) that the sorting is based however the data on whatever page in memory it reads from is sorted. So as long as the index or table haven't changed, including data, then it would be reasonable to assume (there I go again) that it would come back the same. That said I wouldn't trust it since it is all assumptions. – SteveB Mar 14 '19 at 20:58
  • 1
    Possible duplicate of [Unordered results in SQL](https://stackoverflow.com/questions/21371176/unordered-results-in-sql) – Eric Brandt Mar 14 '19 at 21:04
  • I just flagged this as a possible duplicate. Whatever the mods decide, you should check the link. There's a ton of good stuff there. – Eric Brandt Mar 14 '19 at 21:04

1 Answers1

0

The order in this case is not guaranteed.

You will tend to get the same results, but there are a few things that can change it. I can think of four examples off the top of my head, but there are likely others:

  1. If you change the indexing on the table
  2. With Enterprise Edition, Advanced Scanning can piggy-back on the table or index scan already in progress for another query, where suddenly the "top" result is different.
  3. If the table is big enough for multiple pages, and one day you happen to have a different page already in memory than before.
  4. If the table happens to be stored in (A, B) order, but you have an index on (A, B DESC), you might get different result depending on whether the table or the index is already loaded to memory.

Note that a few of these can cause results to change suddenly if memory pressure causes Sql Server to unload a relevant page.

As a more general case, don't think only in terms of one query on the table, but also many different queries on the same unordered data. For example, if you join to this table, based on criteria from another table that matches an index, Sql Server is free to return the first result it finds that matches, and it might be a different row for the join than for the raw query, simply because the join matches well on a different index.

In short, if this matters, be more strict with the ORDER BY clause.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794