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
- It will not create one big transaction.
- It avoids a table lock.
- If the delete statement is canceled, only the last batch is rolled back. Records in previous batches are deleted.
CREATE TABLE DEMO (COL1 INT,COL2 INT)
DECLARE @COUNTER INT
SET @COUNTER = 1
INSERT INTO DEMO (COL1,COL2) Values (2,2)
WHILE @COUNTER < 50000
INSERT INTO DEMO (COL1,COL2) Values (1,@COUNTER)
SET @COUNTER = @COUNTER + 1
-- 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
DELETE TOP (@BatchSize)
WHERE COL1 = 1
SET @BatchSize = @@rowcount
-- SELECT * FROM Demo -- Now we have only 1 record left