HOW TO DELETE AND UPDATE BATCH BY BATCH FOR A GOOD PERFORMANCE

Consider the following table
  
CREATE TABLE TAB (
        CLM1 INT IDENTITY(1,1) PRIMARY KEY
        ,CLM2 CHAR(5)
        ,CLM3 TEXT
        ,CLM4 DATETIME)

The above table has more than 1 million rows. 
I want to update all the rows in this table which have CLM2 = 'ABCDE' to 'LKJHG'. 
Note, this operation may result in at least 60% of the table rows being affected. 
Please write a script which does this task in an optimum way.


ANSWER: for performance i have update bach by batch.
    
set rowcount 1000
declare @cnt int=1
while(@cnt>0)
begin
        UPDATE TAB
        SET CLM2 = 'LKJHG'
        WHERE CLM2 = 'ABCDE'
      
        set @cnt=@@rowcount;
end

USING BATCHES TO DELETE LARGE RECORDS IN A TABLE :

--We have 1 million records in a table then a simple delete is

DELETE FROM MYTABLE WHERE COL1=1

it can take a while when you have for instance 1 million records to delete. It can results in a table lock which has a negative impact on the performance of your application.

As of SQL2005/2008 you can delete records in a table in batches with the DELETE TOP (BatchSize) statement.  This method has 3 advantages

1. It will not create one big transaction.
2. It avoids a table lock.
3. If the delete statement is canceled, only the last batch is rolled back. Records in previous batches are deleted. 

For example :


 CREATE TABLE DEMO (COL1 INT,COL2 INT)


DECLARE @COUNTER INT
SET @COUNTER = 1

INSERT INTO DEMO (COL1,COL2) Values (2,2)

WHILE @COUNTER < 50000
BEGIN

       INSERT INTO DEMO (COL1,COL2) Values (1,@COUNTER)
       SET @COUNTER = @COUNTER + 1

END

/*
-- Show content of the table
SELECT COL1, COUNT(*) FROM DEMO GROUP BY COL1
*/

-- Deleting records in batches of 1000 records

DECLARE @BatchSize INT

SET @BatchSize = 1000

WHILE @BatchSize <> 0

BEGIN
              DELETE TOP (@BatchSize)
              FROM DEMO
               WHERE COL1 = 1

SET @BatchSize = @@rowcount

END

-- SELECT * FROM Demo -- Now we have only 1 record left


----other example

SET ROWCOUNT 5000; -- set batch size
WHILE EXISTS (SELECT 1 FROM myTable WHERE date < '2013-01-03')
BEGIN
    DELETE FROM myTable
    WHERE date < '2013-01-03'
END;
SET ROWCOUNT 0; -- set batch size back to "no limit"