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

SQL Server 動態產生分區表腳本：檔案群組、分區函數、方案一鍵生成

編輯：關於SqlServer

SQL Server 2005 僅支援 T-SQL 建立分區表，2008 SSMS 雖有 UI 但無法產生腳本。本文提供完整動態腳本，輸入參數即可輸出：檔案群組建立、檔案建立、分區函數、分區方案、分區統計查詢，適用 2005/2008/2012+ 所有版本。

參數設定區 (依實際環境修改)

DECLARE @DataBaseName NVARCHAR(50) = 'MyDataBase';  -- 資料庫名稱
DECLARE @TableName NVARCHAR(50) = 'User';            -- 表名稱
DECLARE @ColumnName NVARCHAR(50) = 'Id';             -- 分區欄位 (建議自增 INT)
DECLARE @PartNumber INT = 4;                         -- 分區數量
DECLARE @Location NVARCHAR(50) = 'E:\DataBase\';     -- 檔案儲存路徑
DECLARE @Size NVARCHAR(50) = '30MB';                 -- 初始大小
DECLARE @FileGrowth NVARCHAR(50) = '10%';            -- 成長增量
DECLARE @FunValue INT = 10000000;                    -- 分區邊界遞增值 (如 1000萬 = 每區 1000萬 ID)

動態產生腳本核心邏輯

1. 建立檔案群組

DECLARE @i INT = 1, @PartNumberStr NVARCHAR(50), @sql NVARCHAR(MAX);
PRINT '--1.建立檔案群組';
WHILE @i <= @PartNumber
BEGIN
    SET @PartNumberStr = RIGHT('0' + CONVERT(NVARCHAR, @i), 2);
    SET @sql = 'ALTER DATABASE [' + @DataBaseName + '] ADD FILEGROUP [FG_' + @TableName + '_' + @ColumnName + '_' + @PartNumberStr + ']';
    PRINT @sql + CHAR(13);
    SET @i = @i + 1;
END

2. 建立檔案 (.ndf)

SET @i = 1;
PRINT CHAR(13) + '--2.建立檔案';
WHILE @i <= @PartNumber
BEGIN
    SET @PartNumberStr = RIGHT('0' + CONVERT(NVARCHAR, @i), 2);
    SET @sql = 'ALTER DATABASE [' + @DataBaseName + '] ADD FILE (NAME = N''FG_' + @TableName + '_' + @ColumnName + '_' + @PartNumberStr + '_data'', FILENAME = N''' + @Location + 'FG_' + @TableName + '_' + @ColumnName + '_' + @PartNumberStr + '_data.ndf'', SIZE = ' + @Size + ', FILEGROWTH = ' + @FileGrowth + ') TO FILEGROUP [FG_' + @TableName + '_' + @ColumnName + '_' + @PartNumberStr + ']';
    PRINT @sql + CHAR(13);
    SET @i = @i + 1;
END

3. 建立分區函數 (RANGE RIGHT，邊界值屬於下一分區)

PRINT CHAR(13) + '--3.建立分區函數';
DECLARE @FunValueStr NVARCHAR(MAX) = '';
SET @i = 1;
WHILE @i < @PartNumber
BEGIN
    SET @FunValueStr = @FunValueStr + CONVERT(NVARCHAR(50), @i * @FunValue) + ',';
    SET @i = @i + 1;
END
SET @FunValueStr = SUBSTRING(@FunValueStr, 1, LEN(@FunValueStr) - 1);  -- 移除尾逗號
SET @sql = 'CREATE PARTITION FUNCTION Fun_' + @TableName + '_' + @ColumnName + '(INT) AS RANGE RIGHT FOR VALUES (' + @FunValueStr + ')';
PRINT @sql + CHAR(13);

4. 建立分區方案 (對應檔案群組)

PRINT CHAR(13) + '--4.建立分區方案';
DECLARE @FileGroupStr NVARCHAR(MAX) = '';
SET @i = 1;
WHILE @i <= @PartNumber
BEGIN
    SET @PartNumberStr = RIGHT('0' + CONVERT(NVARCHAR, @i), 2);
    SET @FileGroupStr = @FileGroupStr + '[FG_' + @TableName + '_' + @ColumnName + '_' + @PartNumberStr + '],';
    SET @i = @i + 1;
END
SET @FileGroupStr = SUBSTRING(@FileGroupStr, 1, LEN(@FileGroupStr) - 1);
SET @sql = 'CREATE PARTITION SCHEME Sch_' + @TableName + '_' + @ColumnName + ' AS PARTITION Fun_' + @TableName + '_' + @ColumnName + ' TO (' + @FileGroupStr + ')';
PRINT @sql + CHAR(13);

5. 分區資料統計查詢 (部署後驗證用)

PRINT CHAR(13) + '--5.分區函數記錄數統計';
SET @sql = 'SELECT $PARTITION.Fun_' + @TableName + '_' + @ColumnName + '(' + @ColumnName + ') AS Partition_num, MIN(' + @ColumnName + ') AS Min_value, MAX(' + @ColumnName + ') AS Max_value, COUNT(1) AS Record_num FROM dbo.' + @TableName + ' GROUP BY $PARTITION.Fun_' + @TableName + '_' + @ColumnName + '(' + @ColumnName + ') ORDER BY $PARTITION.Fun_' + @TableName + '_' + @ColumnName + '(' + @ColumnName + ');';
PRINT @sql + CHAR(13);

輸出範例 (參數同上)

--1.建立檔案群組
ALTER DATABASE [MyDataBase] ADD FILEGROUP [FG_User_Id_01]
ALTER DATABASE [MyDataBase] ADD FILEGROUP [FG_User_Id_02]
ALTER DATABASE [MyDataBase] ADD FILEGROUP [FG_User_Id_03]
ALTER DATABASE [MyDataBase] ADD FILEGROUP [FG_User_Id_04]

--2.建立檔案
ALTER DATABASE [MyDataBase] ADD FILE (NAME = N'FG_User_Id_01_data', FILENAME = N'E:\DataBase\FG_User_Id_01_data.ndf', SIZE = 30MB, FILEGROWTH = 10%) TO FILEGROUP [FG_User_Id_01]
... (02~04 同理)

--3.建立分區函數
CREATE PARTITION FUNCTION Fun_User_Id(INT) AS RANGE RIGHT FOR VALUES (10000000,20000000,30000000)

--4.建立分區方案
CREATE PARTITION SCHEME Sch_User_Id AS PARTITION Fun_User_Id TO ([FG_User_Id_01],[FG_User_Id_02],[FG_User_Id_03],[FG_User_Id_04])

--5.分區統計查詢
SELECT $PARTITION.Fun_User_Id(Id) AS Partition_num, MIN(Id) AS Min_value, MAX(Id) AS Max_value, COUNT(1) AS Record_num FROM dbo.User GROUP BY $PARTITION.Fun_User_Id(Id) ORDER BY $PARTITION.Fun_User_Id(Id);

擴充建議

非等距邊界：將 @FunValue 改為字串參數 @BoundaryValues (如 '1000,5000,12000')，直接用於函數建立
非 INT 分區欄位：修改函數型別 (DATE、BIGINT 等) 與邊界值格式
檔案群組資訊表：MSDN 範例使用資料表儲存路徑再用游標建立，適合後續資料搬遷階段擴充
自動化部署：將 PRINT 改為動態執行 (EXEC) 或輸出至 .sql 檔交由 CI/CD 執行