SQL Server 2005 資料表分區技術深度解析與實戰

前言

資料庫效能調優是 DBA 核心責任。SQL Server 2005 引入原生資料表分區，所有分區位於同一伺服器、對應獨立檔案群組/檔案，查詢最佳化器自動分區剪枝、平行掃描多個分區，大幅提升大表查詢與維護效能。本文從概念、架構、實戰效能測試到分區切換歸檔，完整解析分區技術。

分區架構核心概念

三層架構

1. 分區函數：定義邊界值範圍（RANGE LEFT/RIGHT），如依日期、數值分區
2. 分區配置：將分區對應至檔案群組，實現物理儲存分離（不同磁碟/RAID）
3. 分區表/索引：建表時指定 ON partition_scheme(partition_column)

分區對齊原則

• 對齊索引： 索引分區配置與基底表相同 → SWITCH PARTITION 可瞬間完成
• 非對齊索引： 索引不同分區配置 → 切換需重建索引，較慢
• 最佳實務： 叢集索引、唯一索引務必對齊；非叢集索引視查詢模式決定

實戰效能測試：分區表 vs 非分區表

測試環境

• 筆電，記憶體 < 1GB
• 建立 8 個檔案群組，各 1 檔案，對應分區函數 8 個範圍
• 分區表 fact_time_entry、非分區表 fact_time_entry_np，結構相同
• 各插入 1,500 萬筆資料

測試結果

分區切換：毫秒級歸檔與清除

核心語法

-- 將來源表分區切換至目標表分區（元資料操作，零資料移動）
ALTER TABLE source_table SWITCH PARTITION source_partition_num 
    TO target_table PARTITION target_partition_num

前置條件

• 來源/目標表結構完全相同（欄位、索引、約束、分區配置）
• 目標分區必須為空
• 資料值必須滿足目標分區邊界條件
• 雙方分區位於相同檔案群組

實戰：歸檔 2007 年資料至歷史表

-- 1. 建立歷史表，相同結構、相同分區配置，主鍵起始值避免衝突
CREATE TABLE fact_time_entry_history (...) ON ps_DateRange(EntryDate)

-- 2. 將 2007 年資料（分區 8）載入歷史表（可透過 ETL、INSERT、BCP）
-- ...載入完成...

-- 3. 毫秒級切換：將歷史表分區 8 切換至正式表分區 8
ALTER TABLE fact_time_entry_history SWITCH PARTITION 8 
    TO fact_time_entry PARTITION 8
-- 耗時：3 毫秒，正式表瞬間新增 500 萬筆，零鎖定、零阻塞查詢

實戰：清除 2004 年資料

-- 1. 確保歷史表分區 2 為空
-- 2. 將正式表分區 2 切換至歷史表分區 2
ALTER TABLE fact_time_entry SWITCH PARTITION 2 
    TO fact_time_entry_history PARTITION 2
-- 3. 截斷/刪除歷史表分區 2，或移至其他伺服器
TRUNCATE TABLE fact_time_entry_history  -- 或 DROP PARTITION 2（需重建配置）

查詢分區資訊

-- 查看各分區資料筆數
SELECT 
    $PARTITION.pf_DateRange(EntryDate) AS PartitionNumber,
    COUNT(*) AS RowCount
FROM fact_time_entry
GROUP BY $PARTITION.pf_DateRange(EntryDate)
ORDER BY PartitionNumber

分區索引維護策略

適用場景與限制

結語

SQL Server 2005 分區技術將「手工分表+檢視表」升級為引擎原生支援，核心價值在於：

1. 查詢效能： 自動分區剪枝、平行掃描
2. 維護效能： 單一分區重建/重組/更新統計
3. 資料生命週期： SWITCH PARTITION 毫秒級歸檔/清除，零停機

掌握分區函數設計、檔案群組規劃、對齊索引原則與切換操作，是處理 TB 級資料倉庫的必備技能。