SQL Server如何动态生成分区脚本

数据库 SQL Server
在SQL Server 2005中只能通过SQL脚本来创建表分区,而在SQL Server 2008的SSMS中已经提供了操作界面进行表分区,但是也不能把操作生成SQL脚本,所以,下面的SQL的应用场景包括SQL Server 2005、SQL Server 2008。

一、前言

前段时间使用表分区比较多,虽然已经写了解惑:对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),并且是自增作为分区字段,这样就很容易区分历史数据了,而且对分区的操作隔离也是最明显的。

  1. --生成分区脚本 
  2. DECLARE @DataBaseName NVARCHAR(50)--数据库名称 
  3. DECLARE @TableName NVARCHAR(50)--表名称 
  4. DECLARE @ColumnName NVARCHAR(50)--字段名称 
  5. DECLARE @PartNumber INT--需要分多少个区 
  6. DECLARE @Location NVARCHAR(50)--保存分区文件的路径 
  7. DECLARE @Size NVARCHAR(50)--分区初始化大小 
  8. DECLARE @FileGrowth NVARCHAR(50)--分区文件增量 
  9. DECLARE @FunValue INT--分区分段值 
  10. DECLARE @i INT 
  11. DECLARE @PartNumberStr NVARCHAR(50) 
  12. DECLARE @sql NVARCHAR(max
  13.  
  14.  
  15. --设置下面变量 
  16. SET @DataBaseName = 'MyDataBase' 
  17. SET @TableName = 'User' 
  18. SET @ColumnName = 'Id' 
  19. SET @PartNumber = 4 
  20. SET @Location = 'E:\DataBase\' 
  21. SET @Size = '30MB' 
  22. SET @FileGrowth = '10%' 
  23. SET @FunValue = 10000000
  1. --1.创建文件组 
  2. SET @i = 1 
  3. PRINT '--1.创建文件组' 
  4. WHILE @i <= @PartNumber 
  5. BEGIN 
  6.     SET @PartNumberStr = RIGHT('0' + CONVERT(NVARCHAR,@i),2) 
  7.     SET @sql = 'ALTER DATABASE ['+@DataBaseName +'] 
  8. ADD FILEGROUP [FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+']' 
  9.     PRINT @sql + CHAR(13) 
  10.     SET @i=@i+1 
  11. END 
  12.  
  13.  
  14. --2.创建文件 
  15. SET @i = 1 
  16. PRINT CHAR(13)+'--2.创建文件' 
  17. WHILE @i <= @PartNumber 
  18. BEGIN 
  19.     SET @PartNumberStr = RIGHT('0' + CONVERT(NVARCHAR,@i),2) 
  20.     SET @sql = 'ALTER DATABASE ['+@DataBaseName +'] 
  21. ADD FILE 
  22. (NAME = N''FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'_data'',FILENAME = N'''+@Location+'FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'_data.ndf'',SIZE = '+@Size+', FILEGROWTH = '+@FileGrowth+' ) 
  23. TO FILEGROUP [FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'];' 
  24.     PRINT @sql + CHAR(13) 
  25.     SET @i=@i+1 
  26. END 
  27.  
  28. --3.创建分区函数 
  29. PRINT CHAR(13)+'--3.创建分区函数' 
  30. DECLARE @FunValueStr NVARCHAR(MAX
  31. SET @i = 1 
  32. SET @FunValueStr = '' 
  33. WHILE @i < @PartNumber 
  34. BEGIN 
  35.     SET @FunValueStr = @FunValueStr + convert(NVARCHAR(50),(@i*@FunValue)) + ',' 
  36.     SET @i=@i+1 
  37. END 
  38. SET @FunValueStr = substring(@FunValueStr,1,len(@FunValueStr)-1) 
  39. SET @sql = 'CREATE PARTITION FUNCTION 
  40. Fun_'+@TableName+'_'+@ColumnName+'(INTAS 
  41. RANGE RIGHT 
  42. FOR VALUES('+@FunValueStr+')' 
  43. PRINT @sql + CHAR(13)  
  44.  
  45. --4.创建分区方案 
  46. PRINT CHAR(13)+'--4.创建分区方案' 
  47. DECLARE @FileGroupStr NVARCHAR(MAX
  48. SET @i = 1 
  49. SET @FileGroupStr = '' 
  50. WHILE @i <= @PartNumber 
  51. BEGIN 
  52.     SET @PartNumberStr = RIGHT('0' + CONVERT(NVARCHAR,@i),2) 
  53.     SET @FileGroupStr = @FileGroupStr + '[FG_'+@TableName+'_'+@ColumnName+'_'+@PartNumberStr+'],' 
  54.     SET @i=@i+1 
  55. END 
  56. SET @FileGroupStr = substring(@FileGroupStr,1,len(@FileGroupStr)-1) 
  57. SET @sql = 'CREATE PARTITION SCHEME 
  58. Sch_'+@TableName+'_'+@ColumnName+' AS 
  59. PARTITION Fun_'+@TableName+'_'+@ColumnName+' 
  60. TO('+@FileGroupStr+')' 
  61. PRINT @sql + CHAR(13) 
  62.  
  63. --5.分区函数的记录数 
  64. PRINT CHAR(13)+'--5.分区函数的记录数' 
  65. SET @sql = 'SELECT $PARTITION.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+') AS Partition_num, 
  66.  MIN('+@ColumnName+'AS Min_value,MAX('+@ColumnName+'AS Max_value,COUNT(1) AS Record_num 
  67. FROM dbo.'+@TableName+' 
  68. GROUP BY $PARTITION.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+'
  69. ORDER BY $PARTITION.Fun_'+@TableName+'_'+@ColumnName+'('+@ColumnName+');' 
  70. PRINT @sql + CHAR(13) 

生成的脚本如下:

  1. --1.创建文件组 
  2. ALTER DATABASE [MyDataBase] 
  3. ADD FILEGROUP [FG_User_Id_01] 
  4. ALTER DATABASE [MyDataBase] 
  5. ADD FILEGROUP [FG_User_Id_02] 
  6. ALTER DATABASE [MyDataBase] 
  7. ADD FILEGROUP [FG_User_Id_03] 
  8. ALTER DATABASE [MyDataBase] 
  9. ADD FILEGROUP [FG_User_Id_04] 
  10.  
  11. --2.创建文件 
  12. ALTER DATABASE [MyDataBase] 
  13. ADD FILE 
  14. (NAME = N'FG_User_Id_01_data',FILENAME = N'E:\DataBase\FG_User_Id_01_data.ndf',SIZE = 30MB, FILEGROWTH = 10% ) 
  15. TO FILEGROUP [FG_User_Id_01]; 
  16. ALTER DATABASE [MyDataBase] 
  17. ADD FILE 
  18. (NAME = N'FG_User_Id_02_data',FILENAME = N'E:\DataBase\FG_User_Id_02_data.ndf',SIZE = 30MB, FILEGROWTH = 10% ) 
  19.  
  20. TO FILEGROUP [FG_User_Id_02]; 
  21. ALTER DATABASE [MyDataBase] 
  22. ADD FILE 
  23. (NAME = N'FG_User_Id_03_data',FILENAME = N'E:\DataBase\FG_User_Id_03_data.ndf',SIZE = 30MB, FILEGROWTH = 10% ) 
  24. TO FILEGROUP [FG_User_Id_03]; 
  25. ALTER DATABASE [MyDataBase] 
  26. ADD FILE 
  27. (NAME = N'FG_User_Id_04_data',FILENAME = N'E:\DataBase\FG_User_Id_04_data.ndf',SIZE = 30MB, FILEGROWTH = 10% ) 
  28. TO FILEGROUP [FG_User_Id_04]; 
  29.  
  30. --3.创建分区函数 
  31. CREATE PARTITION FUNCTION 
  32. Fun_User_Id(INTAS 
  33.  
  34. RANGE RIGHT 
  35. FOR VALUES(10000000,20000000,30000000) 
  36.  
  37. --4.创建分区方案 
  38. CREATE PARTITION SCHEME 
  39. Sch_User_Id AS 
  40. PARTITION Fun_User_Id 
  41. TO([FG_User_Id_01],[FG_User_Id_02],[FG_User_Id_03],[FG_User_Id_04]) 
  42.  
  43. --5.分区函数的记录数 
  44. SELECT $PARTITION.Fun_User_Id(Id) AS Partition_num, 
  45.  MIN(Id) AS Min_value,MAX(Id) AS Max_value,COUNT(1) AS Record_num 
  46. FROM dbo.User 
  47. GROUP BY $PARTITION.Fun_User_Id(Id) 
  48. ORDER BY $PARTITION.Fun_User_Id(Id); 

三、后记

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

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

原文链接:http://www.cnblogs.com/gaizai/archive/2011/01/14/1935579.html

【编辑推荐】

  1. SQL Server复灾 你懂了吗?
  2. SQL Server管理 这些你懂吗?
  3. 客户的一次疏忽,DBA的一次噩梦
  4. 单数据库vs多数据库,单实例vs多实例 效率测试
  5. 关于Update在不同数据库的使用
责任编辑:艾婧 来源: 听风吹雨饿博客
相关推荐

2009-05-31 09:25:03

SQL Server脚本数据库管理

2010-09-13 11:09:34

SQL SERVER分

2010-11-10 14:47:11

SQL Server创

2010-09-16 15:56:15

SQL Server表

2011-07-06 13:09:11

SQL Server

2011-03-21 10:13:31

Sql Server数分区

2010-11-10 14:35:17

SQL Server创

2013-05-20 16:09:39

SQL Server

2020-06-19 12:59:33

动态脚本Java

2010-08-05 09:07:24

2010-11-12 13:08:36

动态sql语句

2010-06-30 16:58:24

SQL Server分

2011-07-04 14:28:18

SQL Server分区

2015-07-20 17:05:38

SQL ServerNULL值

2010-05-13 10:00:10

SQL Server

2010-07-21 15:01:09

SQL Server

2010-07-21 14:55:48

SQL Server

2010-07-21 14:50:23

SQL Server

2011-03-29 15:27:26

SQL Server 动态管理

2010-11-08 09:59:22

SQL Server动
点赞
收藏

51CTO技术栈公众号