萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 分區表資料操作實戰:新增、查詢、分區編號、跨分區更新

前言

接續上篇建立分區表 Sale(依 SaleTime 分五區),本文示範資料操作如何透明進行,以及如何檢視資料實際分佈。

一、插入測試資料

INSERT Sale ([Name], [SaleTime]) VALUES 
(N'張三', '2009-1-1'), (N'李四', '2009-2-1'), (N'王五', '2009-3-1'),  -- 分區 1
(N'錢六', '2010-4-1'), (N'趙七', '2010-5-1'),                        -- 分區 2
(N'張三', '2011-6-1'), (N'李四', '2011-7-1'), (N'王五', '2011-8-1'),  -- 分區 3
(N'錢六', '2012-9-1'), (N'趙七', '2012-10-1'), (N'張三', '2012-11-1'), -- 分區 4
(N'李四', '2013-12-1'), (N'王五', '2014-12-1');                      -- 分區 5

應用程式只需對邏輯表 Sale 插入資料,SQL Server 自動依 SaleTime 路由至正確分區。

二、查詢全表資料

SELECT * FROM Sale;

邏輯視角下,資料如同單一表呈現,無需關心物理分佈。

三、$PARTITION 函數:查看資料所在分區

語法

$PARTITION.partition_function_name(expression)

查詢特定日期落在哪個分區

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

依分區編號篩選資料

SELECT * FROM Sale WHERE $PARTITION.partfunSale(SaleTime) = 1;  -- 分區 1
SELECT * FROM Sale WHERE $PARTITION.partfunSale(SaleTime) = 2;  -- 分區 2
-- ... 以此類推

統計各分區筆數

SELECT 
    $PARTITION.partfunSale(SaleTime) AS 分區編號,
    COUNT(Id) AS 記錄數
FROM Sale
GROUP BY $PARTITION.partfunSale(SaleTime);

四、跨分區更新:自動資料移動

-- 更新前統計
SELECT $PARTITION.partfunSale(SaleTime) AS 分區編號, COUNT(Id) AS 記錄數
FROM Sale GROUP BY $PARTITION.partfunSale(SaleTime);

-- 將 Id=1 的記錄從 2009 年改為 2019 年(跨越分區 1 → 5)
UPDATE Sale SET SaleTime = '2019-1-1' WHERE Id = 1;

-- 更新後統計
SELECT $PARTITION.partfunSale(SaleTime) AS 分區編號, COUNT(Id) AS 記錄數
FROM Sale GROUP BY $PARTITION.partfunSale(SaleTime);

SQL Server 自動將該列從分區 1 移動至分區 5,開發者無需干預,完全透明。

五、效能與維護提示

  • 分區裁剪:查詢含分區欄位條件時,最佳化器自動略過無關分區
  • 切換分區:ALTER TABLE ... SWITCH PARTITION 可秒級歸檔/載入資料
  • 索引對齊:建議索引與表對齊相同分區配置,避免跨分區索引維護成本
  • 統計資料:大表分區需啟用 AUTO_UPDATE_STATISTICS,必要時手動 UPDATE STATISTICS 指定分區

總結

分區表對應用透明,$PARTITION 函數提供可見性與診斷能力。跨分區更新自動處理資料移動,維護操作(如歸檔)配合 SWITCH 可達到元資料級的極速操作。

copyright © 萬盛學電腦網 all rights reserved