CODEWITHSUNDEEP
sqlsql-servert-sql
Jonathan Porter
Jonathan Porter

Reputation: 1556

How to apply WITH (NOLOCK) to an entire query

I know that you would normally apply WITH (NOLOCK) at the table level but let's say hypothetically you'd like to join 15 tables together. Is there an easier way to apply WITH (NOLOCK) to all of the tables without having to write it after each table name?

Upvotes: 13

Views: 11330

Answers (1)

Siyual
Siyual

Reputation: 16917

You can set the transaction isolation level to read uncommitted:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT ...

This effectively treats the entire transaction as a WITH (NOLOCK)

From MSDN:

READ UNCOMMITTED

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

Upvotes: 19

Related Questions