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

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

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

[sql] SQL Server NOLOCK and joins

Examples related to sql

Examples related to sql-server

Examples related to nolock