SQL Server Archive Process Using Filegroups and Table Partitioning

How This Archive Pattern Works

As tables grow over years, query performance degrades, backup windows grow, and maintenance operations slow down. The standard solution is to archive old data — but naive archiving using INSERT...SELECT...DELETE locks large portions of the table, generates massive transaction log growth, and takes hours to complete.

The filegroup-based partition switching approach solves this. Instead of moving rows, SQL Server simply reassigns a partition’s metadata pointer from the main table to an archive table. The data stays where it is on disk — only the ownership changes. The switch operation completes in milliseconds regardless of how many rows the partition contains.

Key Concepts

RANGE LEFT Partition Boundaries Visualised

With RANGE LEFT and boundary values of '2022-01-01', '2023-01-01', '2024-01-01', '2025-01-01', '2026-01-01':

Prerequisites

• SQL Server Enterprise Edition (or Developer Edition for testing)
• Sufficient disk space for new filegroup files (.ndf files)
• The partition column must be part of any clustered index or primary key on the table
• The archive table must have an identical schema to the source partition (same columns, same types, same constraints)
• Both tables must have matching indexes for SWITCH to succeed
• The sysadmin or db_owner role — or ALTER ANY DATASPACE permission

The Complete Implementation

-- Add a filegroup for each archive year
ALTER DATABASE YourDatabase ADD FILEGROUP [ArchiveFileGroup2022];
ALTER DATABASE YourDatabase ADD FILEGROUP [ArchiveFileGroup2023];
ALTER DATABASE YourDatabase ADD FILEGROUP [ArchiveFileGroup2024];
ALTER DATABASE YourDatabase ADD FILEGROUP [ArchiveFileGroup2025];
ALTER DATABASE YourDatabase ADD FILEGROUP [ArchiveFileGroup2026];

-- Add a data file to each filegroup
-- Update the FILENAME paths to match your data drive
ALTER DATABASE YourDatabase
ADD FILE
(
    NAME     = N'Archive2022',
    FILENAME = N'D:\SQLData\Archive2022.ndf',
    SIZE     = 256MB,
    MAXSIZE  = UNLIMITED,
    FILEGROWTH = 256MB
) TO FILEGROUP [ArchiveFileGroup2022];

ALTER DATABASE YourDatabase
ADD FILE
(
    NAME     = N'Archive2023',
    FILENAME = N'D:\SQLData\Archive2023.ndf',
    SIZE     = 256MB,
    MAXSIZE  = UNLIMITED,
    FILEGROWTH = 256MB
) TO FILEGROUP [ArchiveFileGroup2023];

-- Repeat for 2024, 2025, 2026
-- Size the files appropriately for how much data each year contains

-- Partition function: splits the table by year
-- RANGE LEFT means the boundary date belongs to the left partition
-- e.g. '2023-01-01' is the upper bound of the 2022 partition
CREATE PARTITION FUNCTION MyPartitionFunction (DATE)
AS RANGE LEFT FOR VALUES
(
    '2022-01-01',   -- Partition 1: everything before 2022
    '2023-01-01',   -- Partition 2: 2022 data
    '2024-01-01',   -- Partition 3: 2023 data
    '2025-01-01',   -- Partition 4: 2024 data
    '2026-01-01'    -- Partition 5: 2025 data
                    -- Partition 6: 2026 and beyond
);

-- Map each partition to its filegroup
-- 6 boundary values = 6 partitions = 6 filegroup entries needed
CREATE PARTITION SCHEME MyPartitionScheme
AS PARTITION MyPartitionFunction
TO
(
    [PRIMARY],              -- Partition 1: pre-2022 data
    [ArchiveFileGroup2022], -- Partition 2: 2022 data
    [ArchiveFileGroup2023], -- Partition 3: 2023 data
    [ArchiveFileGroup2024], -- Partition 4: 2024 data
    [ArchiveFileGroup2025], -- Partition 5: 2025 data
    [ArchiveFileGroup2026]  -- Partition 6: 2026+ data
);

-- Main table: partitioned by DateColumn
CREATE TABLE MyMainTable
(
    ID          INT  NOT NULL,
    DateColumn  DATE NOT NULL,
    Description NVARCHAR(500) NULL,
    Amount      DECIMAL(18, 2) NULL,
    CreatedBy   NVARCHAR(100) NULL,
    -- Add all columns your table requires
    CONSTRAINT PK_MyMainTable PRIMARY KEY CLUSTERED (ID, DateColumn)
)
ON MyPartitionScheme (DateColumn);

-- Archive table for 2022 data
-- Must match main table schema exactly
-- CHECK constraint aligns with the partition boundary
CREATE TABLE MyArchiveTable2022
(
    ID          INT  NOT NULL,
    DateColumn  DATE NOT NULL,
    Description NVARCHAR(500) NULL,
    Amount      DECIMAL(18, 2) NULL,
    CreatedBy   NVARCHAR(100) NULL,
    CONSTRAINT PK_MyArchiveTable2022 PRIMARY KEY CLUSTERED (ID, DateColumn),
    CONSTRAINT CK_MyArchiveTable2022_Date
        CHECK (DateColumn >= '2022-01-01' AND DateColumn < '2023-01-01')
)
ON [ArchiveFileGroup2022];

