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)

SUBQUERIES IN SQL SERVER

• A QUERY IN THE WHERE CLASS IS A KNOWN AS SUBQUERY OR NESTED QUERY OR INNER QUERY
• SUB QUERY WOULD BE EXECUTED FIRST ,THEN THE RESULT PASSED TO OUTER QUERY ,FINALLY OUTER QUERY WILL BE EXECUTED.
• MAX 32 LEVELS OF NESTING IS ALLOWED IN SUBQUERY

SYNTAX:

                    SELECT COLUMN_NAME [, COLUMN_NAME 
                    FROM   TABLE1 [, TABLE2 ]

                    WHERE  COLUMN_NAME  [COPARISION_OPERATOR]

                    (SELECT COLUMN_NAME [, COLUMN_NAME ]

                     FROM TABLE1 [, TABLE2 ]

                     [WHERE])

---ABOVE BLUE COLOR CODE IS SUB QUERY OR "INNER QUERY"

----PURPLE  COLOR CODE IS 'OUTER QUERY'

• SUBQUERIES CAN BE USED WITH THE SELECT, INSERT, UPDATE, AND DELETE STATEMENTS ALONG WITH THE OPERATORS LIKE =, <, >, >=, <=,  BETWEEN ETC.

COMPARISION OPERATOR:

"=,<,>,<=,>="      WHEN SUBQUERY RETURNS A SINGLE VALUE.(SINGLE COLUMN)
"="  ------ EQUALS
"<"---------LESS THAN
">"--------GREATER THAN
"<="------LESS THAN OR EQUAL TO
">="------GREATER THAN OR EQUAL TO
"< >"-----NOT EQUAL TO
"!="-----NOT EQUAL TO
"!<"----NOT LESS THAN
"!>"---NOT LESS THAN

THE RESULT OF A COMPARISON OPERATOR HAS THE BOOLEAN DATA TYPE. THIS HAS THREE VALUES: TRUE, FALSE, AND UNKNOWN. EXPRESSIONS THAT RETURN A BOOLEAN DATA TYPE ARE KNOWN AS BOOLEAN EXPRESSIONS.

EXAMPLES:

SELECT * FROM EMP WHERE SAL= (SELECT MAX(SAL) FROM EMP)

SELECT * FROM EMP WHERE SAL= (SELECT MIN(SAL) FROM EMP)

SELECT * FROM EMP WHERE SAL>(SELECT MIN(SAL) FROM EMP)

SELECT * FROM EMP WHERE SAL<=(SELECT AVG(SAL) FROM EMP )

SELECT * FROM EMP WHERE SAL>=(SELECT AVG(SAL) FROM EMP)

TOP2 SAL:

SELECT * FROM EMP WHERE SAL=(

SELECT  MAX(SAL) FROM EMP  WHERE SAL<(SELECT MAX(SAL) FROM EMP))

TOP3 SAL

SELECT * FROM EMP WHERE SAL=(

SELECT MAX(SAL) FROM EMP  WHERE SAL<(SELECT MAX(SAL) FROM EMP

WHERE SAL<(SELECT MAX(SAL) FROM EMP)))

IN & NOT IN:  WHEN SUBQUERY RETURNS MULTIPLE VALUES.(SINGLE COLUMN)

TOP 2 SAL:

SELECT  * FROM EMP WHERE  SAL=(   SELECT MIN(SAL) FROM EMP
                                       WHERE SAL IN(SELECT DISTINCT TOP 2 SAL FROM EMP))

TOP 3 SAL:

SELECT  * FROM EMP WHERE  SAL=(   SELECT MIN(SAL) FROM EMP
                                       WHERE SAL IN(SELECT DISTINCT TOP 3 SAL FROM EMP))

• IF THE VALUE OF TEST_EXPRESSION IS EQUAL TO ANY VALUE RETURNED BY SUBQUERY OR IS EQUAL TO ANY EXPRESSION FROM THE COMMA-SEPARATED LIST, THE RESULT VALUE IS TRUE;OTHERWISE, THE RESULT VALUE IS FALSE.

• USING NOT IN NEGATES THE SUBQUERY VALUE OR EXPRESSION.

RISTRICTIONS IN SUB QUERIES:

• SUBQUERIES MUST BE ENCLOSED WITHIN PARENTHESES.
• A SUBQUERY MUST INCLUDE A SELECT CLAUSE AND A FROM CLAUSE
• A SUBQUERY CAN INCLUDE OPTIONAL WHERE, GROUP BY, AND HAVING CLAUSES.
• YOU CAN INCLUDE AN ORDER BY CLAUSE ONLY WHEN A TOP CLAUSE IS INCLUDED.
• THE BETWEEN OPERATOR CANNOT BE USED WITH A SUBQUERY; HOWEVER, THE BETWEEN OPERATOR CAN BE USED WITHIN THE SUBQUERY.

MERGE STATEMENT IN SQL SERVER

• SQL MERGE STATEMENT WAS INTRODUCED IN SQL SERVER 2008.
• MERGE PERFORMS INSERT, UPDATE, OR DELETE OPERATIONS ON A TARGET TABLE BASED ONTHE RESULTS OF A JOIN WITH A SOURCE TABLE.

       

  SYNTAX:

    

                  MERGE  <HINT>  [INTO]   <TARGET-TABLE_NAME>

                  USING <SOURCE_TABLE_VIEW_OR_QUERY>

                   ON (<CONDITION>)

                  WHEN MATCHED THEN <UPDATE_CLAUSE>

                  WHEN NOT MATCHED THEN <INSERT_CLAUSE>;

---CONDITION MATCHES UPDATE WILL PERFOM THAT PARTICULAR RECORD.

---CONDITION NOT MATCHED INSERT RECORD IN A TABLE

---DELETE IS A OPTINAL.WHEN WE DELETE IN MATCHED CONDITION.

IMPORTANT NOTES:

•    SEMICOLON IS MANDATORY AFTER THE MERGE STATEMENT.

• WHEN THERE IS A MATCH CLAUSE USED ALONG WITH SOME CONDITION, IT HAS TO BE SPECIFIED FIRST AMONGST ALL OTHER WHEN MATCH CLAUSE. 

USES:

   

• USEFUL IN BOTH OLTP AND DATA WAREHOUSE ENVIRONMENTS 

          OLTP: MERGING RECENT INFORMATION FROM EXTERNAL SOURCE

            DW: INCREMENTAL UPDATES OF FACT, SLOWLY CHANGING DIMENSIONS.

EXAMPLES:

SOURCE TABLE:

CREATE TABLE STUDENT_DETAILS

(

       STU_ID INTEGER PRIMARY KEY,

       STU_NAME VARCHAR(15)

)

TARGET TABLE:

CREATE TABLE STUDENT_TOTAL_MARKS

(

      STU_ID INTEGER REFERENCES STUDENTDETAILS,

        STU_MARKS INTEGER

)

INSERT SOURCE TABLE DATA            INSERT TARGET TABLE DATA

STU_ID     STU_NAME                                             STU_ID    STU _MARKS   

    1                    SMITH                                                  1                   230

    2                    ALLEN                                                  2                   255

    3                     JONES                                                   3                  200

    4                   MARTIN

    5                    JAMES

In our example we will consider three main conditions while we merge this twotables.

     1.DELETE THE RECORDS WHOSE MARKS ARE MORE THAN 250.

     2.UPDATE MARKS AND ADD 25 TO EACH AS INTERNALS IF RECORDS EXIST.

      3.INSERT THE RECORDS IF RECORD DOES NOT EXISTS

MERGE  STUDENT_TOTAL_MARKS AS A

USING  (SELECT STU_ID,STU_NAME FROM STUDENT_DETAILS) AS B

ON A.STU_ID=B.STU_ID

WHEN MATCHED AND STU_MARKS>250 THEN DELETE

WHEN MATCHED THEN UPDATE  SET STU_MARKS=STU_MARKS+25

WHEN NOT MATCHED INSERT INTO (STU_ID,STU_MARKS)

VALUES(B.STU_ID,25);

OUTPUT:

IMPLEMENTING OUTPUT CLASS IN MERGE:


CREATE TABLE  BOOKINVENTORY  -- TARGET
(
  TITLEID INT NOT NULL PRIMARY KEY,
  TITLE NVARCHAR(100) NOT NULL,
  QUANTITY INT NOT NULL
    CONSTRAINT QUANTITY_DEFAULT_1 DEFAULT 0
);

CREATE TABLE   BOOKORDER  -- SOURCE
(
  TITLEID INT NOT NULL PRIMARY KEY,
  TITLE NVARCHAR(100) NOT NULL,
  QUANTITY INT NOT NULL
    CONSTRAINT QUANTITY_DEFAULT_2 DEFAULT 0
);
INSERT BOOKINVENTORY VALUES  (1, 'THE CATCHER IN THE RYE', 6),
                                                                       (2, 'PRIDE AND PREJUDICE', 3),
                                                                       (3, 'THE GREAT GATSBY', 0),
                                                                       (5, 'JANE EYRE', 0),
                                                                       (6, 'CATCH 22', 0),
                                                                       (8, 'SLAUGHTERHOUSE FIVE', 4);

INSERT BOOKORDER VALUES  (1, 'THE CATCHER IN THE RYE', 3),
                                                             (3, 'THE GREAT GATSBY', 0),
                                                             (4, 'GONE WITH THE WIND', 4),
                                                             (5, 'JANE EYRE', 5),
                                                             (7, 'AGE OF INNOCENCE', 8)


CAPTURE INSERT,UPDATE,DELETE:

DECLARE @MERGEOUTPUT TABLE
(
  ACTIONTYPE NVARCHAR(10),
  DELTITLEID INT,
  INSTITLEID INT,
  DELTITLE NVARCHAR(50),
  INSTITLE NVARCHAR(50),
  DELQUANTITY INT,
  INSQUANTITY INT
);

MERGE BOOKINVENTORY  AS  A
USING BOOKORDER AS B
ON BI.TITLEID = BO.TITLEID
WHEN MATCHED AND
  BI.QUANTITY + BO.QUANTITY = 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE
  SET BI.QUANTITY = BI.QUANTITY + BO.QUANTITY
WHEN NOT MATCHED BY TARGET THEN
  INSERT (TITLEID, TITLE, QUANTITY)
  VALUES (BO.TITLEID, BO.TITLE,BO.QUANTITY)
WHEN NOT MATCHED BY SOURCE
  AND BI.QUANTITY = 0 THEN
  DELETE
OUTPUT
    $ACTION,
    DELETED.TITLEID,
    INSERTED.TITLEID,
    DELETED.TITLE,
    INSERTED.TITLE,
    DELETED.QUANTITY,
    INSERTED.QUANTITY
  INTO @MERGEOUTPUT;

SELECT * FROM BOOKINVENTORY;

SELECT * FROM @MERGEOUTPUT