SQL Server 表分區建立完整圖文教程：檔案群組、分區函數、分區方案、測試驗證

適用場景

• 大型資料表 (億級行) 查詢/維護效能瓶頸
• 歷史資料冷熱分離 (近期熱資料放 SSD，舊資料放 HDD/雲端存儲)
• 滾動窗口歸檔 (每月/每年自動切換分區，秒級切換/刪除)
• 備份/還原粒度控制 (僅備份變更分區)

分區核心概念三部曲

1. 分區函數：定義 **邊界值** (Range Left/Right)，決定資料如何按分區欄位值劃分區間。
2. 分區方案：將分區函數產生的 **區間映射到檔案群組** (物理存儲)。
3. 建表指定方案：ON PartitionScheme(PartitionColumn)，引擎自動路由寫入/讀取對應分區。

步驟 1：建立資料庫與多檔案群組 (掛載不同磁碟)

**最佳實踐**：每個檔案群組對應一個**獨立物理磁碟/陣列** (如 FG1→SSD, FG2→HDD, FG3→Network Storage)，實現 I/O 隔離與成本優化。

步驟 2：新增資料庫檔案至對應檔案群組

• 邏輯名稱：PartDB_FG1, PartDB_FG2...
• 檔案群組：選擇對應建立的 FG
• 路徑：指向對應磁碟掛載點 (如 E:\Data\PartDB_FG1.ndf、F:\Data\PartDB_FG2.ndf)
• 初始大小/自動增長：預估分區資料量預分配，避免頻繁自動增長碎片。

步驟 3：建立分區函數

-- 以 LoginDate (DATETIME) 為分區欄位，按年劃分
CREATE PARTITION FUNCTION PF_LoginDate (DATETIME)
AS RANGE RIGHT FOR VALUES 
('2020-01-01', '2021-01-01', '2022-01-01', '2023-01-01');
-- RANGE RIGHT: 邊界值屬於「右側」分區
-- 分區結果:
-- P1: LoginDate < '2020-01-01'
-- P2: '2020-01-01' <= LoginDate < '2021-01-01'
-- P3: '2021-01-01' <= LoginDate < '2022-01-01'
-- P4: '2022-01-01' <= LoginDate < '2023-01-01'
-- P5: LoginDate >= '2023-01-01'

RANGE LEFT vs RIGHT**：LEFT 邊界值屬左側分區 (≤ boundary)；RIGHT 邊界值屬右側 (> boundary)。日期類通常用 RIGHT 配合「月初」邊界更直觀。

步驟 4：建立分區方案 (映射至檔案群組)

CREATE PARTITION SCHEME PS_LoginDate
AS PARTITION PF_LoginDate
TO (
    FG_2019,  -- P1: 2019 以前
    FG_2020,  -- P2: 2020
    FG_2021,  -- P3: 2021
    FG_2022,  -- P4: 2022
    FG_2023   -- P5: 2023 及以後
);
-- 檔案群組數量 = 分區數量 (邊界值 N 個 → N+1 個分區)

步驟 5：建立分區表

CREATE TABLE dbo.PartitionTable (
    ID INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(50) NOT NULL,
    LoginDate DATETIME NOT NULL  -- 分區欄位，必須包含在叢集索引中
)
ON PS_LoginDate(LoginDate);  -- 指定分區方案與分區欄位

-- 建立叢集索引 (分區表叢集索引必須包含分區欄位)
CREATE CLUSTERED INDEX CX_PartitionTable_LoginDate 
ON dbo.PartitionTable(LoginDate) 
ON PS_LoginDate(LoginDate);

步驟 6：驗證分區效果

6.1 查看分區元資料

SELECT 
    p.partition_number AS PartitionNum,
    f.name AS FileGroup,
    p.rows AS RowCount,
    prv.value AS BoundaryValue,
    CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS RangeType
FROM sys.partitions p
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
JOIN sys.filegroups f ON ds.data_space_id = f.data_space_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
WHERE p.object_id = OBJECT_ID('dbo.PartitionTable')
ORDER BY p.partition_number;

6.2 測試資料插入與分區分布

INSERT dbo.PartitionTable (Name, LoginDate) VALUES
('User_2019', '2019-06-15'),
('User_2020', '2020-03-22'),
('User_2021', '2021-11-01'),
('User_2022', '2022-07-04'),
('User_2023', '2023-01-15');

-- 查詢各分區行數
SELECT $PARTITION.PF_LoginDate(LoginDate) AS PartitionNum, COUNT(*) AS Cnt
FROM dbo.PartitionTable
GROUP BY $PARTITION.PF_LoginDate(LoginDate);

進階運維：滾動窗口 / 分區切換 / 歸檔

1. 新增分區 (下一年)

-- 1. 修改方案：指定下一個分區使用新檔案群組
ALTER PARTITION SCHEME PS_LoginDate NEXT USED FG_2024;
-- 2. 修改函數：分割最後一個分區
ALTER PARTITION FUNCTION PF_LoginDate() SPLIT RANGE ('2024-01-01');

2. 秒級歸檔/刪除舊分區 (Switch Out)

-- 建立同結構暫存表 (同檔案群組)
CREATE TABLE dbo.PartitionTable_Archive_2019 ( ... ) ON FG_2019;
CREATE CLUSTERED INDEX CX_Archive_2019 ON dbo.PartitionTable_Archive_2019(LoginDate) ON FG_2019;

-- 切換分區 1 到暫存表 (元資料操作，毫秒級，無資料移動)
ALTER TABLE dbo.PartitionTable SWITCH PARTITION 1 TO dbo.PartitionTable_Archive_2019;

-- 現在可對 Archive 表進行備份/壓縮/移動/刪除，完全不影響線上表
DROP TABLE dbo.PartitionTable_Archive_2019; -- 或備份後刪除

3. 分區合併 (合併相鄰空分區)

ALTER PARTITION FUNCTION PF_LoginDate() MERGE RANGE ('2020-01-01');

關鍵限制與避坑指南

總結

表分區是 SQL Server **大表治理的核心武器**。本文完整演示了：**多檔案群組佈局 → 分區函數 (邊界) → 分區方案 (映射) → 建表指定 → 驗證/切換/滾動窗口** 全生命週期操作。**核心價值**：邏輯上單一表，物理上多檔案群組分佈，實現「冷熱分離、秒級歸檔、維護窗口最小化」。建立前務必評估「查詢模式是否受益於分區裁剪」、「維護複雜度是否可控」。