April 16, 2009 at 2:34 am
Hi , I am getting different result set order (sort) with and without nolock. Can anyone suggest the reason. In my query there is no sort order. ..
Thanks in advance.....:-)
April 16, 2009 at 3:53 am
I don’t know why it changed the order of the records, but if it is important for you to get the data in a certain order, you can’t trust the server to order it for you automatically. In that case you have to specify which order should be used regardless of the fact that you are using or not using the nolock hint.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 16, 2009 at 3:54 am
It's because SQL's allowed to use a different method of scanning the table (allocation order scan) when the isolation level is read uncommitted. The different method is slightly faster, can be slightly inaccurate (miss rows or read rows twice) and the order may come out differently.
If you need a specific order, use an ORDER BY
Be aware that NOLOCK doesn't just mean take no locks, it essentially means to SQL 'Get my data regardless of what else is happening, I don't mind if it's slightly inaccurate'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply