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

Jim G. picture Jim G. · Nov 1, 2010 · Viewed 8.6k times · Source

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?

Answer

Quassnoi picture Quassnoi · Nov 1, 2010

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?