程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server 動態生成分區腳本

SQL Server 動態生成分區腳本

編輯:關於SqlServer

SQL Server 2005中只能通過SQL腳本來創建表分區,而在SQL Server 2008的SSMS中已經提供了操作界面進行表分區,但是也不能把操作生成SQL腳本,所以,下面的SQL的應用場景包括SQL Server 2005、SQL Server 2008。

 

解析

下面就是生成分區的腳本了,在執行之前,你需要填寫數據庫名稱、表名、分區表字段、需要的分區數、保存分區文件的路徑、分區初始化大小、分區文件的增量、分區邊界值;

這裡的分區邊界值是按照int類型進行增量計算的,比如你想以每100W進行范圍分區的話,那你只要設置@FunValue為100W;如果你的分區邊界值是其它類型值或者是不等范圍的分區,那麼你只要修改這個變量為字符串,並對分區函數的生成代碼進行相應修改就可以滿足你的需求了。

通常情況下,我們會以一個表Id(int),並且是自增作為分區字段,這樣就很容易區分歷史數據了,而且對分區的操作隔離也是最明顯的。

 

--生成分區腳本

DECLARE @DataBaseName NVARCHAR(50)--數據庫名稱

DECLARE @TableName NVARCHAR(50)--表名稱

DECLARE @ColumnName NVARCHAR(50)--字段名稱

DECLARE @PartNumber INT--需要分多少個區

DECLARE @Location NVARCHAR(50)--保存分區文件的路徑

DECLARE @Size NVARCHAR(50)--分區初始化大小

DECLARE @FileGrowth NVARCHAR(50)--分區文件增量

DECLARE @FunValue INT--分區分段值

DECLARE @i INT

DECLARE @PartNumberStr NVARCHAR(50)

DECLARE @sql NVARCHAR(max)

 

--設置下面變量

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.創建文件組

SET @i = 1

PRINT '--1.創建文件組'

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.創建文件

SET @i = 1

PRINT CHAR(13)+'--2.創建文件'

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.創建分區函數

PRINT CHAR(13)+'--3.創建分區函數'

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.創建分區方案

PRINT CHAR(13)+'--4.創建分區方案'

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.分區函數的記錄數

PRINT CHAR(13)+'--5.分區函數的記錄數'

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.創建文件組

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

(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.創建分區函數

CREATE PARTITION FUNCTION

Fun_User_Id(INT) AS

RANGE RIGHT

FOR VALUES(10000000,20000000,30000000)

 

 

--4.創建分區方案

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.分區函數的記錄數

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 中的分區表和索引中同樣提供了一個腳本用於生成表分區,和他不同的是:他采用了表來保存文件路徑,再使用游標來創建文件而已,其實這只能生成一部分代碼,而我的腳本不同之處就是能最大限度的生成常規表分區的常用代碼,方便快捷很多。有興趣的可以下載SQL2005PartitioningScripts.exe

上面使用表保存信息的這種想法在後期的數據搬遷(比如需要對一個現有的表進行表分區,這種情況下通常會先建一個分區表,再進行導入現有表的數據)中是有很大的用處的,後面的文章中會講講如何進行自動化的數據搬遷,敬請留意。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved