SQL SERVER 2005 分区的基本操作

1.创建数据库 [UserDB]

2.添加文件组到数据库

ALTER DATABASE UserDB ADD FILEGROUP SecondFG;ALTER DATABASE UserDB ADD FILE(NAME='f2',FILENAME='D:/Microsoft SQL Server/Data/userdb2.ndf') TO FILEGROUP SecondFG

如果不想使用多文件组，可以不用加文件组，全部使用[primary]文件组。

3.创建分区函数,分区函数是属于数据库的

CREATE PARTITION FUNCTION [AgePF](int) AS RANGE LEFT FOR VALUES (30,40)

以上创建了一个名为 AgePF 的分区函数，输入参数数据类型为 int,函数的分隔点分别是 30 和 40。
用的 LEFT 关键字，指示分界值存在于他左边的分区上 。分界值可以有 999 个，即最多支持 1000 个分区。

4.创建分区方案

CREATE PARTITION SCHEME [AgePS] AS PARTITION [AgePF] TO ([PRIMARY], [SecondFG],[PRIMARY])

以上使用分区函数 TempPF 创建了一个名为 AgePS 的分区方案，一个分区函数可以创建多个分区方案。
分区方案中指定第一个分区使用 [Primary] 文件组存储数据，第二个分区使用 SecondFG 存储数据，第三个分区使用 [Primary] 文件组存储数据。

5.创建基于分区方案的数据表

CREATE TABLE [UserInfo]([id] [int] IDENTITY(1,1) NOT NULL,[Name] [nvarchar](20) NOT NULL,[Sex] [bit] NOT NULL,[Age] [int] NOT NULL,[Tel] [varchar](32) NULL,[Email] [nvarchar](50) NOT NULL) ON [AgePS]([Age])

以上使用分区方案 AgePS 以 [Age] 列为分区列创建了一个数据表。
创建完数据表就可以插入数据了。

6.查看每分区的记录数

SELECT COUNT(*) AS 数量,$PARTITION.AgePF(Age) AS 分区 FROM UserInfo GROUP BY $PARTITION.AgePF(Age)

7.查看记录所在分区

SELECT AGE,$PARTITION.AgePF(Age) AS 分区 FROM UserInfo

8.修改分区方案

其实修改分区方案与修改分区函数一般同时执行。当增加分区时要指定分区方案中新分区的文件组，按如下语句：

ALTER PARTITION SCHEME [AgePS] NEXT USED [primary]

上句指定了当拆分出新分区时，使用 [primary] 文件组，当然这个可以随意指定数据库包括的文件组，也可以再新加入一个文件组来用。

9.修改分区函数：增加分区

ALTER PARTITION FUNCTION AgePF() SPLIT RANGE(20)

在分区方案或分区函数上点右键，创建分区函数或分区方案的create脚本就可以看到:
CREATE PARTITION FUNCTION [AgePF](int) AS RANGE LEFT FOR VALUES (20, 30, 40)CREATE PARTITION SCHEME [AgePS] AS PARTITION [AgePF] TO ([PRIMARY], [PRIMARY], [SecondFG], [PRIMARY])

或在有数据的情况下用6、7两步里的脚本查看。

以上增加了一个分区，即加了一个分界值，分界值所在的分区为新分区(使用 next used 指定的文件组)， 每次执行增加分区的操作都要在基于此分区函数的分区方案上执行 next used 的操作。

10.修改分区函数:合并分区

ALTER PARTITION FUNCTION AgePF() MERGE RANGE(30)

以上删除分界值 30 ，包含 30 值的分区上的数据将被转移到 30 值相邻的分区上，然后在分区方案中删除包含30 值这个分区所对应的文件组。
在分区方案或分区函数上点右键，创建分区函数或分区方案的create脚本就可以看到:
CREATE PARTITION SCHEME [AgePS] AS PARTITION [AgePF] TO ([PRIMARY], [SecondFG], [PRIMARY])CREATE PARTITION FUNCTION [AgePF](int) AS RANGE LEFT FOR VALUES (20, 40)

或在有数据的情况下用6、7两步里的脚本查看记录所在分区或每分区的记录数量。

---------------------------------------------------------------------------------------

---生成分区脚本 
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 = = = , 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 
) AS 
RANGE RIGHT 
FOR VALUES(
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 
AS 
PARTITION 
TO(
 
PRINT @sql + CHAR(13)
 
--5.分区函数的记录数 
PRINT CHAR(13)+'--5.分区函数的记录数' 
SET @sql = 'SELECT ) AS Partition_num, 
 MIN() AS Min_value,MAX() AS Max_value,COUNT(1) AS Record_num 
FROM 
GROUP BY ) 
ORDER BY 
PRINT @sql + CHAR(13)