SQL Server 生成动态分区脚本代码达成

Entegra for SQL ServerV2.32正式版种类：数据库类大小：40.7M语言：中文 评分：6.6标签：立即下载1、前言之前用表分区比较多，虽然已经写了SQL Server 合并（删除）分区解惑、SQL Server 2005 分区模板与实例，但在实践中一直感觉修改SQL脚本的时间比较多，一直想抽个时间来把分区脚本进行动态化，今天终于付之于行动了。需要说明的一点，下面的脚本并难以满足所有状况，用户可以参考我们的需要进行相应的调整，应该可以满足你的需要的。在SQL Server 2005中只能通过SQL脚本来创建表分区，而在SQL Server 2008的SSMS中已经提供了操作界面进行表分区，但也不可以把操作生成SQL脚本，所以，下面的SQL的应用场景包含SQL Server 2005、SQL Server 2008。

2、分解

下面就是生成分区的脚本了，在实行之前，你需要填写数据库名字、表名、分区表字段、需要的分区数、保存分区文件的路径、分区初始化大小、分区文件的增量、分区边界值；

这里的分区边界值是根据int种类进行增量计算的，譬如你想以每100W进行范围分区的话，那你只须设置@FunValue为100W；假如你的分区边界值是其它种类值或者是不等范围的分区，那样你只须修改这个变量为字符串，并对分区函数的生成代码进行相应修改就能满足你的需要了。

一般情况下，大家会以一个表Id，并且是自增作为分区字段，如此就比较容易区别历史数据了，而且对分区的操作隔离也是最明显的。

--生成分区脚本

DECLARE @DataBaseName NVARCHAR--数据库名字

DECLARE @TableName NVARCHAR--表名字

DECLARE @ColumnName NVARCHAR--字段名字

DECLARE @PartNumber INT--需要分多少个区

DECLARE @Location NVARCHAR--保存分区文件的路径

DECLARE @Size NVARCHAR--分区初始化大小

DECLARE @FileGrowth NVARCHAR--分区文件增量

DECLARE @FunValue INT--分区别段值

DECLARE @i INT

DECLARE @PartNumberStr NVARCHAR

DECLARE @sql NVARCHAR

--设置下面变量SET @DataBaseName = 'MyDataBase'SET @TableName = 'User'SET @ColumnName = 'Id'SET @PartNumber = 4SET @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,2)

SET @sql = 'ALTER DATABASE ['+@DataBaseName +']

ADD FILEGROUP [FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+']'

PRINT @sql + CHAR

SET @i=@i+1

END

--2.创建文件SET @i = 1PRINT CHAR+'--2.创建文件'WHILE @i = @PartNumberBEGINSET @PartNumberStr = RIGHT,2)SET @sql = 'ALTER DATABASE ['+@DataBaseName +']ADD FILETO FILEGROUP [FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'];'PRINT @sql + CHARSET @i=@i+1END

--3.创建分区函数

PRINT CHAR+'--3.创建分区函数'

DECLARE @FunValueStr NVARCHAR

SET @i = 1

SET @FunValueStr = ''

WHILE @i @PartNumber

BEGIN

SET @FunValueStr = @FunValueStr + convert,) + ','

SET @i=@i+1

END

SET @FunValueStr = substring-1)

SET @sql = 'CREATE PARTITION FUNCTION

Fun_'+@TableName+'_'+@ColumnName+' AS

RANGE RIGHT

FOR VALUES'

PRINT @sql + CHAR

--4.创建分区策略PRINT CHAR+'--4.创建分区策略'DECLARE @FileGroupStr NVARCHAR SET @i = 1SET @FileGroupStr = ''WHILE @i = @PartNumberBEGINSET @PartNumberStr = RIGHT,2)SET @FileGroupStr = @FileGroupStr + '[FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'],'SET @i=@i+1ENDSET @FileGroupStr = substring-1)SET @sql = 'CREATE PARTITION SCHEMESch_'+@TableName+'_'+@ColumnName+' ASPARTITION Fun_'+@TableName+'_'+@ColumnName+'TO'PRINT @sql + CHAR

--5.分区函数的记录数

PRINT CHAR+'--5.分区函数的记录数'

SET @sql = 'SELECT $PARTITION.Fun_'+@TableName+'_'+@ColumnName+' AS Partition_num,

MIN AS Min_value,MAX AS Max_value,COUNT AS Record_num

FROM dbo.'+@TableName+'

GROUP BY $PARTITION.Fun_'+@TableName+'_'+@ColumnName+'

ORDER BY $PARTITION.Fun_'+@TableName+'_'+@ColumnName+';'

PRINT @sql + CHAR

生成的脚本如下：--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

TO FILEGROUP [FG_User_Id_01];

ALTER DATABASE [MyDataBase]

ADD FILE

TO FILEGROUP [FG_User_Id_02];

ALTER DATABASE [MyDataBase]ADD FILETO FILEGROUP [FG_User_Id_03];

ALTER DATABASE [MyDataBase]

ADD FILE

TO FILEGROUP [FG_User_Id_04];

--3.创建分区函数

CREATE PARTITION FUNCTION

Fun_User_Id AS

RANGE RIGHT

FOR VALUES

--4.创建分区策略

CREATE PARTITION SCHEME

Sch_User_Id AS

PARTITION Fun_User_Id

TO

--5.分区函数的记录数SELECT $PARTITION.Fun_User_Id AS Partition_num,MIN AS Min_value,MAX AS Max_value,COUNT AS Record_numFROM dbo.UserGROUP BY $PARTITION.Fun_User_IdORDER BY $PARTITION.Fun_User_Id;

3、后记

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

上面用表保存信息的这种想法在后期的数据搬迁（譬如需要对一个现有些表进行表分区，这样的情况下一般会先建一个分区表，再进行导入现有表的数据）中是有非常大的用处的，后面的文章中会讲讲怎么样进行智能化的数据搬迁，敬请注意。

4、参考文献

SQL Server 2005 中的分区表和索引

TAG标签：sqlserver(1)

转载请说明来源于当快软件园（https://www.lrvxg.com）

本文地址：https://www.lrvxg.com/news/937.html

郑重声明：文章来源于网络作为参考，本站仅用于分享不存储任何下载资源,如果网站中图片和文字侵犯了您的版权，请联系我们处理！邮箱3450399331@qq.com