SQL 2008 - resultset order issue

Question

We are using SQL Server 2008. We have a table called response which has a primary key called response_id. It also has a column called bid_id. When we execute the query

‘select * from response where bid_id = x’

without an ‘order by’ we are getting results in mostly ascending order (default), but once in a while in descending order (very random). Is it possible in sql that the same sql query might return a resultset in different order if executed several times without order by? We used to have SQL Server 2000 till 5 months ago and never faced this problem. Does SQL Server 2008 deal differently with sql queries without ‘order by’?

Thanks.

Answer 1

In any SQL statement, on any database, you need to define an ORDER BY clause to guarantee the order is consistent.

Answer 2

Do you mean this happens for varying values of x?

As OMG Ponies says the order is not guaranteed unless you specify one.

In terms of an explanation perhaps you are getting a clustered index scan when it thinks there will be a significant portion of rows matching the bid_id = x criteria, hence them being in order of response_id, but it is looking them up via a non clustered index on the bid_id column when it expects fewer records to match the condition. These different access strategies could lead to them being returned in different orders. Adding an ORDER BY would cause it to favour a strategy that will leave them sorted or it would add an additional sort step to the plan before the results get returned.

Answer 3

Always use ORDER BY if order is important.

What is cached in memory as opposed to what is on disk will affect the order in which the SQL Server engine gets the rows, thus affecting the order of the rows if ORDER BY is not used.

The following URL has an interesting, if not directly related, discussion on the topic of the cache.

http://blog.sqlauthority.com/2010/06/17/sql-server-data-pages-in-buffer-pool-data-stored-in-memory-cache/

Answer 4

This is a very common question. I wrote up a canned answer:

Without ORDER BY, there is no default sort order.