SQL Server NOLOCK with JOIN, Bulk load

Question

Following is the scenario I have: I have a stored procedure that returns data by joining 4 tables. Twice in the middle of the day there is a bulk upload to one of the above 4 tables. The load continues for 10-15 minutes. I do not want the UI that invokes this stored procedure to freeze/block/slow down during this 10-15 minute window. I do not care about showing dirty/uncommitted data from the above tables. Following are my queries:

1. Do I need to use NOLOCK on just the table which is being loaded during the day OR NOLOCK needs to be added to all 4 tables of the join. For e.g.

   SELECT * 
   FROM Table1 T1 WITH (NOLOCK) --this is the table that will be bulk-loaded twice during the day
   INNER JOIN Table2 T2 WITH (NOLOCK)
   INNER JOIN Table3 T3 WITH (NOLOCK)
   INNER JOIN Table4 T4 WITH (NOLOCK)
   

   OR is this sufficient

   SELECT * 
   FROM Table1 T1 WITH (NOLOCK) --this is the table that will be bulk-loaded twice during the day
   INNER JOIN Table2 T2
   INNER JOIN Table3 T3
   INNER JOIN Table4 T4
   

2. If I add a SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the beginning of the retrieval procedure and reset it back to READ COMMITTED at the end, will there be any difference?

Thanks

Vikas

Answer 1

1. You only need to add NOLOCK for the tables that will be locked for prolonged periods of time, so adding NOLOCK to only Table1 is sufficient.
2. If you set the isolation level to READ UNCOMMITTED, you do not need to add NOLOCK at all, since it will be automatically applied to all queried tables. In other words you will create a situation similar to the first example in your question item 1 where NOLOCK is applied to all tables participating in the SELECT.

By the way, make sure you add ON conditions to your INNER JOIN clauses, because as presented they are not valid Transact-SQL.