用GPT 來學習架構場景-SQL SERVER 分區處理

2026-04-16

用GPT 來學習架構場景

SQL SERVER分區處理，是 資料表水平切分，在 SQL Server 裡是用來處理「大資料量」的核心技術（幾百萬～幾億筆）。

分區是什麼？

把一張大表「切成多塊」，但邏輯上還是同一張表

例如 Orders：

訂單日期	分區
2023	Partition 1
2024	Partition 2
2025	Partition 3

為什麼要用分區？

1️⃣ 查詢效能（只掃一部分）

WHERE OrderDate >= '2025-01-01'

👉 只掃 2025 分區（Partition Elimination）

2️⃣ 歷史資料管理（超重要）

👉 可以直接「切掉舊資料」

ALTER TABLE Orders SWITCH PARTITION 1 TO Orders_Archive;

3️⃣ 維護快很多

rebuild index（只重建某分區）
backup（只備份某檔案群組）

🔧 SQL Server 分區實作（4步驟）

✅ Step 1：建立 Partition Function（切資料規則）

CREATE PARTITION FUNCTION pf_OrderDate (DATE)
AS RANGE RIGHT FOR VALUES 
(
    '2024-01-01',
    '2025-01-01'
);

👉 會變成：

< 2024 → P1
2024 → P2
2025 → P3

✅ Step 2：建立 Partition Scheme（分區放哪）

CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
ALL TO ([PRIMARY]); -- 可放不同檔案群組

👉 進階：
可以每個分區放不同磁碟（高階用法）

✅ Step 3：建立分區表

CREATE TABLE Orders (
    Id INT,
    OrderDate DATE,
    Amount DECIMAL(10,2)
)
ON ps_OrderDate (OrderDate);

👉 關鍵：
ON ps_OrderDate (OrderDate) ← 分區鍵

✅ Step 4：建立分區索引（很重要‼️）

CREATE CLUSTERED INDEX IX_Orders_OrderDate
ON Orders(OrderDate)
ON ps_OrderDate(OrderDate);

👉 沒有這個 = 分區效果大打折

🔥 查詢效果（重點）

SELECT *
FROM Orders
WHERE OrderDate = '2025-03-01'

👉 SQL Server 只掃一個 partition
👉 這就是 Partition Elimination

⚠️ 實務大坑（一定要知道）

❌ 1. 查詢沒用到分區鍵

WHERE Amount > 1000

👉 會掃全部分區 → 沒意義

❌ 2. 分區鍵選錯

👉 常見錯誤：

用 GUID
用低選擇性欄位

✔ 正確：

日期（最常見）
Identity range

❌ 3. 太多分區

👉 不要切成幾千個 partition
建議：

月分區（常見）
或年分區

🚀 進階（你這種工程師會用到）

🎯 Sliding Window（滑動視窗）

👉 每月自動：

加新分區
刪舊分區

ALTER PARTITION FUNCTION pf_OrderDate()
SPLIT RANGE ('2026-01-01');

🎯 分區切換（超快刪資料）

ALTER TABLE Orders
SWITCH PARTITION 1 TO Orders_Archive;

👉 幾乎瞬間完成（metadata 操作）

📊 分區 vs 分頁（不要搞混）

	分頁	分區
用途	UI 顯示	大資料管理
SQL	OFFSET	PARTITION
效能	小影響	大影響
資料量	幾千筆	幾億筆

快速產生 10 萬筆測試資料

✅ 方法1：用系統表快速灌資料（推薦）

SET NOCOUNT ON;

INSERT INTO Orders (Id, OrderDate, Amount)
SELECT 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Id,
    DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 1000, '2023-01-01') AS OrderDate,
    CAST(RAND(CHECKSUM(NEWID())) * 10000 AS DECIMAL(10,2)) AS Amount
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;

📌 說明

sys.all_objects：系統表（用來產生大量資料）
CROSS JOIN：放大資料量（幾萬 → 幾十萬）
NEWID()：隨機
DATEADD：產生 2023~2025 隨機日期

⚡ 如果你只要 10 萬筆（控制筆數）

INSERT INTO Orders (Id, OrderDate, Amount)
SELECT TOP 100000
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Id,
    DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 1000, '2023-01-01'),
    CAST(RAND(CHECKSUM(NEWID())) * 10000 AS DECIMAL(10,2))
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;

🧪 三、驗證分區有沒有生效（重點‼️）

✅ 查每個 Partition 筆數

SELECT 
    $PARTITION.pf_OrderDate(OrderDate) AS PartitionNumber,
    COUNT(*) AS RowCount
