SQLServer如何动态生成分区脚本

一、前言

吉县网站制作公司哪家好，找创新互联！从网页设计、网站建设、微信开发、APP开发、响应式网站设计等网站项目制作，到程序开发，运营维护。创新互联从2013年开始到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选创新互联。

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

生成的脚本如下：

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

三、后记

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

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

本文题目：SQLServer如何动态生成分区脚本
网页地址：http://www.stwzsj.com/qtweb/news24/11874.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

App设计知识

同城分类信息

成都响应式网站　　 　光华村机房　　 　西云服务器托管　　 　泸州叙永网站建设　　 　中国电信成都枢纽中心　　 　成都app软件开发公司　　 　成都品牌网站建设　　 　自贡电信机房　　 　精灵网　　 　成都网站推广　　 　香港空间腾讯云　　 　关键词排名　　 　成都圣月窗帘　　 　成都微信开发　　 　南充电信机房托管　　 　四川艾名斯　　 　成都枢纽中心　　 　腾讯免备案空间　　 　乐山服务器托管　　 　服务器租用