NOLOCK vs. Transaction Isolation Level

Question

What's the difference between using "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" and NOLOCK? Is one better than the other?

Answer 1

They're the same thing, just scoped differently. NOLOCK is placed on a per table basis and SET Transaction... can be placed as a block.

Answer 2

NOLOCK is a query hint and as such only applies to the specifc table within the query in which it is specified.

Setting the transaction isolation level applies to all code executed hence forth within the current connection or until it is explicitly modified.

To clarify, functionally the isoloation level at work is the same however the scope which is covered may not be.

Answer 3

See that answer from a few hours ago, to the question SQL Server SELECT statements causing blocking.

Quoting Remus Rusanu:

SELECT can block updates. A properly designed data model and query will only cause minimal blocking and not be an issue. The 'usual' WITH NOLOCK hint is almost always the wrong answer. The proper answer is to tune your query so it does not scan huge tables.

If the query is untunable then you should first consider SNAPSHOT ISOLATION level, second you should consider using DATABASE SNAPSHOTS and last option should be DIRTY READS (and is better to change the isolation level rather than using the NOLOCK HINT). Note that dirty reads, as the name clearly states, will return inconsistent data (eg. your total sheet may be unbalanced).

The other answers may help you as well.

Answer 4

They have the same effect, only one is used as a lock hint (nolock) and the other is used for a connection scope.

Be careful with either of those - dirty reads can be a very bad thing depending on your app. Reading the same record twice or missing a record because of page movement can be a very confusing thing to users...