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
Declare @counter int
Declare @RowsEffected int
Declare @RowsCnt int
Declare @CodeId int
Declare @Err int
SELECT @COUNTER = 1
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)
BEGIN
SET ROWCOUNT 5000
-- Note : The SET ROWCOUNT 5000 will just pick the top 5000 records */
/*UPDATING TABLE */
UPDATE Table
SET CodeID= @CodeID
WHERE Codeid = @OldCode
SELECT @RowsCnt = @@ROWCOUNT ,@Err = @@error
IF @Err <> 0
BEGIN
Print 'Problem Updating the records'
END
IF @RowsCnt = 0
SELECT @COUNTER = 0
ELSE
/* Increment the Counter */
SELECT @RowsEffected = @RowsEffected + @RowsCnt
PRINT 'The total number of rows effected :'+convert(varchar,@RowsEffected)
/*delaying the Loop for 10 secs , so that Update is comepleted*/
WAITFOR DELAY '00:00:10'
END
--Step 4 : Check if all the records are updated or not .
IF EXISTS ( SELECT CodeID
FROM Table (NOLOCK)
WHERE CodeID = @OldCodeid
)
BEGIN
PRINT ('All the records are not updated , there is some problem , Contact Devs ')
END
BEGIN
PRINT ('All the records are updated SUCCESSFULLY !!!!')
END
/* ------Set rowcount to default ----*/
SET ROWCOUNT 0
