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 was pretty sure that you need to specify the NOLOCK for each JOIN in the query. But my experience was limited to SQL Server 2005.

When I looked up MSDN just to confirm, I couldn't find anything definite. The below statements do seem to make me think, that for 2008, your two statements above are equivalent though for 2005 it is not the case:

[SQL Server 2008 R2]

All lock hints are propagated to all the tables and views that are accessed by the query plan, including tables and views referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.

[SQL Server 2005]

In SQL Server 2005, all lock hints are propagated to all the tables and views that are referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.

Additionally, point to note - and this applies to both 2005 and 2008:

The table hints are ignored if the table is not accessed by the query plan. This may be caused by the optimizer choosing not to access the table at all, or because an indexed view is accessed instead. In the latter case, accessing an indexed view can be prevented by using the OPTION (EXPAND VIEWS) query hint.

Answer 2

Neither. You set the isolation level to READ UNCOMMITTED which is always better than giving individual lock hints. Or, better still, if you care about details like consistency, use snapshot isolation.

Answer 3

I won't address the READ UNCOMMITTED argument, just your original question.

Yes, you need WITH(NOLOCK) on each table of the join. No, your queries are not the same.

Try this exercise. Begin a transaction and insert a row into table1 and table2. Don't commit or rollback the transaction yet. At this point your first query will return successfully and include the uncommitted rows; your second query won't return because table2 doesn't have the WITH(NOLOCK) hint on it.