How to DELETE all rows incrementally without too much pressure on the server?

TRUNCATE keyword is the best way to remove all rows from the table because it uses fewer system and transaction log resources. However we can’t always use it when our subject table has a foreign key constraint, the table is part of indexed view or published by using transactional replication or merge replication. If we try to delete all rows in sql server at once with just a simple DELETE command it will put too much pressure on the server because there will be many locks used. It gets even worse when a table has too many rows.

Let us see how we could delete all rows without putting too much pressure on the server in action:

Step 1: Let us first create a big table named ‘TABLE TO DELETE’ from our existing one. Notice in the script below I have GO 50 which is to execute the batch 50 times.

IF OBJECT_ID ('TABLE TO DELETE','U') IS NULL
   SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Company]
      ,CAST( GETDATE() AS TIMESTAMP) TMStamp INTO [TABLE TO DELETE]
   FROM tContact
   ELSE
      INSERT INTO [TABLE TO DELETE]
      ([FirstName]
      ,[LastName]
      ,[Company]
      )
      SELECT [FirstName]
            ,[LastName]
            ,[Company]
      FROM tContact;
GO 50

Let us if our new table is properly created and populated

Step 2: Now let us turn the statistics on and execute a simple DELETE command

SET STATISTICS TIME ON;
GO
SET STATISTICS IO ON;
GO
 
DELETE FROM [TABLE TO DELETE];
GO

delete all rows in sql server

Step 3: Now let us remove the rows incrementally after dropping our test table and executing the script from step 1 first. We are going to use the script below to do that

norecord:
   DELETE TOP(200000) [TABLE TO DELETE];
   IF @@ROWCOUNT != 0
      goto norecord;

This time instead of trying to delete all 17 million records at the same time it will try to remove 200,000 records incrementally without too much pressure and in short time.

delete all rows in sql server

The amount of rows that you are going to delete at the same time or the number that you are going to use in the TOP() function makes a lot of difference on your query performance and execution time therefore try to choose that intelligently based on the query you have.