SQL Server 分區表完整實戰：從概念、建立到檔案群組規劃

前言

當單表資料超過百萬筆，且查詢/維運呈現明顯的「熱/冷資料」特徵（如：近三個月頻繁讀寫，歷史資料唯讀查詢），**分區表** 能在不修改應用程式碼的前提下，大幅提升查詢效能與維運彈性。本文完整演示從檔案群組規劃、分區函數/方案建立、到分區表建立的全流程。

一、何時需要分區表？

若全表皆為熱資料（均勻高頻存取），分區反而增加開銷，不建議使用。

二、核心概念：邏輯一張表，物理多張表

• 分區函數：定義「依何欄位、何範圍值」切分資料（如：以 SaleTime 切 2010/2011/2012/2013 四個界點）。
• 分區方案：將函數產生的各分區**對應到檔案群組**（物理儲存位置）。
• 分區表：建表時指定 ON PartitionScheme(PartitionColumn)，邏輯上仍是單一表。

三、步驟 1：規劃並建立檔案群組與檔案

3.1 透過 SSMS 圖形化介面

1. 資料庫右鍵 → 屬性 → 檔案群組 → 新增：Sale2009、Sale2010、Sale2011、Sale2012、Sale2013。
2. 切換「檔案」頁籤 → 新增 5 個 .ndf 檔案，分別指派到上述群組。
3. 最佳實踐：不同群組放置於不同實體磁碟/磁碟陣列（SSD 給熱資料，HDD 給冷資料），發揮 I/O 平行度。

3.2 T-SQL 腳本化（建議納入版控）

ALTER DATABASE SalesDB ADD FILEGROUP Sale2009;
ALTER DATABASE SalesDB ADD FILEGROUP Sale2010;
-- ... 依序新增群組

ALTER DATABASE SalesDB
ADD FILE (NAME='Sale2009_data', FILENAME='D:\Data\Sale2009.ndf', SIZE=1GB, FILEGROWTH=512MB)
TO FILEGROUP Sale2009;
-- ... 依序新增檔案至對應群組

四、步驟 2：建立分區函數

CREATE PARTITION FUNCTION pf_SaleDate (DATETIME)
AS RANGE RIGHT FOR VALUES ('20100101','20110101','20120101','20130101');

關鍵參數解析

日期字面值建議用 YYYYMMDD 格式，避免語系/日期格式設定導致解析錯誤。

五、步驟 3：建立分區方案

CREATE PARTITION SCHEME ps_SaleDate
AS PARTITION pf_SaleDate
TO (Sale2009, Sale2010, Sale2011, Sale2012, Sale2013);

• 方案將函數的 5 個分區一一映射到 5 個檔案群組。
• 若群組數 < 分區數，多餘分區會進 PRIMARY；反之則有群組閒置。

六、步驟 4：建立分區表

CREATE TABLE dbo.Sale (
    Id INT IDENTITY(1,1) NOT NULL,
    Name VARCHAR(16) NOT NULL,
    SaleTime DATETIME NOT NULL
    -- 注意：分區表**不可再建立叢集索引**，分區鍵隱含排序
) ON ps_SaleDate(SaleTime);

• ON ps_SaleDate(SaleTime) 指定分區方案與分區欄位。
• 分區欄位**必須包含於所有唯一索引鍵中**（含 PK），否則報錯。

七、驗證分區表建立成功

-- 檢視表屬性
SELECT * FROM sys.tables WHERE name = 'Sale' AND is_partitioned = 1;

-- 檢視分區細節
SELECT 
    p.partition_number,
    f.name AS filegroup,
    p.rows,
    prv.value AS boundary_value
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
LEFT JOIN sys.partition_range_values prv ON prv.function_id = 
    (SELECT function_id FROM sys.partition_functions WHERE name = 'pf_SaleDate')
    AND prv.boundary_id = p.partition_number
WHERE p.object_id = OBJECT_ID('dbo.Sale');

八、分區表維運優勢

• 分區切換：ALTER TABLE ... SWITCH PARTITION ... TO ... 秒級歸檔/載入百萬筆，無資料移動、無記錄寫入。
• 分區修剪：查詢含分區欄位條件時，優化器僅掃描相關分區。
• 獨立維運：針對單一分區重建索引、更新統計資料、設定壓縮/唯讀。
• 備份彈性：檔案群組層級備份/還原，冷資料群組可設 READ_ONLY 免備份。

九、常見誤區與限制