SQL Server 資料表分割

目錄

• 什麼是資料表分割
• 核心元件
• 對齊索引
• 分割切換 SWITCH（深入）
• 滑動視窗維護
• 查詢面的好處：分割消除
• 設計取捨與陷阱
• 常見問題與總結

什麼是資料表分割

資料表分割（table partitioning） 是把一張邏輯上的大表，依某個欄位（分割鍵）的值範圍，水平切成多個實體分割（partition）。對應用程式而言它仍是一張表，查詢、寫入語法不變；但在儲存與維護層面，每個分割可以獨立放置、獨立操作。

邏輯上：一張 Orders 表

實體上：    
            ┌─ 分割 1：2025 Q1 ─┐
            ├─ 分割 2：2025 Q2 ─┤
   Orders ──┼─ 分割 3：2025 Q3 ─┤
            ├─ 分割 4：2025 Q4 ─┤
            └─ 分割 5：2026+   ─┘

為什麼要分割

分割主要解決「大表的可管理性與載入/汰除」，它本身不等於查詢一定變快——查詢的好處要靠分割消除，而那要分割鍵與查詢條件對得上。把分割當成純效能銀彈是常見誤解。

核心元件

SQL Server 的分割由三個東西組合而成：partition function（定義邊界）＋ partition scheme（把分割對應到檔案群組）＋ 把 scheme 套到資料表/索引。

1. Partition function：定義邊界

決定「用什麼型別、在哪些邊界值切」。

CREATE PARTITION FUNCTION pf_OrderDate (date)
AS RANGE RIGHT
FOR VALUES ('2025-04-01', '2025-07-01', '2025-10-01', '2026-01-01');

• N 個邊界值 → N + 1 個分割。上例 4 個邊界 → 5 個分割。
• RANGE RIGHT vs RANGE LEFT：邊界值本身歸哪一邊。
  • RANGE RIGHT：邊界值是右側分割的第一個值（如 2025-07-01 屬於 Q3）。日期/時間最常用這個。
  • RANGE LEFT：邊界值是左側分割的最後一個值。

RANGE RIGHT，邊界 2025-07-01：
  分割: [..., 2025-06-30] | [2025-07-01, ...]
                            ↑ 邊界值在右邊

2. Partition scheme：對應到檔案群組

決定「每個分割放在哪個檔案群組」。可分散到多個檔案群組（利於 I/O 與分檔備份），也可全放同一個：

-- 各分割分到不同檔案群組
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
TO (fg2025q1, fg2025q2, fg2025q3, fg2025q4, fg2026);

-- 或全部放 PRIMARY
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate ALL TO ([PRIMARY]);

3. 套到資料表

建表時指定用哪個 scheme、哪個欄位當分割鍵：

CREATE TABLE dbo.Orders (
    OrderId   bigint       NOT NULL,
    OrderDate date         NOT NULL,
    Amount    decimal(18,2) NOT NULL
) ON ps_OrderDate(OrderDate);   -- ← 分割鍵

元件關係

┌───────────────────────────────────────────┐
│ partition function  →  邊界值 + RANGE 方向  │
│            ↓ 被引用                        │
│ partition scheme    →  分割 ↔ 檔案群組對應  │
│            ↓ 套用於                        │
│ 資料表 / 索引       →  ON scheme(分割鍵欄位) │
└───────────────────────────────────────────┘

對齊索引

對齊索引（aligned index） 指「索引和它所屬的資料表用同一個 partition scheme、同一個分割鍵分割」。預設在分割表上建的索引會繼承表的 scheme，因而對齊。

為什麼重要

• SWITCH 的硬性前提：要做分割切換，表上所有索引都必須與表對齊（見下節）。
• 維護獨立性：對齊後，索引也跟著一個個分割，可逐分割重建。

對齊 vs 非對齊

重要限制：分割表上的唯一索引（含主鍵）必須把分割鍵包含進索引鍵，否則無法對齊。設計主鍵時要把這點算進去（例如主鍵改成 (OrderId, OrderDate)）。

分割切換 SWITCH（深入）

ALTER TABLE ... SWITCH 是分割最有威力的操作：它是 metadata-only——不搬動任何資料列，只是把「這些頁面屬於哪張表/哪個分割」的中繼資料指標翻過去，因此耗時與資料量無關，幾百萬列也近乎瞬間。

這也是大量載入的上線手段（搭配 bcp 載入暫存表，詳見 SQL Server 大量寫入與 TDS）。

兩個方向

-- 切入：把 staging（非分割表）整批換進 target 的第 n 個分割
ALTER TABLE dbo.Orders_Staging SWITCH TO dbo.Orders PARTITION 5;

-- 切出：把 target 的第 n 個分割整批換到 staging
ALTER TABLE dbo.Orders SWITCH PARTITION 1 TO dbo.Orders_Archive;

成立條件（缺一不可）

