SQLSERVER2005 分区的基本操作

【摘要】
前段时间公司一个应用的数据库数据量急剧增加，很多查询占用了大量的系统资源，慢的几乎瘫痪，所以研究了一下SQL Server2005的分区表操作，完成后查询效率得到了很大的提高，现在把分区表的应用过程简单的总结下：


【全文】

1.创建数据库 [UserDB]

2.添加文件组到数据库

 

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

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

 

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

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

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

 

4.创建分区方案

 

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

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

 

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

1. CREATE TABLE [UserInfo](
2.  [id] [int] IDENTITY(1,1) NOT NULL,
3.  [Name] [nvarchar](20) NOT NULL,
4.  [Sex] [bit] NOT NULL,
5.  [Age] [int] NOT NULL,
6.  [Tel] [varchar](32) NULL,
7.  [Email] [nvarchar](50) NOT NULL
8. ) ON [AgePS]([Age])

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

6.查看每分区的记录数

 

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

7.查看记录所在分区

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

8.修改分区方案

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

1. ALTER PARTITION SCHEME [AgePS] NEXT USED [primary]

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

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

1. ALTER PARTITION FUNCTION AgePF() SPLIT RANGE(20)

在分区方案或分区函数上点右键，创建分区函数或分区方案的create脚本就可以看到:

1. CREATE PARTITION FUNCTION [AgePF](int) AS RANGE LEFT FOR VALUES (20, 30, 40)
2. CREATE PARTITION SCHEME [AgePS] AS PARTITION [AgePF] TO ([PRIMARY], [PRIMARY], [SecondFG], [PRIMARY])

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

 

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

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

1. ALTER PARTITION FUNCTION AgePF() MERGE RANGE(30)

以上删除分界值 30 ，包含 30 值的分区上的数据将被转移到 30 值相邻的分区上，然后在分区方案中删除包含30 值这个分区所对应的文件组。
在分区方案或分区函数上点右键，创建分区函数或分区方案的create脚本就可以看到:

1. CREATE PARTITION SCHEME [AgePS] AS PARTITION [AgePF] TO ([PRIMARY], [SecondFG], [PRIMARY])
2. CREATE PARTITION FUNCTION [AgePF](int) AS RANGE LEFT FOR VALUES (20, 40)

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