NOLOCK – truth

With the NOLOCK hint (or setting the isolation level of the session to READ UNCOMMITTED) you tell SQL Server that you don’t expect consistency, so there are no guarantees. Bear in mind though that “inconsistent data” does not only mean that you might see uncommitted changes that were later rolled back, or data changes in an intermediate state of the transaction. It also means that in a simple query that scans all table/index data SQL Server may lose the scan position, or you might end up getting the same row twice.

ref:

Clustered index physically organizes the data in order
(myth/false assumption)
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-i.aspx

Which access methods are available to SQL Server to fully
scan the table’s data when an ORDER BY clause is not
specified

http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-ii.aspx

NOLock truth

http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii.aspx

About these ads
This entry was posted in Lock/Blocking, What I learned today and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s