SQL Server 2005 資料表分區實作完整教學

為什麼需要分區？

• Scale-out 替代 Scale-up：透過水平擴展多伺服器協作，而非單機堆硬體，降低成本。
• 避免昂貴硬體投資：大量資料表分區後，無需採購高階伺服器即可維持效能。
• 管理粒度適中：將 GB 級大表拆分為數百 MB 的分區，備份、索引重建、還原等維護操作更輕量、快速。
• 消除效能瓶頸、降低維護成本：查詢只掃描相關分區，鎖定範圍縮小，並發度提升。

適用場景

1. 大資料量表：管理困難、索引建立/重建逾時、備份還原耗時、結構變更鎖升級。
2. 不同存取模式：OLTP（頻繁增刪改）與 DSS（大量查詢分析）共存時，分區可隔離負載。

分區策略

垂直分區

將寬表拆分為多個窄表，每表包含較少欄位。包含正規化（移除冗餘欄位至關聯表）與行拆分（按邏輯群組欄位）。需謹慎使用，跨分區聯結可能影響效能。

水平分區

依資料值範圍（如日期、編號）將資料列分散至多個結構相同的分區。查詢可透過分區消除只存取必要分區，避免大量 UNION 影響效能。

垂直分區實例

某表 160 萬列、47 欄位、每列 4600 Bytes，受 8060 Bytes 頁面限制導致每列佔一頁，總大小 12GB。拆分為三表後總大小降為 7GB，節省 5GB 空間並提升 I/O 效能。

關鍵優化點：
1. 優化列大小，讓更多列放入單一頁面。
2. 將常用欄位群組同表，減少跨表聯結。
3. 隔離唯讀與 OLTP 欄位，降低鎖衝突。

SQL Server 2005 分區實作

核心概念

• 分區函數：定義依據欄位值將列對應到哪個分區（最多 1000 分區）。
• 分區配置：將分區對應到檔案群組（一個函數可供多個配置使用）。
• 建立分區表/索引：指定 ON partition_scheme(partition_column)。

建立腳本範例

-- 1. 分區函數（RANGE RIGHT：邊界值屬於右側分區）
CREATE PARTITION FUNCTION myRangePF1 (DATETIME)
AS RANGE RIGHT FOR VALUES ('2007-12-21','2007-12-22','2007-12-23','2007-12-24','2007-12-25','2007-12-26')

-- 2. 分區配置
CREATE PARTITION SCHEME mySchemePS1
AS PARTITION myRangePF1
TO ([PRIMARY], [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7])

-- 3. 新增分區
ALTER PARTITION SCHEME mySchemePS1 NEXT USED [FG8]
ALTER PARTITION FUNCTION myRangePF1() SPLIT RANGE ('2007-12-27')

-- 4. 合併分區
ALTER PARTITION FUNCTION myRangePF1() MERGE RANGE ('2007-12-21')

-- 5. 建立分區表
CREATE TABLE PartitionTable (col1 INT, col2 CHAR(10))
ON mySchemePS1(col1)

-- 6. 建立分區索引（依分區欄位建立可省略 ON 子句）
CREATE INDEX ix_Col2 ON PartitionTable(col2) ON mySchemePS1(col1)

-- 7. 查詢分區資訊
SELECT $PARTITION.myRangePF1(col1) AS partition_id, COUNT(*)
FROM PartitionTable
GROUP BY $PARTITION.myRangePF1(col1)

實戰案例：AdventureWorks 交易歷史表

• TransactionHistory：12 個月分區（當前年度）。
• TransactionHistoryArchive：2 個分區（歷史資料）。
• 每月透過 ALTER TABLE ... SWITCH PARTITION 瞬間將最舊月份資料移至歸檔表，無實際資料移動，極快。

條碼物流系統應用

輪胎狀態表依製造年份與批次分區（08 年前按年、08 年後按 10 週），解決原本查詢逾時、用戶端當機問題，顯著提升現場與 Web 系統效能。