-- Archive table for 2023 data
CREATE TABLE MyArchiveTable2023
(
    ID          INT  NOT NULL,
    DateColumn  DATE NOT NULL,
    Description NVARCHAR(500) NULL,
    Amount      DECIMAL(18, 2) NULL,
    CreatedBy   NVARCHAR(100) NULL,
    CONSTRAINT PK_MyArchiveTable2023 PRIMARY KEY CLUSTERED (ID, DateColumn),
    CONSTRAINT CK_MyArchiveTable2023_Date
        CHECK (DateColumn >= '2023-01-01' AND DateColumn < '2024-01-01')
)
ON [ArchiveFileGroup2023];

-- Repeat for 2024, 2025, 2026 with matching date ranges

-- Switch 2022 data (partition 2) to the archive table
ALTER TABLE MyMainTable
SWITCH PARTITION 2 TO MyArchiveTable2022;

-- Switch 2023 data (partition 3) to the archive table
ALTER TABLE MyMainTable
SWITCH PARTITION 3 TO MyArchiveTable2023;

-- Switch 2024 data (partition 4) to the archive table
ALTER TABLE MyMainTable
SWITCH PARTITION 4 TO MyArchiveTable2024;

-- Verify the main table partitions are now empty
SELECT
    p.partition_number,
    p.rows,
    prv.value AS boundary_value
FROM sys.partitions              p
JOIN sys.tables                  t  ON p.object_id = t.object_id
JOIN sys.partition_schemes       ps ON ps.data_space_id =
    (SELECT index_id FROM sys.indexes WHERE object_id = t.object_id
     AND type = 1)
JOIN sys.partition_functions     pf ON pf.function_id = ps.function_id
LEFT JOIN sys.partition_range_values prv
    ON prv.function_id = pf.function_id
    AND prv.boundary_id = p.partition_number
WHERE t.name = 'MyMainTable'
ORDER BY p.partition_number;

-- Merge the boundary to remove empty partition for 2022
-- This removes the '2022-01-01' boundary — partition 1 and 2 become one
ALTER PARTITION FUNCTION MyPartitionFunction()
MERGE RANGE ('2022-01-01');

-- Merge the 2023 boundary
ALTER PARTITION FUNCTION MyPartitionFunction()
MERGE RANGE ('2023-01-01');

-- Note: MERGE removes the boundary value and merges the two adjacent partitions
-- Only do this after confirming the partition is empty via the row count query above

Verifying the Archive

After completing the switch operations, verify the row counts are as expected in both the main table and archive tables:

-- Row counts by partition in the main table
SELECT
    p.partition_number,
    p.rows                   AS row_count,
    fg.name                  AS filegroup_name
FROM sys.partitions              p
JOIN sys.allocation_units        au ON au.container_id = p.hobt_id
JOIN sys.filegroups              fg ON fg.data_space_id = au.data_space_id
JOIN sys.tables                  t  ON p.object_id = t.object_id
WHERE t.name = 'MyMainTable'
AND   p.index_id IN (0, 1)       -- heap or clustered index only
ORDER BY p.partition_number;

-- Confirm archive tables have data
SELECT '2022' AS year, COUNT(*) AS rows FROM MyArchiveTable2022
UNION ALL
SELECT '2023', COUNT(*) FROM MyArchiveTable2023
UNION ALL
SELECT '2024', COUNT(*) FROM MyArchiveTable2024;

ALTER TABLE SWITCH Requirements

The SWITCH operation has strict requirements. Understanding these prevents the most common errors:

Ongoing Maintenance and Adding New Years

Each year, before new data arrives, add a new boundary to the partition function and a new filegroup to accommodate it:

-- Add a new year (2027) to the partition function
-- First, mark the next filegroup as the destination for the split
ALTER PARTITION SCHEME MyPartitionScheme
NEXT USED [ArchiveFileGroup2027];

-- Then split the partition function to add the new boundary
ALTER PARTITION FUNCTION MyPartitionFunction()
SPLIT RANGE ('2027-01-01');

-- Create the archive table for 2027 when you are ready to archive
CREATE TABLE MyArchiveTable2027
(
    ID          INT  NOT NULL,
    DateColumn  DATE NOT NULL,
    Description NVARCHAR(500) NULL,
    Amount      DECIMAL(18, 2) NULL,
    CreatedBy   NVARCHAR(100) NULL,
    CONSTRAINT PK_MyArchiveTable2027 PRIMARY KEY CLUSTERED (ID, DateColumn),
    CONSTRAINT CK_MyArchiveTable2027_Date
        CHECK (DateColumn >= '2027-01-01' AND DateColumn < '2028-01-01')
)
ON [ArchiveFileGroup2027];

Making Archive Filegroups Read-Only

Once a year's data is fully archived and no further changes are expected, mark the filegroup as read-only. This protects the data, reduces backup complexity, and can improve query performance:

-- Make the 2022 archive filegroup read-only
ALTER DATABASE YourDatabase
MODIFY FILEGROUP [ArchiveFileGroup2022] READ_ONLY;

-- Verify filegroup status
SELECT name, is_read_only
FROM sys.filegroups
WHERE name LIKE 'Archive%'
ORDER BY name;

Summary

The filegroup-based partition switching pattern is the production-grade approach to SQL Server data archiving. It eliminates the blocking, log growth, and downtime associated with row-by-row archiving while giving you independent control over each year's data for backup, storage tiering, and access policy purposes.

References

• Microsoft Docs – Partitioned Tables and Indexes
• Microsoft Docs – ALTER TABLE (SWITCH)
• Microsoft Docs – CREATE PARTITION FUNCTION
• Microsoft Docs – CREATE PARTITION SCHEME
• Microsoft Docs – Modify a Partition Function (SPLIT and MERGE)
• Microsoft Docs – Database Files and Filegroups