SQL Server 自動化分區管理方案：滾動分區、預警切換與分區管理表設計

適用場景與前提

• 分區鍵為遞增：自增 ID 或日期
• 可接受歷史資料移出分區表（歸檔/刪除）
• 單日寫入量 < 分區容量閾值（Change_Value - Part_Value），否則需縮短排程頻率

核心架構圖解

• 分區函數：定義邊界值（如 ID 範圍）
• 分區方案：將分區對應到檔案群組（FG1~FG5 + PRIMARY）
• 循環複用：最舊分區資料切換出 → 截斷 → 重新掛載為最新分區
• PRIMARY 檔案群組：預留 ≥ 1 分區大小空間，作為切換暫存區

分區管理表：自動化大腦

CREATE TABLE PartitionManage (
    PartitionNum INT PRIMARY KEY,      -- 分區編號 1~5
    Part_Value BIGINT NOT NULL,        -- 分區函數邊界值
    Change_Value BIGINT NOT NULL,      -- 觸發切換的預警 ID 值
    FileGroup_String VARCHAR(50),      -- 目標檔案群組名稱（如 FG1）
    IsDone BIT NOT NULL DEFAULT 0,     -- 1=已處理
    UpdateTime DATETIME                -- 最後切換時間
);

彈性控制能力

1. 調整 Change_Value → 提前/延後觸發切換
2. 調整 Part_Value → 改變分區跨度
3. 修改 FileGroup_String → 跳過/重新指派檔案群組
4. 批次更新 IsDone = 1 → 跳過已處理分區（如手動調整後同步）
5. 記錄 UpdateTime → 審計追蹤

自動化切換邏輯（每日排程作業）

-- 1. 找出需切換的分區
DECLARE @PNum INT, @PVal BIGINT, @FG VARCHAR(50);
SELECT TOP 1 @PNum = PartitionNum, @PVal = Part_Value, @FG = FileGroup_String
FROM PartitionManage
WHERE IsDone = 0 AND Change_Value <= (SELECT MAX(ID) FROM FactTable)
ORDER BY PartitionNum;

IF @PNum IS NOT NULL
BEGIN
    -- 2. 建立暫存表（同結構、同分區方案、在 PRIMARY 上）
    CREATE TABLE FactTable_Temp (...) ON PartitionScheme(ID);

    -- 3. 切換舊分區資料到暫存表（中繼資料操作，秒級）
    ALTER TABLE FactTable SWITCH PARTITION @PNum TO FactTable_Temp PARTITION @PNum;

    -- 4. 截斷暫存表（釋放空間，或改為歸檔表保留歷史）
    TRUNCATE TABLE FactTable_Temp;

    -- 5. 修改分區方案：下一個分區使用此檔案群組
    ALTER PARTITION SCHEME PartitionScheme NEXT USED @FG;

    -- 6. 修改分區函數：分割邊界值（擴展範圍）
    ALTER PARTITION FUNCTION PartitionFunc() SPLIT RANGE (@PVal);

    -- 7. 更新管理表
    UPDATE PartitionManage SET IsDone = 1, UpdateTime = GETDATE() WHERE PartitionNum = @PNum;
END

關鍵技術細節

• 索引對齊：所有索引必須建立在相同分區方案上，隨資料自動切換
• SWITCH PARTITION：純中繼資料操作，無資料移動，極快；要求來源/目標結構、檢查約束、分區方案完全一致
• 分區函數/方案修改順序：先改方案 NEXT USED，再改函數 SPLIT RANGE（與建立順序相反）
• PRIMARY 空間預留：避免切換時動態擴展檔案造成延遲

實戰分佈圖譜

• ID 分區：各分區筆數均勻，若出現明顯波峰 → 切換不及時
• 分類欄位分區：筆數天然不均，需前期規劃邊界值使分區筆數盡量平衡