Table Partitioning

Partition Function
  │  defines boundary values and range direction
  │
  └─► Partition Scheme
        │  maps partition numbers → filegroups
        │
        └─► Table / Index
              │  references the scheme and specifies the partition column

Boundary values: (20231231, 20240331)

RANGE LEFT:  partition 1 = col <= 20231231
             partition 2 = col >  20231231 AND col <= 20240331
             partition 3 = col >  20240331

RANGE RIGHT: partition 1 = col <  20231231
             partition 2 = col >= 20231231 AND col <  20240340
             partition 3 = col >= 20240331

-- Monthly partitioning on an integer YYYYMM key
CREATE PARTITION FUNCTION pf_monthly (int)
AS RANGE RIGHT
FOR VALUES (
    202301, 202302, 202303, 202304,
    202305, 202306, 202307, 202308,
    202309, 202310, 202311, 202312,
    202401
);
-- Creates 15 partitions: one catch-all left of 202301, 12 monthly, one right of 202312

-- Date-based partition function
CREATE PARTITION FUNCTION pf_daily (date)
AS RANGE RIGHT
FOR VALUES (
    '2024-01-01', '2024-02-01', '2024-03-01',
    '2024-04-01', '2024-05-01', '2024-06-01'
);

-- Drop a partition function (must have no schemes using it)
DROP PARTITION FUNCTION pf_monthly;

-- View current boundary values
SELECT
    pf.name,
    prv.boundary_id,
    prv.value,
    CASE pf.boundary_value_on_right
        WHEN 1 THEN 'RANGE RIGHT'
        ELSE 'RANGE LEFT'
    END AS range_type
FROM sys.partition_functions pf
JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
WHERE pf.name = 'pf_monthly'
ORDER BY prv.boundary_id;

-- Map all partitions to the same filegroup (simplest)
CREATE PARTITION SCHEME ps_monthly
AS PARTITION pf_monthly
ALL TO ([PRIMARY]);

-- Map partitions to different filegroups (enables filegroup backup strategy)
CREATE PARTITION SCHEME ps_monthly_fg
AS PARTITION pf_monthly
TO (
    fg_archive,   -- partition 1 (before 202301)
    fg_2023_01,   -- partition 2 (202301)
    fg_2023_02,   -- partition 3 (202302)
    -- ... one entry per partition
    fg_current    -- must include one extra for NEXT USED
);

-- Mark a filegroup as "next used" for SPLIT (required before adding a boundary)
ALTER PARTITION SCHEME ps_monthly
NEXT USED [PRIMARY];

-- Drop a scheme (must have no tables/indexes using it)
DROP PARTITION SCHEME ps_monthly;

CREATE TABLE dbo.SalesEvents (
    EventID     bigint       NOT NULL,
    EventDate   date         NOT NULL,
    CustomerID  int          NOT NULL,
    Amount      money        NOT NULL,
    Payload     nvarchar(500) NULL
)
ON ps_monthly (EventDate);  -- reference the scheme, not a filegroup

CREATE TABLE dbo.SalesEvents (
    EventID     bigint NOT NULL,
    EventDate   date   NOT NULL,
    CustomerID  int    NOT NULL,
    Amount      money  NOT NULL
);

-- Partition column MUST be part of the clustered index key
CREATE CLUSTERED INDEX cx_SalesEvents
ON dbo.SalesEvents (EventDate, EventID)
ON ps_monthly (EventDate);

-- Aligned index: uses the same partition scheme as the table
CREATE NONCLUSTERED INDEX ix_SalesEvents_CustomerID
ON dbo.SalesEvents (CustomerID)
INCLUDE (Amount)
ON ps_monthly (EventDate);   -- same function, same column

-- ─────────────────────────────────────────────────────────────────────────────
-- STEP 1: Add a new empty partition for the incoming month
-- ─────────────────────────────────────────────────────────────────────────────
ALTER PARTITION SCHEME ps_monthly NEXT USED [PRIMARY];

ALTER PARTITION FUNCTION pf_monthly ()
SPLIT RANGE (202402);   -- adds boundary; new partition for 202402 and beyond

-- ─────────────────────────────────────────────────────────────────────────────
-- STEP 2: Stage the new month's data into a separate table (identical schema)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE dbo.SalesEvents_Stage (
    EventID     bigint NOT NULL,
    EventDate   date   NOT NULL,
    CustomerID  int    NOT NULL,
    Amount      money  NOT NULL
)
ON [PRIMARY];

-- Load data into staging table
INSERT INTO dbo.SalesEvents_Stage
SELECT EventID, EventDate, CustomerID, Amount
FROM SomeSource
WHERE EventDate >= '2024-02-01' AND EventDate < '2024-03-01';

-- Rebuild or create required indexes on staging table
CREATE CLUSTERED INDEX cx_Stage
ON dbo.SalesEvents_Stage (EventDate, EventID)
ON [PRIMARY];

-- Verify no data falls outside the target partition range (required for switch)
-- This CHECK constraint tells the optimizer the data is in range
ALTER TABLE dbo.SalesEvents_Stage
ADD CONSTRAINT chk_Stage_Date
CHECK (EventDate >= '2024-02-01' AND EventDate < '2024-03-01');

-- ─────────────────────────────────────────────────────────────────────────────
-- STEP 3: Switch IN — move staging table into the target partition
-- ─────────────────────────────────────────────────────────────────────────────
-- $PARTITION.pf_monthly('2024-02-01') returns the partition number = 14
DECLARE @partition_num int = $PARTITION.pf_monthly(CAST('2024-02-01' AS date));