FROM Orders
GROUP BY $PARTITION.pf_OrderDate(OrderDate)
ORDER BY PartitionNumber;

👉 你會看到：

Partition 1：幾萬筆
Partition 2：幾萬筆
Partition 3：幾萬筆

✅ 查詢只掃某分區（效能測試）

SET STATISTICS IO ON;

SELECT *
FROM Orders
WHERE OrderDate >= '2025-01-01';

👉 觀察：

logical reads 會變少
代表 Partition Elimination 生效

🔥 四、模擬真實場景（更進階）

🎯 每年固定資料（更準）

-- 2023
INSERT INTO Orders
SELECT TOP 30000
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
    DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 365, '2023-01-01'),
    RAND(CHECKSUM(NEWID())) * 10000
FROM sys.all_objects;

-- 2024
INSERT INTO Orders
SELECT TOP 30000
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 30000,
    DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 365, '2024-01-01'),
    RAND(CHECKSUM(NEWID())) * 10000
FROM sys.all_objects;

-- 2025
INSERT INTO Orders
SELECT TOP 30000
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 60000,
    DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 365, '2025-01-01'),
    RAND(CHECKSUM(NEWID())) * 10000
FROM sys.all_objects;

👉 這樣可以：

明確看到每個 partition 的資料分布

SQL SERVER分區有什麼限制?

因為 SQL Server 分區不是萬能，很多專案踩雷都是因為「不知道它的限制」。

我直接講實務會痛的限制，不是官方文件那種表面條列👇

🚨 一、架構層級限制（設計時就卡）

❗ 1. 一張表只能有「一個分區鍵」

👉 你不能這樣：想用 OrderDate + CustomerId 分區 ❌

👉 只能：OrderDate ✔（最常見）

📌 影響：

查詢如果不是用這個欄位 → 分區幾乎沒用

❗ 2. 分區鍵一旦選錯 → 很難改

👉 沒有簡單 ALTER 可以改

你只能：

建新表
搬資料
切換

👉 大資料量 = 災難 😅

❗ 3. Partition Function 有上限（15,000 個）

👉 不能無限切

📌 但實務上：

月分區（10年）= 120 ✔
日分區（10年）= 3650 ✔
通常不會碰到上限

⚠️ 二、查詢與效能限制（最常被誤會）

❗ 4. 沒用到分區鍵 = 全掃描

WHERE Amount > 1000

👉 結果：

所有 partition 都掃

📌 很多人以為：
👉 有分區就會快 ❌（錯）

❗ 5. Partition Elimination 有條件

👉 必須：

✔ 條件直接作用在分區鍵
✔ 不能包 function

-- ❌ 失效
WHERE YEAR(OrderDate) = 2025

-- ✅ 正確
WHERE OrderDate >= '2025-01-01'

❗ 6. OFFSET 分頁 + 分區 ≠ 自動變快

👉 如果排序不是分區鍵：

ORDER BY Amount

👉 還是會慢

🔧 三、索引限制（很關鍵）

❗ 7. Clustered Index 必須包含分區鍵

👉 不然 SQL Server 會報錯或強制調整

-- 正確
CREATE CLUSTERED INDEX IX ON Orders(OrderDate, Id);

❗ 8. Unique Index 必須包含分區鍵

-- ❌ 錯誤
UNIQUE (Id)

-- ✅ 正確
UNIQUE (OrderDate, Id)

📌 影響：
👉 設計主鍵會被限制

❗ 9. Nonclustered Index 預設不是分區對齊

👉 你要手動指定：

ON ps_OrderDate(OrderDate)

不然：
👉 查詢會跨 partition

🔄 四、資料操作限制

❗ 10. SWITCH 操作限制很多（常踩雷）

ALTER TABLE Orders SWITCH PARTITION 1 TO Orders_Archive;

👉 必須：

結構完全一樣
index 完全一樣
constraint 一樣

👉 不然直接 fail

❗ 11. 外鍵（FK）很麻煩

👉 分區表 + FK：

會限制 SWITCH
有時候根本不能用

📌 很多高效系統：
👉 直接不用 FK（用應用層控）

❗ 12. 更新分區鍵 = 搬資料

UPDATE Orders
SET OrderDate = '2025-01-01'

👉 等於：

delete + insert

👉 成本很高

💾 五、維運與成本限制

❗ 13. 管理複雜度高

你要多管理：

Partition Function
Partition Scheme
Filegroup（進階）
Sliding Window

👉 比一般表難很多

❗ 14. 備份 / 還原變複雜

👉 如果用 filegroup：

要部分備份策略
還原流程更複雜

❗ 15. 開發人員容易誤用