Why does SQL Server 2000 behave differently than SQL Server 2008 in the absence of an ORDER BY clause?

Question

We are using SQL Server 2008 R2. We have a table called PD (6 columns) which has a NO primary key and no index on any column. When we execute the query

select * from PD where ESE='bycad'

without an ORDER BY clause, we are getting results in different order each time executing the same query. We used to have SQL Server 2000 until two months ago and never faced this problem.

Is this a bug in SQL Server 2008? I know we should use an ORDER BY clause, but why Does SQL Server 2008 deal differently with SQL queries without an ORDER BY clause?

Answer 1

Any result-set coming from SQL Server is a set. A set does not have an inherent order.

There has never been any order guarantee for rows to come back from a select statement unless "ORDER BY" is specified. (This is true for any rdbms not only SQL Server)

EDIT: To answer your question, the reason you are seeing a different order with every execution is most likely be "caused" by a parallel scan of the table. Did you get new hardware too? But again, if you need ordered results, you need to specify "ORDER BY".