Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
huenemeca
Starting Member
3 Posts |
 Posted - 2011-08-25 : 17:04:42
|
| I am restoring a database from 2000 to 2008 R2. One of my clients is complaining that the sort order has changed. It is a select with an order on the score column. The first 2 records have the same score and it 2000 they come by one way in 2008 they come back the opposite.I have looked and they have the same collation settings, and the 2008 is in 2000 compatibility mode. Is there anything else I can check?Thanks,Mike |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-08-25 : 17:08:13
|
Without a proper ORDER BY, you can never be sure of the order for the set.If there is a column with two equal values, and you sort by that column, the first may come before the second or the opposite.There is no guarantee without ORDER BY.It's as simple as that.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
huenemeca
Starting Member
3 Posts |
Posted - 2011-08-25 : 17:09:26
|
| That is what I was trying to tell my Boss, but I needed confirmation that there isn't some setting that I was missing.Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-08-26 : 00:58:13
|
Parallellism? Outdated statistics?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
huenemeca
Starting Member
3 Posts |
Posted - 2011-08-26 : 10:37:09
|
| This is not in a view, sorry but I don't know how to check Parallellism. Updating statistics did not fix it.Correction, Updating statistics did not fix it if you only update stats on the 2008 db. I ran it on the 2000 db and now they are in sync. Thanks for the help. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-26 : 10:53:09
|
| EXEC sp_configure 'max degree of parallelism'GO this will give degree of parallelism setting of the server------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-26 : 11:11:30
|
| Note that the sort order may change from query-to-query.Whilst, mostly, you will probably get tie-break by the clustered index order, it is possible for part of the data you need to be in memory (from another query) and SQL may start processing that data whilst waiting for other data to be retrieved from disk. This can cause the "tie break" to be different to the clustered index. Its as rare as hen's teeth though ...So ... if your ORDER BY is ambiguous then most of the time, indeed often "very nearly 100% of the time", the ordering will be repeatable. But it is not guaranteed.To be guaranteed include additional tie-break columns in your ORDER BY - e.g. the PK Columns - such that each row is uniquely defined by the ORDER BY sort order. |
|
|
|
|
|
|
|
|
Sort order different from 2000