Those of us who work in the Production / Live DB , know the pain , of firing a update query on a huge table . This is very problematic as this will lead to the following
- Lock escalation : The entire table will be locked by your application , so other users / application will not be able to perform the DDL on this table
- The TempDB will grow huge
- A update lock on a table will stop other users , for making changes in the table
I have written a below snippet which will help us fight the above issues
-- Step 1 : Declare the varaibles
use DBNAME<BR>Declare @counter int <BR>Declare @RowsEffected int <BR>Declare @RowsCnt int <BR>Declare @CodeId int <BR>Declare @Err int<BR>SELECT @COUNTER = 1<BR>SELECT @RowsEffected = 0
Step 2 : Get the value of the Code , with which we need to update the existing Code . In my case I am capturing is from a table , we can always hard code it .
SELECT @CodeID = CodeID FROM CODE WHERE XXXX ='YYYY'
Step 3: Start the while loop , if we have 100,000 records , and in each loop 5,000 records will be update , so the total number of cycle will be 100,000/5000 i.e 20
WHILE ( @COUNTER > 0)<BR> BEGIN <BR> SET ROWCOUNT 5000
-- Note : The SET ROWCOUNT 5000 will just pick the top 5000 records */
/*UPDATING TABLE */
UPDATE Table<BR> SET CodeID= @CodeID <BR> WHERE Codeid = @OldCode <BR> SELECT @RowsCnt = @@ROWCOUNT ,@Err = @@error <BR> IF @Err <> 0 <BR> BEGIN <BR> Print 'Problem Updating the records' <BR> END <BR> IF @RowsCnt = 0<BR> SELECT @COUNTER = 0 <BR> ELSE <BR>
/* Increment the Counter */
SELECT @RowsEffected = @RowsEffected + @RowsCnt <BR> PRINT 'The total number of rows effected :'+convert(varchar,@RowsEffected) <BR>
/*delaying the Loop for 10 secs , so that Update is comepleted*/
WAITFOR DELAY '00:00:10'
--Step 4 : Check if all the records are updated or not .
IF EXISTS ( SELECT CodeID<BR> FROM Table (NOLOCK)<BR> WHERE CodeID = @OldCodeid<BR> )<BR>BEGIN <BR>PRINT ('All the records are not updated , there is some problem , Contact Devs ')<BR>END<BR>BEGIN <BR>PRINT ('All the records are updated SUCCESSFULLY !!!!')<BR>END <BR>/* ------Set rowcount to default ----*/
SET ROWCOUNT 0