SQL Server 分區表資料操作實戰：新增、查詢、統計與跨分區更新

前言

接續上篇「分區表建立」，本文實戰演示分區表的 **資料操作（CRUD）**、**分區判斷函數 $PARTITION**、**各分區統計**，以及 **跨分區更新的自動資料移動** 行為，驗證「邏輯一張表、物理多檔案」的透明特性。

一、測試資料準備

-- 沿用上篇分區表 dbo.Sale (Id, Name, SaleTime) 
-- 分區函數 pf_SaleDate: RANGE RIGHT '20100101','20110101','20120101','20130101'
-- 對應 5 個分區：<2010 | 2010 | 2011 | 2012 | ≥2013

INSERT INTO dbo.Sale (Name, SaleTime) VALUES
(N'張三','2009-01-01'), (N'李四','2009-02-01'), (N'王五','2009-03-01'),  -- 分區 1
(N'錢六','2010-04-01'), (N'趙七','2010-05-01'),                          -- 分區 2
(N'張三','2011-06-01'), (N'李四','2011-07-01'), (N'王五','2011-08-01'),  -- 分區 3
(N'錢六','2012-09-01'), (N'趙七','2012-10-01'), (N'張三','2012-11-01'),  -- 分區 4
(N'李四','2013-12-01'), (N'王五','2014-12-01');                          -- 分區 5

插入語法與普通表**完全相同**，開發者無需知道實體分區分佈。

二、查詢驗證：邏輯上仍是單一表

SELECT * FROM dbo.Sale;
-- 正常回傳 13 筆資料，不感知分區存在

三、$PARTITION 函數：判斷資料所在分區

3.1 語法

$PARTITION.()

• 回傳 INT 分區編號（1-based）。
• expression 型別必須與分區函數定義一致（本例為 DATETIME）。

3.2 判斷特定日期落在哪個分區

SELECT $PARTITION.pf_SaleDate('2010-10-01');  -- 回傳 2

3.3 查詢特定分區資料

SELECT * FROM dbo.Sale WHERE $PARTITION.pf_SaleDate(SaleTime) = 2;
-- 僅回傳分區 2 的 2 筆資料

3.4 統計各分區筆數

SELECT 
    $PARTITION.pf_SaleDate(SaleTime) AS PartitionNo,
    COUNT(*) AS RowCount
FROM dbo.Sale
GROUP BY $PARTITION.pf_SaleDate(SaleTime);

結果

PartitionNo  RowCount
1            3
2            2
3            3
4            3
5            2

四、跨分區 UPDATE：自動資料移動

-- 1. 變更前統計
SELECT $PARTITION.pf_SaleDate(SaleTime) AS PNo, COUNT(*) AS Cnt
FROM dbo.Sale GROUP BY $PARTITION.pf_SaleDate(SaleTime);

-- 2. 將 Id=1 (原 2009-01-01, 分區1) 改為 2019-01-01 (應進分區5)
UPDATE dbo.Sale SET SaleTime = '2019-01-01' WHERE Id = 1;

-- 3. 變更後統計
SELECT $PARTITION.pf_SaleDate(SaleTime) AS PNo, COUNT(*) AS Cnt
FROM dbo.Sale GROUP BY $PARTITION.pf_SaleDate(SaleTime);

結果對比

-- 變更前
PNo  Cnt
1    3
2    2
3    3
4    3
5    2

-- 變更後
PNo  Cnt
1    2   -- 少 1 筆
2    2
3    3
4    3
5    3   -- 多 1 筆

**完全透明**：開發者只需 UPDATE 邏輯欄位，SQL Server 自動處理跨分區資料移動（內部為 DELETE + INSERT），無須手工干預。

五、分區表刪除與截斷

• DELETE FROM dbo.Sale WHERE $PARTITION.pf_SaleDate(SaleTime) = 1; —— 可刪除特定分區資料，支援 OUTPUT、觸發器、記錄逐列寫入。
• TRUNCATE TABLE dbo.Sale; —— **不支援分區層級截斷**，會清空全表。若需快速清空單一分區，請用 SWITCH PARTITION ... TO staging_table 再 TRUNCATE staging_table。

六、分區切換進階：秒級歸檔/載入

-- 1. 建立結構完全相同的暫存表（含相同檔案群組、索引、檢查約束）
CREATE TABLE dbo.Sale_Staging (...) ON ps_SaleDate(SaleTime);
-- 2. 將分區 1 (2009 年資料) 切換出來
ALTER TABLE dbo.Sale SWITCH PARTITION 1 TO dbo.Sale_Staging;
-- 3. 現在 Sale_Staging 擁有該分區資料，Sale 分區 1 瞬間變空
-- 4. 可對 Sale_Staging 壓縮、移動、匯出、備份，不影響主表線上服務

• 切換為**中繼資料操作**，不移動資料頁，**毫秒級完成**，不產生交易記錄。
• 前提條件：來源/目標表結構、索引、檢查約束、檔案群組**完全一致**。

七、效能調優建議

1. 查詢務必帶分區欄位條件：啟用分區修剪，避免全表掃描。
2. 索引對齊：所有索引建議建立在相同分區方案上，避免非對齊索引導致跨分區查詢效能下降。
3. 統計資料：分區層級統計資料自動維護；大量資料變更後可 UPDATE STATISTICS ... WITH RESAMPLE。
4. 壓縮策略：冷資料分區啟用 DATA_COMPRESSION = PAGE，節省空間並減少 I/O。