ALTER TABLE dbo.SalesEvents_Stage
SWITCH TO dbo.SalesEvents PARTITION @partition_num;

-- ─────────────────────────────────────────────────────────────────────────────
-- STEP 4: Archive old partition — switch OUT to an archive table
-- ─────────────────────────────────────────────────────────────────────────────
-- Archive table must exist on the filegroup the partition currently lives on
CREATE TABLE dbo.SalesEvents_Archive_202301
ON [PRIMARY]
AS SELECT TOP 0 * FROM dbo.SalesEvents;

CREATE CLUSTERED INDEX cx_Archive ON dbo.SalesEvents_Archive_202301
(EventDate, EventID) ON [PRIMARY];

ALTER TABLE dbo.SalesEvents
SWITCH PARTITION $PARTITION.pf_monthly(CAST('2023-01-15' AS date))
TO dbo.SalesEvents_Archive_202301;

-- ─────────────────────────────────────────────────────────────────────────────
-- STEP 5: Merge the now-empty boundary to remove the old partition
-- ─────────────────────────────────────────────────────────────────────────────
ALTER PARTITION FUNCTION pf_monthly ()
MERGE RANGE (202301);   -- merges partition 2 (202301) into partition 1; partition 1 must be empty

-- Aligned: uses the same function (pf_monthly) and column (EventDate)
CREATE NONCLUSTERED INDEX ix_aligned
ON dbo.SalesEvents (CustomerID)
ON ps_monthly (EventDate);

-- Non-aligned: sits on a single filegroup
CREATE NONCLUSTERED INDEX ix_nonaligned
ON dbo.SalesEvents (CustomerID)
ON [PRIMARY];

-- Partition elimination occurs: optimizer evaluates $PARTITION.pf_monthly(EventDate)
-- and skips partitions outside the range
SELECT CustomerID, SUM(Amount)
FROM dbo.SalesEvents
WHERE EventDate >= '2024-01-01' AND EventDate < '2024-02-01'
GROUP BY CustomerID;

SET STATISTICS IO ON;
-- Look for "table 'SalesEvents'. Scan count N" — if N = 1, only one partition touched

-- Enable incremental statistics at table creation
CREATE TABLE dbo.SalesEvents (
    EventID   bigint NOT NULL,
    EventDate date   NOT NULL
)
ON ps_monthly (EventDate);

CREATE STATISTICS st_EventDate ON dbo.SalesEvents (EventDate)
WITH INCREMENTAL = ON;

-- Enable on an existing statistics object
UPDATE STATISTICS dbo.SalesEvents (st_EventDate)
WITH INCREMENTAL = ON;

-- Auto-update statistics incremental setting
ALTER TABLE dbo.SalesEvents
SET (INCREMENTAL_STATS = ON);  -- auto_update_statistics fires per partition

-- Force a single-partition stats update (partition 3 only)
UPDATE STATISTICS dbo.SalesEvents (st_EventDate)
WITH RESAMPLE ON PARTITIONS (3);

-- Must set NEXT USED on the scheme before splitting
ALTER PARTITION SCHEME ps_monthly NEXT USED [PRIMARY];

-- Add boundary for 202403; creates new right-most partition
ALTER PARTITION FUNCTION pf_monthly ()
SPLIT RANGE (202403);

-- The partition being removed must be EMPTY before merging
-- Merge boundary 202301: partitions 1 and 2 combine into one
ALTER PARTITION FUNCTION pf_monthly ()
MERGE RANGE (202301);

-- Create new scheme with the desired filegroup mapping
CREATE PARTITION SCHEME ps_monthly_new
AS PARTITION pf_monthly
TO (fg_cold, fg_cold, [PRIMARY], [PRIMARY], ...);  -- old partitions to cold storage

-- Rebuild clustered index onto the new scheme (rewrites data into new FGs)
CREATE CLUSTERED INDEX cx_SalesEvents
ON dbo.SalesEvents (EventDate, EventID)
WITH (DROP_EXISTING = ON)
ON ps_monthly_new (EventDate);

SELECT
    p.partition_number,
    p.rows,
    prv.value                            AS boundary_value,
    SUM(a.total_pages) * 8 / 1024.0     AS total_mb,
    SUM(a.used_pages)  * 8 / 1024.0     AS used_mb,
    fg.name                             AS filegroup_name
FROM sys.partitions p
JOIN sys.tables t ON p.object_id = t.object_id
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values prv
    ON pf.function_id = prv.function_id
    AND p.partition_number = prv.boundary_id + CASE pf.boundary_value_on_right WHEN 1 THEN 1 ELSE 0 END
JOIN sys.destination_data_spaces dds
    ON ps.data_space_id = dds.partition_scheme_id
    AND p.partition_number = dds.destination_id
JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
WHERE t.name = 'SalesEvents'
  AND i.index_id <= 1  -- clustered or heap only
ORDER BY p.partition_number;

SELECT $PARTITION.pf_monthly(202403);  -- returns 15 (or whatever partition number)

SELECT
    t.name   AS table_name,
    i.name   AS index_name,
    i.type_desc,
    ps.name  AS partition_scheme,
    pf.name  AS partition_function,
    c.name   AS partition_column,
    CASE WHEN i.data_space_id = base_i.data_space_id THEN 'ALIGNED' ELSE 'NOT ALIGNED' END AS alignment
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.indexes base_i ON t.object_id = base_i.object_id AND base_i.index_id <= 1
LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
LEFT JOIN sys.index_columns ic ON i.object_id = ic.object_id
    AND i.index_id = ic.index_id AND ic.partition_ordinal > 0
LEFT JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE t.name = 'SalesEvents'
ORDER BY i.index_id;