Effect of NOLOCK hint in SELECT statements

Question

I guess the real question is:

If I don't care about dirty reads, will adding the with (NOLOCK) hint to a SELECT statement affect the performance of:

1. the current SELECT statement
2. other transactions against the given table

Example:

Select * 
from aTable with (NOLOCK)

Answer 1

It will be faster because it doesnt have to wait for locks

Answer 2

1) Yes

2) Yes

Nolock typically (depending on your DB engine) means give me your data, and I don't care what state it is in, and don't bother holding it still while you read from it. It is all at once faster, less resource intensive, and very very dangerous.

If I can give you just one line of advise - never do an update or perform anything system critical based on data that originated from a nolock read, or you're just asking for hurt at some point or another.

Answer 3

NOLOCK makes most SELECT statements faster, because of the lack of shared locks. Also, the lack of issuance of the locks means that writers will not be impeded by your SELECT.

NOLOCK is functionally equivalent to an isolation level of READ UNCOMMITTED. The main difference is that you can use NOLOCK on some tables but not others, if you choose. If you plan to use NOLOCK on all tables in a complex query, then using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is easier, because you don't have to apply the hint to every table.

Here is information about all of the isolation levels at your disposal, as well as table hints.

SET TRANSACTION ISOLATION LEVEL

Table Hint (Transact-SQL)