Will the order of the record retrieved using a select query, be in the same order if retrieved infinite number of times, each time using a new connection? (to the same table, same select)
What order method is used when none is specified?
views:
50answers:
4No, not necessarily.
TomTom
2010-06-26 06:19:13
+2
A:
Depends totally on the query - basically on the way it thinks it will get the query processed fastest.
Which, for all practical means, means the order is "random" (as in: not determined), per standard SQL specifications.
TomTom
2010-06-26 06:20:02
+3
A:
If you do not define an ORDER BY clause, there's no implied ordering.
It might happen to look like it's being ordered by the primary key or the clustering key - but it's not guaranteed to be that way.
So: if you need a specific order, specify an ORDER BY.
marc_s
2010-06-26 07:07:31
The so called "execution plan" which is built by the server decides how to perform the query. If you add a new index, or the statistics on the data changes over time, the execution plan might differ from one run to another. Hence, the answer provided by marc_s is correct.
lmsasu
2010-06-26 08:50:13
A:
Will the order of the record retrieved using a select query, be in the same order if retrieved infinite number of times, each time using a new connection?
No. There is no guaranteed "natural" order.
As others have suggested, your results will often be ordered by primary key, but that's not guaranteed, and once you add JOINs there's not even that to go by.
egrunin
2010-06-26 07:52:06