Creating Partition on Existing Tables

-- Summary. Full article: "Creating Partition on Existing tables and Rolling Partitions" by Avinash Gunjuluri, SQLServerCentral – https://www.sqlservercentral.com/articles/creating-partition-on-existing-tables-and-rolling-partitions

-- Steps to partition an existing table by a date column:
-- 1. Make sure the filegroups exist (e.g. PRIMARY for current data, ARCHIVE on cheaper storage).
-- 2. Create a partition function on the date boundaries.
-- 3. Create a partition scheme that maps the partitions to the filegroups.
-- 4. Create the clustered index on the partition scheme - this moves the existing rows.
-- If the table already has a clustered index on another column, drop it first.
-- 5. Sliding window: when a period ends, MERGE the oldest boundary and SPLIT a new one, in one transaction.

CREATE PARTITION FUNCTION pf_ByYear (DATETIME)
  AS RANGE RIGHT FOR VALUES ('2026-01-01');

CREATE PARTITION SCHEME ps_ByYear
  AS PARTITION pf_ByYear TO ([ARCHIVE], [PRIMARY]);

CREATE CLUSTERED INDEX IX_MyTable_OrderDate
  ON dbo.MyTable (OrderDate)
  ON ps_ByYear (OrderDate);

-- Rows per partition
SELECT p.partition_number, p.rows
  FROM sys.partitions AS p
 WHERE p.object_id = OBJECT_ID('dbo.MyTable') AND p.index_id = 1;

-- Sliding window, once a year: the old year moves to ARCHIVE, a new partition opens on PRIMARY
BEGIN TRAN;
ALTER PARTITION FUNCTION pf_ByYear() MERGE RANGE ('2026-01-01');
ALTER PARTITION SCHEME ps_ByYear NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_ByYear() SPLIT RANGE ('2027-01-01');
COMMIT;