December 10, 2007 at 11:56 am
I have a database on SQL Server 2000 which i restored on SQL Server 2005. When i run a simple query the results are sorted differently. The collations of both the servers are the same.
SELECT StatusText, StatusCode FROM Status WHERE StatusField = 'SomeStatus'
Output From 2000
------------------
StatusText StatusCode
Discount D
No Discount N
Output From 2005
------------------
StatusText StatusCode
No Discount N
Discount D
Any suggestions what else I should be looking for?
-SA
December 10, 2007 at 12:00 pm
If you want a specific order - you should specify an ORDER BY clause. Because of the way SQL server accesses data, you can't count on it using a specific order unless you explicitly specify it in the outermost query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 10, 2007 at 12:18 pm
I agree with that...but would anyone be able to explain why this would happen...
December 10, 2007 at 12:53 pm
It has to do with the way SQL 2000 and SQL 2005 handled parallel processing.
In SQL 2000, the database engine did not use multiple threads very efficiently. Because of this, you could anticipate the order in which results would be returned even without specifying an order. For instance, if you select all records from a table without ordering them, they will be returned in the order they appear on disk (the order of your clustered index if you have one).
In SQL 2005, the query processing engine multi-threads and each thread can get records. If thread 1 returns five records and then thread 2 returns tree and then thread 1 returns four more, if thread 1 is getting records from the first 10 pages of the table and thread 2 is getting records from the second 10 pages your records will be out of order. Since the processing of a thread can depend on constantly changing variables, you can no longer anticipate the order in which the threads will return anything.
I believe (although I have never tried it), if you set the MAX DOP to 1, you will again be able to depend on the order of the records but you will effectively turn off your multi-threading.
I would suggest ordering your records. If you have queries in code that you cannot change, look at the books online section about "Plan Guides" that will in 2005 allow you to manipulate options of individual queries after they are sent to the query optimizer.
December 10, 2007 at 1:18 pm
I would agree with Michael above. You should not change MAXDOP or depend on any ordering of the records for any reason. It isn't the way SQL is designed to work most efficiently.
If you require an ordering, add an ORDER BY clause and possibly a column to order by.
December 10, 2007 at 1:29 pm
I did change the MAX DOP to test this and it gave the same results as SQL 2000. I reverted that back to the default.
Thanks for the quick response. This was indeed driving me nuts.
December 10, 2007 at 2:05 pm
Again, if you are really stuck and have a query being executed from some code that you cannot get to, you can use a plan guide to add the MAXDOP option just to a single query. It is a last-resort, but it is a pretty nice option when there is no other alternative.
I would be curious as to what the result would be in SQL 2000 compatibility mode. Does anyone know if SQL 2005 uses the new threading model in SQL 2000 compatibility mode?
December 10, 2007 at 2:09 pm
I tried it with SQL 2000 compatibility and it gave me the same results it did with the SQL 2005 compatibility so it is using threading
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply