CODEWITHSUNDEEP
sql-server
Peter
Peter

Reputation: 11

Does SQL Server randomly sort results when no ORDER BY is used? Why?

I have a query in SSMS that gives me the same number of rows but in a different order each time I hit the F5 key. A similar problem is described in this post:

Query returns a different result every time it is run

The response given is to include an ORDER BY clause because, as the response in that post explains, SQL Server guesses the order if you don't give it one.

OK, that does fix it, but I'm confused about what it is that SQL Server is doing. Tables have a physical order whether they are heaps or have clustered indexes. The physical order of each table does not change with every execution of the query which also does not change. We should see the same results each time! What's it doing, accessing tables in their physical orders and then, instead of displaying the results by that unchanging physical order, it randomly sorts the results? Why? What am I missing? Thanks!

Upvotes: 1

Views: 139

Answers (3)

Ben Thul
Ben Thul

Reputation: 32707

One way that you can get different ordering is if parallelism is at play. Imagine a simple select (i.e. select * from yourTable). Let's say that the optimizer produces a parallel plan for that query and that the degree of parallelism is 4. Each thread will process (roughly) 1/4 of the table. But, if yours isn't the only workload on the server, each thread will go between status of running and runnable (just by the nature of how the SQLOS schedules threads, they will go into runnable from time to time even if yours is the only workload on the server, but is exacerbated if you have to share). Since you can't control which threads are running at any given time, and since each thread is going to return its results as soon as it's retrieved them (since it doesn't have to do any joins, aggregates, etc), the order in which the rows comes back is non-deterministic.

To test this theory, try to force a serial plan with the maxdop = 1 query hint.

Upvotes: 1

Grantly
Grantly

Reputation: 2556

SQL server uses a set of statistics for each table to assist with speed and joins etc... If the stats give ambiguous choice for the fatest route, the choice by SQL can be arbitrary - and could require slightly different indexing to achieve... Hence a different output order. The physical order is only a small factor in predicting order. Any indexes, joins, where clause can affect the order, as SQL will also create and use its own temporary indexes to help with satisfying the query, if the appropriate indexes do not already exist. Try re calculating the statistics on each table involved and see if there is any change or consistency after that.

You are probably not getting random order each time, but rather an arbitrary choice between a handful of similarly weighted pathways to get the same result from the query.

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

Simple - if you want records in certain order then ask for them in a certain order.

If you don't asked for an order it does not guess. SQL just does what is convenient.

Upvotes: 2

Related Questions