您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 普通表轉分區表完整實戰腳本與關鍵步驟解析

編輯：關於SqlServer

分區表將大表按範圍劃分到多個文件組，實現「分區消除」加速查詢、分區切換秒級載入/歸檔、獨立維護索引/統計。本文演示將 Sale 表按 SaleTime 年度分區的完整遷移流程。

遷移前準備：原表結構

CREATE TABLE dbo.Sale (
    Id INT IDENTITY(1,1) NOT NULL,
    Name VARCHAR(16) NOT NULL,
    SaleTime DATETIME NOT NULL,
    CONSTRAINT PK_Sale PRIMARY KEY CLUSTERED (Id ASC)
) ON [PRIMARY];

-- 插入 2009-2014 跨年測試數據
INSERT INTO dbo.Sale (Name, SaleTime) VALUES
('張三','2009-1-1'),('李四','2009-2-1'),('王五','2009-3-1'),
('錢六','2010-4-1'),('趙七','2010-5-1'),
('張三','2011-6-1'),('李四','2011-7-1'),('王五','2011-8-1'),
('錢六','2012-9-1'),('趙七','2012-10-1'),
('張三','2012-11-1'),('李四','2013-12-1'),('王五','2014-12-1');

核心遷移四步驟

1. 移除叢集主鍵（分區表要求叢集索引與分區列一致）

ALTER TABLE dbo.Sale DROP CONSTRAINT PK_Sale;
GO
-- 重建非叢集主鍵（可選，業務需唯一鍵時）
ALTER TABLE dbo.Sale ADD CONSTRAINT PK_Sale PRIMARY KEY NONCLUSTERED (Id ASC) ON [PRIMARY];
GO

2. 建立分區函數：定義邊界值

CREATE PARTITION FUNCTION pf_Sale (DATETIME)
AS RANGE LEFT FOR VALUES (
    '2010-05-01T00:00:00.000',  -- < 2010-05-01 進分區 1
    '2012-09-01T00:00:00.000'   -- 2010-05-01 ~ 2012-09-01 進分區 2
);
GO
/* RANGE LEFT 含義：
   分區 1: SaleTime <= '2010-05-01'
   分區 2: '2010-05-01' < SaleTime <= '2012-09-01'
   分區 3: SaleTime > '2012-09-01'
*/

3. 建立分區方案：映射文件組

-- 假設已建立文件組 Sale1, Sale2, Sale3
CREATE PARTITION SCHEME pt_Sale
AS PARTITION pf_Sale
TO ([Sale1], [Sale2], [Sale3]);
GO

4. 重建叢集索引到分區方案（數據實際移動發生在這裡）

CREATE CLUSTERED INDEX CX_Sale_SaleTime
ON dbo.Sale (SaleTime)
ON pt_Sale(SaleTime)
WITH (DROP_EXISTING = OFF, ONLINE = ON);  -- Enterprise 版支援 ONLINE
GO

驗證分區分布

SELECT 
    $PARTITION.pf_Sale(SaleTime) AS PartitionNumber,
    COUNT(*) AS RowCount,
    MIN(SaleTime) AS MinDate,
    MAX(SaleTime) AS MaxDate
FROM dbo.Sale
GROUP BY $PARTITION.pf_Sale(SaleTime)
ORDER BY PartitionNumber;

關鍵注意事項

陷阱	說明
主鍵包含分區列	叢集主鍵必須包含 `SaleTime`，否則無法建在分區方案上。若業務主鍵只有 `Id`，需改為非叢集主鍵 + 叢集索引`(SaleTime, Id)`。
文件組預建	執行分區方案前，必須先在數據庫屬性→文件組→添加 Sale1/2/3 並指定 .ndf 路徑。
RANGE LEFT/RIGHT	邊界值歸屬不同，務必與業務邏輯對齊（如月末歸屬哪個月）。
統計信息	分區表統計按分區維護，`UPDATE STATISTICS` 會掃描全表，大表建議增量更新或分區級維護。
版本限制	分區表僅 Enterprise/Developer/Evaluation 版支援（Standard 2016 SP1+ 部分支援）。

分區切換應用：秒級歸檔歷史數據

-- 1. 建立結構相同的暫存表（同分區方案）
CREATE TABLE dbo.Sale_Staging (...) ON pt_Sale(SaleTime);

-- 2. 切換分區 1 (2010 前) 到暫存表
ALTER TABLE dbo.Sale SWITCH PARTITION 1 TO dbo.Sale_Staging PARTITION 1;

-- 3. 暫存表改名/備份/刪除，元數據操作，零數據移動
DROP TABLE dbo.Sale_Staging;