1. 同一檔案群組：來源與目標（含其索引、LOB 欄位）必須在相同檔案群組。
2. 結構完全相同：欄位名稱、型別、長度、定序、精度、scale、可空性、主鍵都要一致，順序也要一致。
3. 索引對齊：兩邊的索引都必須與各自的表對齊。
4. CHECK 約束界定範圍：切入時，staging 表必須有一個 CHECK 約束，保證它的資料都落在目標分割的邊界範圍內——SQL Server 靠這個約束「信任」資料歸屬，不必掃描每一列，這正是 SWITCH 能維持 metadata-only 的關鍵。目標表上的 CHECK 約束也必須在來源表上存在（精確相符或為其子集）。
5. 目標分割須為空（切入時）。

-- staging 表上界定範圍的 CHECK（對應第 5 個分割：2026-01-01 之後）
ALTER TABLE dbo.Orders_Staging
ADD CONSTRAINT ck_range CHECK (OrderDate >= '2026-01-01');

為什麼這招快，可以一句話記住：把「重的資料操作」換成「輕的中繼資料操作」。資料早就在 staging 表的頁面上，SWITCH 只改一筆歸屬中繼資料——和 TRUNCATE 比 DELETE 快、RENAME 比 copy 快，是同一個道理。那個 CHECK 約束則把「驗證資料歸屬」從執行期掃描前移成宣告期保證。

常見錯誤

切換失敗常見於 錯誤 4982 / 4972（CHECK 約束不滿足或缺失）、索引未對齊、檔案群組不符。逐項對照上面五個條件即可定位。

滑動視窗維護

「滑動視窗（sliding window）」是分割最經典的應用：固定保留近 N 期資料，每期載入最新、汰除最舊。組合 SWITCH + SPLIT + MERGE 完成。

操作三件套

一輪滑動的流程

汰除最舊：
  1. ALTER TABLE Orders SWITCH PARTITION 1 TO Archive   ← 最舊分割瞬間移出
  2. TRUNCATE / DROP Archive                            ← 瞬間清除（不像 DELETE 逐列記錄）
  3. ALTER PARTITION FUNCTION pf() MERGE RANGE(舊邊界)   ← 移除空邊界

載入最新：
  4. ALTER PARTITION SCHEME ps NEXT USED 新檔案群組
  5. ALTER PARTITION FUNCTION pf() SPLIT RANGE(新邊界)   ← 開出新分割
  6. bcp 載入 staging → SWITCH 進新分割                 ← 新資料瞬間上線

汰除用 SWITCH OUT + TRUNCATE，比 DELETE 整批刪快太多——後者要逐列寫交易記錄、可能撐爆 log，前者是 metadata-only。

注意：對非空分割做 SPLIT/MERGE 會觸發資料搬移，反而很貴。實務上維持「兩端各留一個空分割」當緩衝，讓 SPLIT/MERGE 永遠作用在空分割上。

查詢面的好處：分割消除

分割消除（partition elimination） 是查詢層的主要效益：當查詢條件落在分割鍵上時，最佳化器能判斷哪些分割不可能含符合的列，直接跳過、只掃相關分割。

-- 只掃 2026 Q1 那個分割，其餘分割完全不碰
SELECT SUM(Amount)
FROM dbo.Orders
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2026-04-01';

執行計畫裡會看到 Actual Partition Count 之類資訊，顯示實際掃描的分割數。

用 $PARTITION 檢查歸屬

可以用內建函式查某個值會落在第幾個分割，驗證設計：

SELECT $PARTITION.pf_OrderDate('2026-02-15');   -- 回傳分割編號

-- 看各分割的列數分布
SELECT partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('dbo.Orders') AND index_id IN (0,1);

消除失效的常見原因

• 查詢條件不在分割鍵上（例如分割鍵是 OrderDate，卻只用 OrderId 過濾）→ 無法消除，全分割掃描。
• 對分割鍵套用函式（如 WHERE YEAR(OrderDate) = 2026）可能使最佳化器無法消除 → 改寫成範圍條件。

設計取捨與陷阱

分割鍵的選擇

分割鍵決定一切：它要同時滿足「查詢最常用的過濾欄位」（才有分割消除）與「載入/汰除的單位」（才好 SWITCH）。時間欄位（日期）最常見，因為兩者通常一致。

常見陷阱

常見問題與總結

問題 1：分割表查詢一定比較快嗎？

不一定。分割的主效益是可管理性與載入/汰除；查詢變快只在「條件落在分割鍵上、能分割消除」時才成立。條件對不上分割鍵，反而可能因跨分割掃描而沒好處。

問題 2：什麼時候該用分割？

資料量大（通常數千萬列以上）、有明確的時間或範圍維度、需要定期大量載入/汰除（滑動視窗）、或需要逐區段維護時。小表用分割是過度設計。

問題 3：分割和 bulk load 怎麼搭？

把 bulk load 灌進結構相同的 staging 表（離線、最小記錄），再 SWITCH 進目標分割，達成「快速載入 + 即時上線、不擋線上讀取」。完整載入細節見 SQL Server 大量寫入與 TDS。

核心要點

分割 = partition function（邊界）
     + partition scheme（檔案群組對應）
     + 套到表/索引（ON scheme(分割鍵)）

威力來源：
  ├─ SWITCH    → metadata-only，瞬間上線/汰除
  ├─ 分割消除  → 查詢跳過無關分割
  └─ 逐分割維護 → 重建/備份不影響其他分割

快速參考

建立日期：2026-06-30