UmbraCare LOGO

Mastering Paginated Deletions in MSSQL Tables

Remove records from large tables using the paginated deletion technique to avoid common pitfalls like bloated transaction logs and performance bottlenecks

Published on: , by:
Piotr Bach picture
Piotr Bach
Wojciech Tengler picture
Wojciech Tengler
MS SQL

Why Use Paginated Deletions?

Deleting large amounts of records from a table at once may:

  • Balloon the SQL Transaction Log file.

  • Cause disk space issues.

  • Strain system performance.

Paginated deletions divide the workload into manageable chunks, reducing resource consumption and ensuring stable performance.

Step 1: Creating a Sample Table

CREATE TABLE [Logs] (
    [LogId] INTEGER NOT NULL IDENTITY(1, 1), 
    [Text] VARCHAR(MAX) NULL,
    [Severity] VARCHAR(7) NULL,
    [Created] DATETIME,
    PRIMARY KEY ([LogId])
);
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 1','Error',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 2','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 3','Info',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 4','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 5','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 6','Error',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 7','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 8','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 9','Info',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 10','Info',GETDATE())

Step 2: Preparing for Deletion

Set up the necessary variables and temporary table:

DECLARE @pageSize INTEGER = 2; -- Records per batch
DECLARE @tempLogIdWithPageIndexTable TABLE (
    LogId INT,
    PageIndex INT
);

Assign page indices to the records based on the batch size:

INSERT INTO @tempLogIdWithPageIndexTable (LogId, PageIndex)
SELECT LogId, 
       CEILING(CAST(ROW_NUMBER() OVER(ORDER BY LogId) AS DECIMAL) / CAST(@pageSize AS DECIMAL)) AS PageIndex
FROM [Logs];

Calculate total pages:

DECLARE @totalCount INTEGER;
DECLARE @pageCount INTEGER;

SELECT @totalCount = COUNT(1) FROM @tempLogIdWithPageIndexTable;
SET @pageCount = CEILING(CAST(@totalCount AS DECIMAL) / CAST(@pageSize AS DECIMAL));

Step 3: Paginated Deletion Logic

Perform the deletion page by page:

DECLARE @pageIndex INTEGER = 1;

WHILE (@pageIndex <= @pageCount)
BEGIN
    DELETE FROM [Logs]
    WHERE LogId IN (
        SELECT LogId FROM @tempLogIdWithPageIndexTable WHERE PageIndex = @pageIndex
    );

    PRINT 'Processed page ' + CAST(@pageIndex AS VARCHAR(MAX)) + '/' + CAST(@pageCount AS VARCHAR(MAX));
    SET @pageIndex = @pageIndex + 1;
END

Step 4: Post-Deletion Analysis

Verify the deletion results:

PRINT 'Script finished at : ' + CONVERT(varchar(25), getdate(), 120) 

SET @totalCount = (SELECT count(1) FROM dbo.Logs)

-- print the report when the job is done
PRINT 'Number of records after deletion : ' + CAST(@totalCount as VARCHAR(MAX))

Example output:

Total number of records to remove: 10
Page size: 2
Total pages: 5
Script started at : 2020-09-05 15:36:21

(2 rows affected)
Processed page 1/5

(2 rows affected)
Processed page 2/5

(2 rows affected)
Processed page 3/5

(2 rows affected)
Processed page 4/5

(2 rows affected)
Processed page 5/5
Script finished at : 2020-09-05 15:36:21
Number of records after deletion : 0

Complete Script for Paginated Deletions

use [dbName]

/* Removing data from large table in MSSQL using batch */

CREATE TABLE [Logs] (
    [LogId] INTEGER NOT NULL IDENTITY(1, 1), 
    [Text] VARCHAR(MAX) NULL,
    [Severity] VARCHAR(7) NULL,
    [Created] DATETIME,
    PRIMARY KEY ([LogId])
);
GO

INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 1','Error',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 2','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 3','Info',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 4','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 5','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 6','Error',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 7','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 8','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 9','Info',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 10','Info',GETDATE())

SELECT count(1) FROM dbo.Logs;

DECLARE @tempLogIdWithPageIndexTable TABLE
(
	LogId INT,
	PageIndex INT
)

DECLARE @pageSize INTEGER = 2;
DECLARE @totalCount INTEGER
​
INSERT INTO @tempLogIdWithPageIndexTable (LogId, PageIndex) (
	SELECT  LogId, 
	CEILING(CAST(ROW_NUMBER() OVER(ORDER BY LogId) AS DECIMAL)/CAST(@pageSize AS DECIMAL)) AS PageIndex
	FROM [Logs] -- You can add WHERE conditions here
)

-- Get total counts of records to remove

SELECT @totalCount = count(1) FROM @tempLogIdWithPageIndexTable
​
DECLARE @pageCount INTEGER
SET @pageCount = CEILING(CAST(@totalCount as DECIMAL) / CAST(@pageSize as DECIMAL))
​
DECLARE @pageIndex INTEGER = 1
​
-- print the report with metrics before start

PRINT 'Total number of records to remove : ' + CAST(@totalCount as VARCHAR(MAX))
PRINT 'Page size : ' + CAST(@pageSize as VARCHAR(MAX))
PRINT 'Total pages : ' + CAST(@pageCount as VARCHAR(MAX))
PRINT 'Script started at : ' + CONVERT(varchar(25), getdate(), 120) 

-- delete records in a loop by paging

WHILE(@pageIndex <= @pageCount)
BEGIN	
	-- you can delete related entries first using JOIN here
	DELETE [dbo].Logs where LogId in 
	(SELECT LogId FROM @tempLogIdWithPageIndexTable WHERE PageIndex = @pageIndex)
​
	PRINT 'Processed page ' + CAST(@pageIndex as VARCHAR(MAX)) + '/' + CAST(@pageCount as VARCHAR(MAX))
	SET @pageIndex = @pageIndex + 1
END

PRINT 'Script finished at : ' + CONVERT(varchar(25), getdate(), 120) 

SET @totalCount = (SELECT count(1) FROM dbo.Logs)

-- print the report when the job is done
PRINT 'Number of records after deletion : ' + CAST(@totalCount as VARCHAR(MAX))

SELECT count(1) FROM dbo.Logs;

Remember When Deleting All Records from Table

The fastest way to clear an entire table is to use the TRUNCATE command:

TRUNCATE TABLE [Logs];

Handle Foreign Key Constraints

If TRUNCATE isn’t possible due to foreign key constraints:

  1. Drop foreign key constraints.

  2. Use TRUNCATE.

  3. Recreate foreign key constraints.

Key Takeaways

  • Paginated deletions help manage large datasets efficiently without overwhelming system resources.

  • This method avoids common pitfalls like bloated transaction logs and performance bottlenecks.

  • Use TRUNCATE for faster cleanup when applicable, but handle constraints carefully.

For complete source code, check out the GitHub repository

EDITOR'S PICK

↑ Top ↑