Deleting records from the large tables with minimum transaction log footprints

Reading Time: 3 minutes

While working as a DBA or developer, there might be some cases that you need to keep a very limited number of records in a large table and remove the remaining records (for example, you may wish to keep 1000 records in a table containing one billion records and delete the rest) and if we want to use the usual methods for removing them, we have to dedicate a long time and the result is the generation of many logs which will eventually create some problems for the system. Today, I will teach you a suitable method for removing a large number of records from a table and keep some of the information within.
It is notable that in this scenario the main table has no foreign key. If the tables have a foreign key, you must have a multi-table scenario to keep the information comprehensive. This tutorial is intended for a simple scenario such as Log tables without foreign key constraints.
To begin with, we create a test table.

Step 1: Creating the Table

CREATE TABLE [dbo].[Test1]  (
   Col1_id INT IDENTITY(1,1),
   Col2_D DATETIME DEFAULT GETDATE(),
   Col3_C NCHAR(20) DEFAULT 'My test desc!',
   Col4_T NVARCHAR(MAX) DEFAULT REPLICATE('0123456789', 100)
);
GO

In the next stage, we will add some sample records to the table.
In this example, we have 5000 records and 2 index.

Step 2: Creating the Sample Data

--Load Test Data
SET NOCOUNT ON;
INSERT [dbo].[Test1]  (Col2_D, Col3_C, Col4_T)
VALUES (DEFAULT, 'My test desc!', DEFAULT);
GO 5000 
 
--Check it.
SELECT top 10 * FROM [dbo].[Test1];
SELECT COUNT(*) As 'Test1' FROM [dbo].[Test1];
GO
 
--Create Indexes
CREATE CLUSTERED INDEX [ClusteredIndex_Test1_Col2_D] ON [dbo].[Test1]
([Col2_D] ASC) 
GO
 
CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex_Test1_Col1_ID] ON [dbo].[Test1]
([Col1_id] ASC) 
GO

The following figure shows the result of the above query:

Step 3: Creating an Empty Table Similar to the Main Table

Step 3: Creating an Empty Table Similar to the Main Table
--Now let's duplicate the original schema and change the table and index names
CREATE TABLE [dbo].[Test1_SWITCH] (
   Col1_ID INT IDENTITY(1,1),
   Col2_D DATETIME DEFAULT GETDATE(),
   Col3_C NCHAR(20) DEFAULT 'My test desc!',
   Col4_T NVARCHAR(MAX) DEFAULT REPLICATE('0123456789', 201)
);
 
CREATE CLUSTERED INDEX [ClusteredIndex_Test1_SWITCH_Col2_D] ON [dbo].[Test1_SWITCH]
([Col2_D] ASC) 
GO
 
CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex_Test1_SWITCH_Col1_ID] ON [dbo].[Test1_SWITCH]
([Col1_id] ASC) 
GO

--Check it
SELECT COUNT(*) AS 'Test1' FROM [dbo].[Test1];
SELECT COUNT(*) AS 'Test1_SWITCH' FROM [dbo].[Test1_SWITCH];

The figure below shows 5,000 rows in the main table and 0 rows in the new table.

Step 4: Switching the Tables

Run the Alter Table SWITCH command to check the number of records.

--Switch the data to a new table
ALTER TABLE [dbo].[Test1] SWITCH to [dbo].[Test1_SWITCH];
 
SELECT COUNT(*) AS 'Test1' FROM [dbo].[Test1];
SELECT COUNT(*) AS 'Test1_SWITCH' FROM [dbo].[Test1_SWITCH];

As you can see, the information was transferred to the new table quickly.

Step 5: Transferring the Required Data to the Main Table

Now you can move a small set of records to the main table. In this example, I have an Identity field, so we have to use IDENTITY_INSERT to keep the numbers in this field. This may not be the case in your real-world scenario.

--Now we can load the small subset of data back to the table.
SET NOCOUNT ON;

SET IDENTITY_INSERT [dbo].[Test1] ON;
INSERT dbo.Test1 (Col1_ID, Col2_D, Col3_C, Col4_T)
SELECT Col1_ID, Col2_D, Col3_C, Col4_T FROM [dbo].[Test1_SWITCH] WHERE Col1_ID <= 1000;
SET IDENTITY_INSERT [dbo].[Test1] OFF;
 
SELECT COUNT(*) AS 'Test1' FROM [dbo].[Test1];
SELECT COUNT(*) AS 'Test1_SWITCH' FROM [dbo].[Test1_SWITCH];

100 records were moved to the main table:

Step 6: Delete Test1_Switch Table

--Finally drop the newly created switch table. 
DROP TABLE [dbo].[Test1_SWITCH];

Author Profile

Mohsen Farhangian

SQL Server DBA at Ofogh Koorosh chain stores

Source MSSQLTips