轉(zhuǎn)載自：http://www.cnblogs.com/gaizai/archive/2011/01/14/1935579.html
一、前言
前段時(shí)間使用表分區(qū)比較多，雖然已經(jīng)寫了SQL Server 合并（刪除）分區(qū)解惑、SQL Server 2005 分區(qū)模板與實(shí)例，但是在實(shí)踐中一直感覺修改SQL腳本的時(shí)間比較多，一直想抽個(gè)時(shí)間來把分區(qū)腳本進(jìn)行動(dòng)態(tài)化，今天終于付之于行動(dòng)了。需要說明的一點(diǎn)，下面的腳本并不能滿足所有情況，用戶可以根據(jù)自己的需要進(jìn)行相應(yīng)的調(diào)整，應(yīng)該可以滿足你的需求的。
在SQL Server 2005中只能通過SQL腳本來創(chuàng)建表分區(qū)，而在SQL Server 2008的SSMS中已經(jīng)提供了操作界面進(jìn)行表分區(qū)，但是也不能把操作生成SQL腳本，所以，下面的SQL的應(yīng)用場景包括SQL Server 2005、SQL Server 2008。
二、分解
下面就是生成分區(qū)的腳本了，在執(zhí)行之前，你需要填寫數(shù)據(jù)庫名稱、表名、分區(qū)表字段、需要的分區(qū)數(shù)、保存分區(qū)文件的路徑、分區(qū)初始化大小、分區(qū)文件的增量、分區(qū)邊界值；
這里的分區(qū)邊界值是按照int類型進(jìn)行增量計(jì)算的，比如你想以每100W進(jìn)行范圍分區(qū)的話，那你只要設(shè)置@FunValue為100W；如果你的分區(qū)邊界值是其它類型值或者是不等范圍的分區(qū)，那么你只要修改這個(gè)變量為字符串，并對(duì)分區(qū)函數(shù)的生成代碼進(jìn)行相應(yīng)修改就可以滿足你的需求了。
通常情況下，我們會(huì)以一個(gè)表Id(int)，并且是自增作為分區(qū)字段，這樣就很容易區(qū)分歷史數(shù)據(jù)了，而且對(duì)分區(qū)的操作隔離也是最明顯的。
--生成分區(qū)腳本
DECLARE @DataBaseName NVARCHAR(50)--數(shù)據(jù)庫名稱
DECLARE @TableName NVARCHAR(50)--表名稱
DECLARE @ColumnName NVARCHAR(50)--字段名稱
DECLARE @PartNumber INT--需要分多少個(gè)區(qū)
DECLARE @Location NVARCHAR(50)--保存分區(qū)文件的路徑
DECLARE @Size NVARCHAR(50)--分區(qū)初始化大小
DECLARE @FileGrowth NVARCHAR(50)--分區(qū)文件增量
DECLARE @FunValue INT--分區(qū)分段值
DECLARE @i INT
DECLARE @PartNumberStr NVARCHAR(50)
DECLARE @sql NVARCHAR(max)
--設(shè)置下面變量
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.創(chuàng)建文件組
SET @i = 1
PRINT '--1.創(chuàng)建文件組'
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.創(chuàng)建文件
SET @i = 1
PRINT CHAR(13)+'--2.創(chuàng)建文件'
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.創(chuàng)建分區(qū)函數(shù)
PRINT CHAR(13)+'--3.創(chuàng)建分區(qū)函數(shù)'
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.創(chuàng)建分區(qū)方案
PRINT CHAR(13)+'--4.創(chuàng)建分區(qū)方案'
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.分區(qū)函數(shù)的記錄數(shù)
PRINT CHAR(13)+'--5.分區(qū)函數(shù)的記錄數(shù)'
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.創(chuàng)建文件組
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.創(chuàng)建文件
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.創(chuàng)建分區(qū)函數(shù)
CREATE PARTITION FUNCTION
Fun_User_Id(INT) AS
RANGE RIGHT
FOR VALUES(10000000,20000000,30000000)
--4.創(chuàng)建分區(qū)方案
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.分區(qū)函數(shù)的記錄數(shù)
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 中的分區(qū)表和索引中同樣提供了一個(gè)腳本用于生成表分區(qū)，和他不同的是：他采用了表來保存文件路徑，再使用游標(biāo)來創(chuàng)建文件而已，其實(shí)這只能生成一部分代碼，而我的腳本不同之處就是能最大限度的生成常規(guī)表分區(qū)的常用代碼，方便快捷很多。有興趣的童鞋可以下載：SQL2005PartitioningScripts.exe
上面使用表保存信息的這種想法在后期的數(shù)據(jù)搬遷（比如需要對(duì)一個(gè)現(xiàn)有的表進(jìn)行表分區(qū)，這種情況下通常會(huì)先建一個(gè)分區(qū)表，再進(jìn)行導(dǎo)入現(xiàn)有表的數(shù)據(jù)）中是有很大的用處的，后面的文章中會(huì)講講如何進(jìn)行自動(dòng)化的數(shù)據(jù)搬遷，敬請(qǐng)留意。
四、參考文獻(xiàn)
SQL Server 2005 中的分區(qū)表和索引