SQL Server 动态生成分区脚本-阿里云开发者社区

SQL Server 动态生成分区脚本

2017-10-09 933

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS SQL Server，独享型 2核4GB

简介：

一、前言

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

三、后记

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

上面使用表保存信息的这种想法在后期的数据搬迁（比如需要对一个现有的表进行表分区，这种情况下通常会先建一个分区表，再进行导入现有表的数据）中是有很大的用处的，后面的文章中会讲讲如何进行自动化的数据搬迁，敬请留意。

SQL Server 动态生成分区脚本

热门文章

最新文章

相关课程

相关电子书

相关实验场景