Why on earth are you updating 70% of a million row table that is being accessed by users on a regular basis? That's borderline insanity on the part of whoever designed the system...
So far as having to rewrite all of the procs to use the WITH (NOLOCK) hint.... not quite true... if you rename the table and create a surrogate view (named the same as the table originally was) with the appropriate transaction isolation level and using only SELECT * from one table, you will come real close to creating a "synonym" like they do in Oracle. There may be one or two places where you will get a locking clash so I'd recommend a test in a parallel environment first but it may work just fine.
If you have triggers, I wouldn't do it...
Note that the surrogate view is NOT a fix... it's a patch, a work around, and should be very temporary until you can figure out a better way to do things than updating such a large part of a large table with users in constant access...
Serqiy's method is one of the better ones but I still question the need for the massive update to begin with... somebody missed something big in the design...
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)