0 votes
1 view
in SQL by (20.3k points)

Background: I have a performance-critical query I'd like to run and I don't care about dirty reads.

My question is; If I'm using joins, do I have to specify the NOLOCK hint on those as well?

For instance; is:

SELECT * FROM table1 a WITH (NOLOCK)

INNER JOIN table2 b WITH (NOLOCK) ON a.ID = b.ID

Equivalent to:

SELECT * FROM table1 a WITH (NOLOCK)

INNER JOIN table2 b ON a.ID = b.ID

Or will I need to specify the (NOLOCK) hint on the join to ensure I'm not locking the joined table?

1 Answer

0 votes
by (40.4k points)

I will not address the READ UNCOMMITTED argument here, I'll just talk about your original question. Yes, you must use WITH(NOLOCK) on each table of the join. Your queries are not the same though. 

Try this: Begin a transaction and insert a row into table1 and table2. But, don't commit or rollback the transaction. After that, your first query will return successfully and will include the uncommitted rows. Whereas, your second query will not be returned because table2 doesn't have the WITH(NOLOCK) hint on it.

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...