|
|
一、前言
前段時間使用表分區比較多，雖然已經寫了SQL Server 合并（刪除）分區解惑、SQL Server 2005 分區模板與實例，但是在實踐中一直感覺修改SQL腳本的時間比較多，一直想抽個時間來把分區腳本進行動態化，今天終于付之于行動了。需要說明的一點，下面的腳本并不能滿足所有情況，用戶可以根據自己的需要進行相應的調整，應該可以滿足你的需求的。
在SQL Server 2005中只能通過SQL腳本來創建表分區，而在SQL Server 2008的SSMS中已經提供了操作界面進行表分區，但是也不能把操作生成SQL腳本，所以，下面的SQL的應用場景包括SQL Server 2005、SQL Server 2008。
二、分解下面就是生成分區的腳本了，在執行之前，你需要填寫數據庫名稱、表名、分區表字段、需要的分區數、保存分區文件的路徑、分區初始化大小、分區文件的增量、分區邊界值；
這里的分區邊界值是按照int類型進行增量計算的，比如你想以每100W進行范圍分區的話，那你只要設置@FunValue為100W；如果你的分區邊界值是其它類型值或者是不等范圍的分區，那么你只要修改這個變量為字符串，并對分區函數的生成代碼進行相應修改就可以滿足你的需求了。
通常情況下，我們會以一個表Id(int)，并且是自增作為分區字段，這樣就很容易區分歷史數據了，而且對分區的操作隔離也是最明顯的。
--生成分區腳本
DECLARE @DataBaseName NVARCHAR(50)--數據庫名稱
DECLARE @TableName NVARCHAR(50)--表名稱
DECLARE @ColumnName NVARCHAR(50)--字段名稱
DECLARE @PartNumber INT--需要分多少個區
DECLARE @Location NVARCHAR(50)--保存分區文件的路徑
DECLARE @Size NVARCHAR(50)--分區初始化大小
DECLARE @FileGrowth NVARCHAR(50)--分區文件增量
DECLARE @FunValue INT--分區分段值
DECLARE @i INT
DECLARE @PartNumberStr NVARCHAR(50)
DECLARE @sql NVARCHAR(max)
--設置下面變量
SET @DataBaseName = 'MyDataBase'
SET @TableName = 'User'
SET @ColumnName = 'Id'
SET @PartNumber = 4
SET @Location = 'E:/DataBase/'
SET @Size = '30MB'
SET @FileGrowth = '10%'
SET @FunValue = 10000000
--1.創建文件組
SET @i = 1
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.創建文件
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.創建分區函數
PRINT CHAR(13)+'--3.創建分區函數'
DECLARE @FunValueStr NVARCHAR(MAX)
SET @i = 1
SET @FunValueStr = ''
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
SET @FileGroupStr = ''
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];
ALTER DATABASE [MyDataBase]
ADD FILE
(NAME = N'FG_User_Id_02_data',FILENAME = N'E:/DataBase/FG_User_Id_02_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )
TO FILEGROUP [FG_User_Id_02];
ALTER DATABASE [MyDataBase]
ADD FILE
(NAME = N'FG_User_Id_03_data',FILENAME = N'E:/DataBase/FG_User_Id_03_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )
TO FILEGROUP [FG_User_Id_03];
ALTER DATABASE [MyDataBase]
ADD FILE
(NAME = N'FG_User_Id_04_data',FILENAME = N'E:/DataBase/FG_User_Id_04_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )
TO FILEGROUP [FG_User_Id_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);
it知識庫：SQL Server 動態生成分區腳本，轉載需保留來源！
鄭重聲明：本文版權歸原作者所有，轉載文章僅為傳播更多信息之目的，如作者信息標記有誤，請第一時間聯系我們修改或刪除，多謝。
