SQL Server 2005 資料表分區完整實作範例：分區函數、配置、驗證

分區表核心優勢

• 大量資料載入/清除：SWITCH 分區瞬間完成 (中繼資料操作)。
• 查詢裁剪：僅掃描相關分區，縮小索引範圍。
• 維護彈性：單一分區重建索引/更新統計，不影響整表。
• 儲存分級：熱資料置 SSD，冷資料置 HDD/壓縮儲存。

完整建立腳本 (按月份分區)

-- 1. 建立測試資料庫
CREATE DATABASE dbPartitionTest; GO
USE dbPartitionTest; GO

-- 2. 新增檔案群組 (每分區一群組，利於檔案群組備份/還原)
ALTER DATABASE dbPartitionTest ADD FILEGROUP P200801;
ALTER DATABASE dbPartitionTest ADD FILEGROUP P200802;
ALTER DATABASE dbPartitionTest ADD FILEGROUP P200803;
GO

-- 3. 分區函數：定義邊界值 (RANGE LEFT 表示邊界值屬於左側分區)
CREATE PARTITION FUNCTION pf_Monthly (DATETIME)
AS RANGE LEFT FOR VALUES (
    '20080131 23:59:59.997',  -- 分區 1: <= 1月31日
    '20080229 23:59:59.997',  -- 分區 2: 2月1日 ~ 2月29日
    '20080331 23:59:59.997'   -- 分區 3: 3月1日 ~ 3月31日
); -- 分區 4 (PRIMARY): > 3月31日
GO

-- 4. 分區配置：將分區對應到檔案群組
CREATE PARTITION SCHEME ps_Monthly AS PARTITION pf_Monthly 
TO (P200801, P200802, P200803, [PRIMARY]);
GO

-- 5. 建立檔案 (每群組一檔案，生產環境建議多檔案分散 I/O)
ALTER DATABASE dbPartitionTest ADD FILE (NAME=N'P200801', FILENAME=N'C:\Data\P200801.ndf', SIZE=100MB, FILEGROWTH=50MB) TO FILEGROUP P200801;
ALTER DATABASE dbPartitionTest ADD FILE (NAME=N'P200802', FILENAME=N'C:\Data\P200802.ndf', SIZE=100MB, FILEGROWTH=50MB) TO FILEGROUP P200802;
ALTER DATABASE dbPartitionTest ADD FILE (NAME=N'P200803', FILENAME=N'C:\Data\P200803.ndf', SIZE=100MB, FILEGROWTH=50MB) TO FILEGROUP P200803;
GO

-- 6. 建立分區表
CREATE TABLE dbo.t_part (
    Name VARCHAR(100) DEFAULT NEWID(),
    [Date] DATETIME NOT NULL
) ON ps_Monthly ([Date]);
GO

-- 7. 測試資料：每天一筆，跨越 2007-12 到 2008-04
DECLARE @dt DATETIME = '2007-12-31';
WHILE @dt <= '2008-04-01'
BEGIN
    INSERT INTO dbo.t_part ([Date]) VALUES (@dt);
    SET @dt = DATEADD(DAY, 1, @dt);
END
GO

-- 8. 驗證資料分布
SELECT $PARTITION.pf_Monthly([Date]) AS PartitionNumber, 
       MIN([Date]) AS MinDate, MAX([Date]) AS MaxDate, COUNT(*) AS RowCount
FROM dbo.t_part
GROUP BY $PARTITION.pf_Monthly([Date])
ORDER BY PartitionNumber;

-- 9. 檢視檔案群組使用狀況
EXEC sp_helpfile;

預期分區分布結果

進階操作：分區切換 (SWITCH) 實現秒級歸檔

-- 建立同結構的暫存表 (同檔案群組)
CREATE TABLE dbo.t_part_Archive (Name VARCHAR(100), [Date] DATETIME NOT NULL) ON P200801;

-- 將分區 1 瞬間切換到歸檔表 (無資料移動)
ALTER TABLE dbo.t_part SWITCH PARTITION 1 TO dbo.t_part_Archive;

-- 歸檔表可移至唯讀檔案群組、壓縮、異地備份
-- 原表分區 1 瞬間變空，查詢自動略過

分區索引對齊原則

• 索引**必須**與基底表使用相同分區函數/配置 (對齊)，才能支援 SWITCH。
• 非對齊索引 (不同分區鍵/無分區) 會阻礙分區切換操作。