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

User · Answer

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.

User · Answer

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

User · Answer

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

[sql] SQL Server NOLOCK and joins

Examples related to sql

Examples related to sql-server

Examples related to nolock