In the UPDATE statement, are NOLOCK hint honored in the FROM clause?

Question

Given the following update statement:

UPDATE @TableVariable
SET city = T2.city
FROM @TableVariable TV
INNER JOIN dbo.TABLE_1 T1 WITH (NOLOCK)
    ON  (TV.customer_id = T1.customer_id)
INNER JOIN dbo.TABLE_2 T2 WITH (NOLOCK)
    ON  (T1.address_id = T2.address_id)

Will the (NOLOCK) hints on TABLE_1 and TABLE_2 be honored?

Telic

Answer 1

Will the (NOLOCK) hints on TABLE_1 and TABLE_2 be honored?

Yes, they will.

Note that in general, this behavior is very wrong and if you think you really need this, most probably, you should redesign your database and/or app.

What is the purpose of the NOLOCK hints? Why do you want to update your table variable with dirty data?

Fain

+1 - More complete than mine. Want to note, though, that any DDL transactions will still require a lock and will be blocked. – Jonejonell

If I try to add a column to TABLE_1 while his query is running, it wouldn't block it? – Jonejonell

@JNK: oh, sorry, confused with DML :) Sure it will. – Fain

NO! NoLocks and READUNCOMMITTED table hints as of SQL2005 ignored on update and delete statements. technet.microsoft.com/en-us/library/ms143729(v=sql.90).aspx – Clearsighted

@Brian: yes. The hints are only ignored when applied to the target table of the DML statements. – Fain

Answer 2

NoLock hint nearly in any way increase server productivity. However (NOLOCK) hint may be used safely when accessing data is either guaranteed enought to be not changing at that moment (for example, once data portion is posted to DB it will stay unchanged or you know that current portion of data will not be changed by other users) or if it doesn't matter to take old data or new data (for example, some data often changes and in any way there is no need to save exactly the latest one). In other way you may get divergence of data in different tables...

Houseboy