一、前言

　　前段时间使用表分区比较多，虽然已经写了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:DataBaseFG_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:DataBaseFG_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:DataBaseFG_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:DataBaseFG_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);

　　三、后记

　　在MSND的SQL Server 2005 中的分区表和索引中同样提供了一个脚本用于生成表分区，和他不同的是：他采用了表来保存文件路径，再使用游标来创建文件而已，其实这只能生成一部分代码，而我的脚本不同之处就是能最大限度的生成常规表分区的常用代码，方便快捷很多。有兴趣的童鞋可以下载：SQL2005PartitioningScripts.exe

　　上面使用表保存信息的这种想法在后期的数据搬迁(比如需要对一个现有的表进行表分区，这种情况下通常会先建一个分区表，再进行导入现有表的数据)中是有很大的用处的。