1. NOLOCK reads uncommitted data i.e., dirty data. This is the key point that needs to be considered before using this keyword. This will determine the accuracy of the final result. Learn more about this issue, please visit http://msdn.microsoft.com/en-us/library/ms190805.aspx
2. NOLOCK will be ignored in the DELETE, UPDATE or INSERT statement but on the table to be updated/inserted. The NOLOCK syntax on UPDATE/DELETE /INSERT will be removed in the future release of SQL Server. See examples (from the MSDN forum) below:
Example 1:
UPDATE orders
SET no_of_orderlines = (
SELECT COUNT(*)
FROM orderlines WITH (NOLOCK)
WHERE orderlines.order_id = orders.order_id
)
The NOLOCK hint in the above example is okay and will be honored.
Example 2:
UPDATE orders
SET no_of_orderlines = COUNT(*)
FROM orders O WITH (NOLOCK)
JOIN orderlines OL ON OL.order_id = O.order_id
However, the NOLOCK in the above example will be ignored as it is on the table being updated.
3. NOLOCK equals to READUNCOMMITED and they are exchangeable. Have seen people suggest that the NOLOCK keyword should be removed in the future release but keep READUNCOMMITED keyword in use only.
4. Use isolation level as shown below to apply NOLOCK on a group of statements
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on
SELECT * FROM Person.Contact WHERE ContactID < 20
UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID = 1
SELECT * FROM Person.Contact WHERE ContactID < 20
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off
5. Use READPAST hint instead of NOLOCK hint will eliminate the dirty data. However, everything else will be the same as NOLOCK. See this article for more details.
