建站
咨詢
售后
建站咨詢
新聞中心
一、前言
吉縣網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)！從網(wǎng)頁設(shè)計、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、響應(yīng)式網(wǎng)站設(shè)計等網(wǎng)站項(xiàng)目制作，到程序開發(fā)，運(yùn)營維護(hù)。創(chuàng)新互聯(lián)從2013年開始到現(xiàn)在10年的時間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)。
前段時間使用表分區(qū)比較多，雖然已經(jīng)寫了解惑：對SQL Server分區(qū)進(jìn)行合并（刪除）、SQL Server 2005 分區(qū)模板與實(shí)例，但是在實(shí)踐中一直感覺修改SQL腳本的時間比較多，一直想抽個時間來把分區(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)行增量計算的，比如你想以每100W進(jìn)行范圍分區(qū)的話，那你只要設(shè)置@FunValue為100W；如果你的分區(qū)邊界值 是其它類型值或者是不等范圍的分區(qū)，那么你只要修改這個變量為字符串，并對分區(qū)函數(shù)的生成代碼進(jìn)行相應(yīng)修改就可以滿足你的需求了。
通常情況下，我們會以一個表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--需要分多少個區(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ū)表和索引中同樣提供了一個腳本用于生成表分區(qū)，和他不同的是：他采用了表來保存文件路徑，再使用游標(biāo)來創(chuàng)建文件而已，其實(shí)這只能生成一部分代碼，而我的腳本不同之處就是能***限度的生成常規(guī)表分區(qū)的常用代碼，方便快捷很多。有興趣的童鞋可以去下載。
上面使用表保存信息的這種想法在后期的數(shù)據(jù)搬遷（比如需要對一個現(xiàn)有的表進(jìn)行表分區(qū)，這種情況下通常會先建一個分區(qū)表，再進(jìn)行導(dǎo)入現(xiàn)有表的數(shù)據(jù)）中是有很大的用處的，后面的文章中會講講如何進(jìn)行自動化的數(shù)據(jù)搬遷，敬請留意。
當(dāng)前標(biāo)題：SQLServer如何動態(tài)生成分區(qū)腳本
標(biāo)題網(wǎng)址：http://www.dlmjj.cn/article/cdghgig.html
