SQL Server2000教程 SQL Server2005教程 SQL Server2008教程 SQL SERVER2012教程
返回首页

SQL Server 动态生成分区脚本

时间:2011-01-14 16:42来源:未知 作者:admin 点击:我要投稿  高质量的ASP.NET空间,完美支持1.0/2.0/3.5/4.0/MVC等

一、前言

前段时间使用表分区比较多,虽然已经写了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 2005 中的分区表和索引

作者:听风吹雨

本站推荐文章:
本站热点文章:
顶一下
(0)
0%
踩一下
(1)
100%
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色 情、暴力、反动的言论。
评价:
表情:
用户名:密码: 验证码:点击我更换图片