SQL Server Tips ----- EFFICIENTLY MANAGE LARGE DATA MODIFICATIONS

Didyou know that you can now use the TOP operator for Deleting, Inserting andUpdating data in SQL Server tables?

Using the TOP operator for DML operation can help you in executing verylarge data operations by breaking the process into smaller pieces. This canpotentially help with increased performance and also helps with improvingdatabase concurrency for larger and highly accessed tables. This is consideredas one of the best techniques for managing data modifications on large dataloads for reporting or data warehouse applications.

When you perform an update on large number of records using single set updates,it can cause the Transaction Log to grow considerably. However, when processingthe same operation in chunks or pieces, each chunk is committed aftercompletion allowing SQL Server to potentially re-use the T-Log space. Anotherclassic issue many of us have experienced is when you are performing very largedata updates and you cancel the query for some reason, you would have to waitfor a long time while the transaction completely rolls back.

 

Withthis technique you can perform data modifications in smaller chunks and you cancontinue with your updates more quickly. Also, chunking allows more concurrencyagainst the modified table, allowing user queries to jump in, instead ofwaiting for several minutes for a large modifications to finish.

Let’s take an example of deleting 1000 rows of records in a chunk. Assume atable called LARGETABLE table that has millions of records and you wantdelete 1000 records in chunk:

--CREATE A DEMO TABLE CALLED LARGETABLE
CREATE TABLE LARGETABLE (ID_COL INT IDENTITY(1,1), COL_A VARCHAR(10),COL_B VARCHAR(10))
GO

--INSERT THE DATA IN LARGETABLE.. NOTICE THE USE OF ‘GO 10000’
INSERT INTO LARGETABLE VALUES ('A','B')
GO 10000 

--QUERY THE TABLE 
SELECT COUNT(*) FROM LARGETABLE;

--PERFORM DELETION OF 1000 ROWS FROM LARGETABLE
WHILE (SELECT COUNT(*) FROM LARGETABLE) > 0
BEGIN
            DELETE TOP (1000) FROM LARGETABLE
            SELECT LTRIM(STR(COUNT(*)))+' RECORDS TO BE DELETED' FROM LARGETABLE --THIS IS JUST A COMMENT.
END

The above technique can also be used with INSERT and UPDATE commands.