Rows not in order in 2005
-
hi all, I am working in sql 2005 migration. when i ran same query in sql 2000 and sql 2005,no of values return are same.But the Resulting data is in different order. In sql 2005 the data returns either in asc or desc order. In sql 2000 the data returns in random order. plz clarify me 1.the reason for the rows order difference.(is it because of execution plan?) 2.The resulting data of sql2005 should be in same order of sql 2000. how to overcome this problem. Thanks in advance,
cheers sangeet
-
hi all, I am working in sql 2005 migration. when i ran same query in sql 2000 and sql 2005,no of values return are same.But the Resulting data is in different order. In sql 2005 the data returns either in asc or desc order. In sql 2000 the data returns in random order. plz clarify me 1.the reason for the rows order difference.(is it because of execution plan?) 2.The resulting data of sql2005 should be in same order of sql 2000. how to overcome this problem. Thanks in advance,
cheers sangeet
ramyasangeet wrote:
1.the reason for the rows order difference.(is it because of execution plan?)
The order in which the rows are returned is determined by a lot of factors, clustered index, page placements, row order etc. For example, if there's a clustered index on the table, the optimizer will find it cheaper to return rows in the index order. Also, the query engine has undergone vast changes in 2005 compared to 2000.
ramyasangeet wrote:
2.The resulting data of sql2005 should be in same order of sql 2000.
Use an Order by clause.
SG Cause is effect concealed. Effect is cause revealed.
-
hi all, I am working in sql 2005 migration. when i ran same query in sql 2000 and sql 2005,no of values return are same.But the Resulting data is in different order. In sql 2005 the data returns either in asc or desc order. In sql 2000 the data returns in random order. plz clarify me 1.the reason for the rows order difference.(is it because of execution plan?) 2.The resulting data of sql2005 should be in same order of sql 2000. how to overcome this problem. Thanks in advance,
cheers sangeet
As the previous poster said, if you want a specific order then use the order by clause. Do not rely on the intrinsic order that SQL Server uses as it can, as you've seen, be non-deterministic.
Upcoming FREE developer events: * Developer Day Scotland My website