Benefits and disadvantages of WITH (NOLOCK)

Question

Benefits and disadvantages of WITH (NOLOCK). Also is there other commands that can serve different use cases.

Answer 1

The benefit is that no locks are taken and the query will not block another query. The main downside is the results may contain uncommited values (updated, added or deleted) that end up not being committed or see partial updates.

Answer 2

It's more than just see uncommitted values that can be inconsistent. You may also fail to read committed data, because it was being moved or in a page split when you scanned the table. Or you may read the same data twice due to the same reason.

When you use NOLOCK and SQL Server scans a table, it may use an IAM scan, which is more efficient, but which is not safe for things like I mentioned above.

NOLOCK can be very useful for ad-hoc queries in a production system to avoid blocking, and particularly to investigate the progress of long-running transactions.

But it very rarely has a place in application code, since the risk for transient incorrect results is too high. If blocking is a concern, using some form of snapshot is better.