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];
SQL Server DBA at Ofogh Koorosh chain stores
Excellent, thank you.