SQL Server NOLOCK and joins

Question

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?

Answer 1

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.

Answer 2

I will not address the READ UNCOMMITTED argument; my attention will be solely on your original question. Yes, it is essential to use WITH(NOLOCK) for all tables being used in the join. While your questions are alike, they are not the same.

Try this,Begin a transaction and insert a record into both table1 and table2. Avoid both executing and canceling the transaction. Afterwards, your first question will also be able to fetch the unreconciled records. Table2 does not have the WITH(NOLOCK) hint, so your second query will not be returned.