一、前言

前段時(shí)間使用表分區(qū)比較多，雖然已經(jīng)寫了解惑：對SQL Server分區(qū)進(jìn)行合并（刪除）、SQL Server 2005 分區(qū)模板與實(shí)例，但是在實(shí)踐中一直感覺修改SQL腳本的時(shí)間比較多，一直想抽個(gè)時(shí)間來把分區(qū)腳本進(jìn)行動態(tài)化，今天終于付之于行動了。需要說明的一點(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è)變量為字符串，并對分區(qū)函數(shù)的生成代碼進(jìn)行相應(yīng)修改就可以滿足你的需求了。

通常情況下，我們會以一個(gè)表Id(int)，并且是自增作為分區(qū)字段，這樣就很容易區(qū)分歷史數(shù)據(jù)了，而且對分區(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ū)的常用代碼，方便快捷很多。有興趣的童鞋可以去下載。

上面使用表保存信息的這種想法在后期的數(shù)據(jù)搬遷（比如需要對一個(gè)現(xiàn)有的表進(jìn)行表分區(qū)，這種情況下通常會先建一個(gè)分區(qū)表，再進(jìn)行導(dǎo)入現(xiàn)有表的數(shù)據(jù)）中是有很大的用處的，后面的文章中會講講如何進(jìn)行自動化的數(shù)據(jù)搬遷，敬請留意。

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

【編輯推薦】

1. SQL Server復(fù)災(zāi) 你懂了嗎？
2. SQL Server管理 這些你懂嗎?
3. 客戶的一次疏忽，DBA的一次噩夢
4. 單數(shù)據(jù)庫vs多數(shù)據(jù)庫，單實(shí)例vs多實(shí)例 效率測試
5. 關(guān)于Update在不同數(shù)據(jù)庫的使用