5

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?

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Jim G.
  • 15,141
  • 22
  • 103
  • 166

2 Answers2

9

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?

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 1
    +1 - More complete than mine. Want to note, though, that any DDL transactions will still require a lock and will be blocked. – JNK Nov 01 '10 at 15:17
  • If I try to add a column to `TABLE_1` while his query is running, it wouldn't block it? – JNK Nov 01 '10 at 15:24
  • @JNK: oh, sorry, confused with `DML` :) Sure it will. – Quassnoi Nov 01 '10 at 15:27
  • NO! NoLocks and READUNCOMMITTED table hints as of SQL2005 ignored on update and delete statements. https://technet.microsoft.com/en-us/library/ms143729(v=sql.90).aspx – Brian Dec 04 '15 at 21:08
  • 1
    @Brian: yes. The hints are only ignored when applied to the target table of the DML statements. – Quassnoi Dec 04 '15 at 22:37
1

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...

Badiboy
  • 1,519
  • 1
  • 18
  • 31