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 |
|
wahoofan22
Starting Member
1 Post |
 Posted - 2005-03-25 : 09:59:25
|
| We have a third party app here at work that issues this select statement on the DB 'select * from RateRule (nolock)' I can run this statement on 2 identical databases, and the results are returned in a different order (don't ask why the vendor didn't apply an 'Order By' statement). The order doesn't change for each execution, they just are returned differently between the 2 db's. I found that if I comment out the (nolock) on the select statement, both DB's return the results in the same order every time.Anyone know why the (nolock) is having this effect?Thanks in advance for the help. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-03-25 : 10:03:50
|
| You must be getting a different query plan. SQL Server choses the query plan it thinks has the lowest cost. If you want a result set in a particular order, you must specify it.If you have access to the code enough to change the hint, you should just add an ORDER BY clause.CODO ERGO SUM |
 |
|
|
eralper
Yak Posting Veteran
66 Posts |
Posted - 2005-03-26 : 17:25:10
|
| When NoLock is used, you may get records that are not committed. It causes dirty read. This may cause the difference.-------------Eralperhttp://www.kodyaz.com |
|
|
|
rkasse
Starting Member
14 Posts |
Posted - 2005-03-26 : 17:27:04
|
| As stated above, the only way to guarantee the order of the results is to use the Order By clause. The concept of rows being stored in a particular order just does not exist in a relational database. Without an Order By clause there are numerous internal workings of the database that can affect the order of the results. If the order matters, you must have it specified. |
|
|
|
|
|
|
Select with (nolock) results in strange order