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"

SIMPLE SENARIO

CREATE TABLE [DBO].[PROJECT](
[ENAME] [VARCHAR](MAX) NULL,
[PID] [INT] NULL,
[STATUS] [INT] NULL
) ON [PRIMARY]
GO

INSERT PROJECT VALUES ('bala', 1, NULL)
INSERT PROJECT VALUES ('manoj', 1, 1)
INSERT PROJECT VALUES ('krishna', 1, 1)
INSERT PROJECT VALUES ('a', 1, 1)
INSERT PROJECT VALUES ('b', 2, NULL)
INSERT PROJECT VALUES ('c', 2, 0)
INSERT PROJECT VALUES ('d', 3, NULL)
INSERT PROJECT VALUES ('e', 3, 1)
INSERT PROJECT VALUES ('f', 3, 1)

PROJECT DONE:

;WITH X AS
(
SELECT PID,COUNT(PID) PD,SUM(STATUS) STATU FROM PROJECT
GROUP BY PID,STATUS HAVING STATUS IS NOT NULL
)SELECT P.* FROM  PROJECT P JOIN X B ON B.PID=P.PID
 WHERE B.PD=B.STATU AND STATUS IS NULL



 ;WITH X AS( SELECT PID,COUNT(PID) AS COU_PID,SUM(status) AS STAU FROM project
 GROUP BY pid,status  HAVING status IS  NOT NULL)

 SELECT PID FROM X  WHERE COU_PID=STAU

PROJECT NOT DONE 


;WITH X AS
(
SELECT PID,COUNT(PID) PD,SUM(STATUS) STATU FROM PROJECT
GROUP BY PID,STATUS HAVING STATUS IS NOT NULL
)SELECT P.* FROM  PROJECT P JOIN X B ON B.PID=P.PID
 WHERE B.PD<>B.STATU AND STATUS IS NULL

HOW TO CAPTURE PAGE BY PAGE DATA IN A TABLE BY USING OUTPUT PARAMETER

CREATE PROC USP_PAGE_ORDER(@PAGE_NO INT,@SIZE INT,@ORDER VARCHAR(20),@COUNT INT OUT)
AS
BEGIN
                SET NOCOUNT ON
                DECLARE @START INT=(@PAGE_NO-1)*@SIZE+1
                DECLARE @END INT= @PAGE_NO*@SIZE;
                DECLARE @S VARCHAR(MAX)
                CREATE TABLE #TT(ID INT IDENTITY,NAME VARCHAR(MAX))
                SET @S='SELECT NAME FROM SYS.OBJECTS ORDER BY NAME '+@ORDER
                INSERT INTO #TT EXEC(@S)
                SET @COUNT=@@ROWCOUNT

               SELECT * FROM #TT WHERE ID BETWEEN @START AND @END

END

CHECK:

DECLARE @TT INT
EXEC USP_PAGE_ORDER 2,10,'ASC',@COUNT=@TT OUT
PRINT @TT

DECLARE @TT INT
EXEC USP_PAGE_ORDER 3,10,'ASC',@COUNT=@TT OUT
PRINT @TT

HOW TO FIND COUNT OF NEGATIVE & POSITIVE VALUE

CREATE TABLE SIGNS(ID INT)
GO
INSERT INTO SIGNS VALUES(-1),(-2),(-3),(2),(3)

SELECT * FROM  SIGNS

METHOD 1:

SELECT SIGN(ID) AS NA_PO,COUNT(SIGN(ID)) AS COUNT_NE_PO FROM signs GROUP BY SIGN(ID)

METHOD 2:

SELECT  NEGTIVE=COUNT(CASE WHEN ID<0 THEN 1 ELSE NULL END),

 POSTIVE=COUNT(CASE WHEN ID>0 THEN 1 ELSE NULL END)

FROM SIGNS

HOW TO DELETE TWO NULL VALUES IN A TABLE

CREATE TABLE GGGG(ID VARCHAR(50))

INSERT INTO GGGG VALUES('A'),('B'),('C'),('D'),(NULL),(NULL),(NULL),(NULL)

SELECT * FROM  GGGG

;WITH X AS(SELECT ID,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS ROW_NO FROM GGGG)

---SELECT * FROM X

DELETE FROM X WHERE ID IS NULL AND ROW_NO IN(1,2)