Sorting order changes on SQL 2000 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Sorting order changes on SQL 2000

Hello, I recently moved a database from SQL7.0 to SQL 2000 by copying the data file. My SQL query on the 2 servers gives results in different order. The application uses this query to display a report. The query uses 2 tables both of which have composite primary keys and hence cluster-indexed. But some of the records returned by the query are not in the same order as the old server, as a result it affects the Report in the application. The query looks like this. Select tab1.*, tab2.x
from tab1 inner join (tab2…….)
order by tab1.a, tab2.a given that the field "a" is a part of the composite primary keys in both the tables. Any idea how I can resolve this compatibility issue between SQL 7 and SQL 2000? Thanks,
is the order by fully unique, if not, then a set of multiple rows could meet each particular order by clause, and SQL Server can choose any order it pleases,
more likely is a difference in execution plan, leading each to a difference in order if the order is fully unique, then it probably means the data is different
Also check the collation settings on user database & TEMPDB on SQL 2000 server. Satya SKJ
Contributing Editor & Forums Moderator
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.