SQL Server 2005 表分區三步曲：分區函數、分區配置、分區表實戰

前言

SQL Server 2005 引入表分區，將大表依範圍劃分為多個物理儲存單元 (分區)，各分區可置於不同檔案群組/磁碟。查詢時優化器僅掃描相關分區 (Partition Elimination)，並支援跨分區平行查詢，大幅提升大表效能。

分區前三思

1. 管理成本與複雜度增加，確認效能收益 > 維護成本
2. 評估資料邏輯分組模式，決定分區鍵 (如日期、區域、租戶 ID)
3. 規劃多檔案群組：分離熱/冷資料、備份分級、I/O 隔離

三步曲核心流程

步驟 1：建立分區函數 (邏輯邊界)

-- 清理既有物件
IF OBJECT_ID('tb_partition1') IS NOT NULL DROP TABLE tb_partition1
IF OBJECT_ID('tb_partition2') IS NOT NULL DROP TABLE tb_partition2
GO

IF EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = 'my_psch')
    DROP PARTITION SCHEME my_psch
GO

IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = 'my_pfun')
    DROP PARTITION FUNCTION my_pfun
GO

-- 建立分區函數：以 datetime 為鍵，RANGE LEFT (邊界值屬左分區)
CREATE PARTITION FUNCTION my_pfun (datetime)
AS RANGE LEFT FOR VALUES ('2007-12-31')
/*
  產生 2 個分區：
  Partition 1: datetime <= '2007-12-31'
  Partition 2: datetime >  '2007-12-31'
  RANGE RIGHT 則邊界值屬右分區
*/

步驟 2：建立分區配置 (物理映射)

-- 將分區映射到檔案群組 (此處皆映射到 PRIMARY，實務建議分離)
CREATE PARTITION SCHEME my_psch
AS PARTITION my_pfun
TO ([PRIMARY], [PRIMARY])
/*
  可重複使用：多張表套用同一分區配置/函數
  實務建議：
  TO ([FG_Cold], [FG_Hot])  -- 冷熱資料分離不同磁碟
  或 TO ([FG_2023], [FG_2024], [FG_Current]) -- 年度分離
*/

步驟 3：建立分區表 (套用分區配置)

CREATE TABLE tb_partition1
(
    id int IDENTITY,
    dt datetime,
    [name] AS 'Name' + LTRIM(STR(id)),
    CONSTRAINT pk_tbpartition1 PRIMARY KEY CLUSTERED (id, dt) ON my_psch(dt)
) ON my_psch(dt)
GO

CREATE TABLE tb_partition2
(
    id int IDENTITY,
    dt datetime,
    [name] AS 'Name' + LTRIM(STR(id)),
    CONSTRAINT pk_tbpartition2 PRIMARY KEY CLUSTERED (id, dt) ON my_psch(dt)
) ON my_psch(dt)
GO

-- 叢集索引鍵必須包含分區欄位 (dt)，且 ON 子句指定分區配置
-- 非叢集索引若未指定 ON，預設跟隨叢集索引分區

資料填充與驗證

-- 填充測試資料
DECLARE @dt datetime = '2007-01-01'
WHILE @dt <= '2009-10-01'
BEGIN
    INSERT tb_partition1 SELECT @dt
    SET @dt = DATEADD(day, 1, @dt)
END

-- 查詢分區分布
SELECT * FROM sys.partitions
WHERE object_id IN (SELECT object_id FROM sys.tables WHERE name IN ('tb_partition1','tb_partition2'))

-- 檢視每列所屬分區
SELECT *, $PARTITION.my_pfun(dt) AS PartitionNum FROM tb_partition1

分區切換: 秒級資料移動 (元資料操作)

-- 切換前
SELECT * FROM tb_partition1
SELECT * FROM tb_partition2

-- 將 tb_partition1 分區 2 切換到 tb_partition2 分區 2
-- 前提：結構完全相同、檢查條件滿足、目標分區為空
ALTER TABLE tb_partition1
SWITCH PARTITION 2 TO tb_partition2 PARTITION 2
GO

-- 切換後
SELECT * FROM tb_partition1  -- 分區 2 資料消失
SELECT * FROM tb_partition2  -- 分區 2 出現資料

應用場景：歸檔歷史資料、載入暫存表後秒級切入主表、滾動視窗維護。

分區維護：修改三步曲

-- 1. 新增檔案群組 (可選)
ALTER DATABASE MyDB ADD FILEGROUP FG_2009
ALTER DATABASE MyDB ADD FILE (NAME='FG_2009_Data', FILENAME='E:\Data\FG_2009.ndf') TO FILEGROUP FG_2009

-- 2. 修改分區配置：指定下一個分區使用新檔案群組
ALTER PARTITION SCHEME my_psch NEXT USED [FG_2009]

-- 3. 修改分區函數：分割/合併邊界
-- 分割：將 > '2008-12-31' 的分區再切一刀
ALTER PARTITION FUNCTION my_pfun() SPLIT RANGE ('2008-12-31')

-- 合併：移除 '2007-12-31' 邊界，合併前兩分區
ALTER PARTITION FUNCTION my_pfun() MERGE RANGE ('2007-12-31')

效能驗證重點

• Partition Elimination：執行計畫顯示 `Partitioned: True`、`Actual Partitions Accessed` 遠小於總分區數
• 平行查詢：跨分區查詢出現 `Parallelism` 運算子，多執行緒同時掃描
• 索引對齊：所有索引建立在相同分區配置上，避免跨分區 Join 產生昂貴運算子

適用場景與限制

總結

表分區「三步曲」——**函數定邊界、配置映射物理、表套用配置**——將大表切分為可獨立管理的單元。善用分區消除、平行掃描、SWITCH 元資料操作，配合檔案群組隔離 I/O，是大型資料倉庫與高載 OLTP 的核心架構手段。