Deleting batch of rows from MS-SQL Server table

Deleting large number of rows from a (SQL Server) table can cause replication delay, create a very large transaction and a big performance impact, as well as escalating locks so that the table will be unavailable.
If it is possible use truncate:

TRUNCATE TABLE MyTable;

Truncate will run much faster although it has no facility to filter rows, it does a table meta data change at the back (e.g. TRUNCATE will reset the IDENTITY value for the table if there is one).

The option of deleting small portions at a time will be slower, although it will generate less impact on the server performance.

    DECLARE @COUNT INT
 
    SET @COUNT = 0
 
    WHILE (@COUNT < 1000)
 
    BEGIN
 
       DELETE TOP (10000) FROM [MyTable] -- WHERE [];
       --SELECT CURRENT_TIMESTAMP;
       WAITFOR DELAY '00:00:02';
 
       SET @COUNT = (@COUNT + 1)
 
    END
Leave a Reply

*