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