一步一步在sqlserver中创建分区表

在SqlServer2005中新增了分区表的支持，对于一些大数据量的表，我们可以对它进行分区，以便提供更好的性能，下面我一起来感受下详细的分区步骤。

假设要操作的数据库名为test,表名为tb1(id 自增int, insertTime DateTime)，建表语句如下：create tabletb1 (id int identity(1,1) not null, insertTime DateTime not null);

表内保存了2006~2010年共5年的数据，要做5个分区：

 

1、分区键的选择
分区的关键是要选择好分区键，就是在插入数据时，新的数据按什么条件插入到需要的分区，一般而言，分区键一般要满足下面2个条件：
a、常用的检索能保证检索结果在同一个分区内
b、能把数据均匀分布到各个分区
这里如果tb1是报表，主要根据时间来检索数据的话，那么分区键可以用insertTime，根据时间段进行分区
如果tb1是用户表，主要根据id进行数据检索，因为id是自增字段，那么可以根据id对分区数进行取模（比如5个分区就是id%5）

 

2、新建文件组（对应着oracle的表空间）
你需要为数据库创建新的文件组，可以理解为分区，就是数据分别存储到几个文件组中，下面是建5个文件组：

ALTER DATABASE [test] ADD FILEGROUP [test_fg_01];ALTER DATABASE [test] ADD FILEGROUP [test_fg_02];ALTER DATABASE [test] ADD FILEGROUP [test_fg_03];ALTER DATABASE [test] ADD FILEGROUP [test_fg_04];ALTER DATABASE [test] ADD FILEGROUP [test_fg_05];go

 

3、为新建的文件组添加文件，指示插入这些文件组的数据具体对应到哪个物理文件（对应着oracle的表空间下的数据文件）：

-- 下面的sql省略了SIZE ,MAXSIZE,FILEGROWTH 参数的指定，表示按默认值ALTER DATABASE [test] ADD FILE (NAME='[test_f_01]', FILENAME='c:\disk1\test_01.ndf')TO FILEGROUP [test_fg_01];ALTER DATABASE [test] ADD FILE (NAME='[test_f_02]', FILENAME='c:\disk2\test_02.ndf')TO FILEGROUP [test_fg_02];ALTER DATABASE [test] ADD FILE (NAME='[test_f_03]', FILENAME='c:\disk3\test_03.ndf')TO FILEGROUP [test_fg_03];ALTER DATABASE [test] ADD FILE (NAME='[test_f_04]', FILENAME='c:\disk4\test_04.ndf')TO FILEGROUP [test_fg_04];ALTER DATABASE [test] ADD FILE (NAME='[test_f_05]', FILENAME='c:\disk5\test_05.ndf')TO FILEGROUP [test_fg_05];go--一般情况不同的分区放置在不同的磁盘上可以使读的性能更好，我们没有这么多磁盘，这里就用文件夹代替不同的磁盘意思意思

执行完成后查看数据库属性如图所示

 

4、创建分区函数，这个函数的作用是定义分区数据的左右边界
如果用insertTime作为分区键，且每个分区保存1年的数据，那么创建的函数如下：

CREATE PARTITION FUNCTION [PartionFunction](datetime) -- PartionFunction为分区函数的函数名，datetime为分区键的字段类型AS RANGE LEFT FOR VALUES (     -- LEFT表示左边界(即在这个时间点左边),RIGHT表示右边界(即在这个时间点右边)    N'2006-12-31 23:59:59.997',      --小于等于该时间,保存在第1个分区    N'2007-12-31 23:59:59.997',      --小于等于该时间,保存在第2个分区    N'2008-12-31 23:59:59.997',      --小于等于该时间,保存在第3个分区    N'2009-12-31 23:59:59.997',      --小于等于该时间,保存在第4个分区    N'2010-12-31 23:59:59.997'  --小于等于该时间,保存在第5个分区)

注：函数的参数也允许使用函数，例如：DateAdd(ms, -3, '2010-12-31 12:13:14.997')
如果用id%5作为分区键, 那么函数如下（因为对5取模，所以值只有0~4）

CREATE PARTITION FUNCTION [PartionFunction](int)AS RANGE LEFT FOR VALUES (0, 1, 2, 3, 4)

 

5、创建分区架构，这个架构根据上面的函数结果，指示符合该结果的数据要保存到哪个分区

CREATE PARTITION SCHEME [PartionStruct] AS PARTITION [PartionFunction] TO([test_fg_01], [test_fg_02], [test_fg_03], [test_fg_04], [test_fg_05],[PRIMARY])

注1：架构的参数个数必须等于函数的参数个数+1，最后一个使用PRIMARY文件组即可(不符合分区条件的所有数据都会放入最后一个文件组,其实就是默认文件组)
注2：可以不创建文件组，所有参数都使用PRIMARY，这样所有的分区数据都在同一个文件组里，如果所有分区文件都在同一个分区，那么可以不创建文件组，全部使用同一个文件组，比如：

CREATE PARTITION SCHEME [PartionStruct] AS PARTITION [PartionFunction] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]) 

但是显然这样对于提高性能，打破IO瓶颈显然没啥意义了。。。。。。。

 

6、对需要进行分区的表进行分区

a:首先确定tb1没有主键，后面由我们自己来创建（因为分区键必须是主键和聚集索引之一）
b:如果使用insertTime作为分区键，那么把id和insertTime作为主键，并创建索引：

ALTER TABLE tb1 ADD CONSTRAINT PK_tb1    PRIMARY KEY (insertTime, id)    ON PartionStruct(insertTime)go

 

7、分区完成
到这里，我们要做的分区工作已经完成，但是这里还需要做一件事情，检查我们的数据，是不是正确分区了：
执行下面的SQL，可以看到，根据这个分区函数处理后，各个分区储存的数据量，如果每个分区的rows字段数目基本差不多，说明数据的分布还是比较均匀的

select * from sys.partitions where object_id=object_id('tb1')

 

由于没数据所以显示的结果如下：

接下来我们插入一些数据看下是否按照我们的要求进行分区了

insert into tb1(insertTime) values('2005-12-31 23:59:59.997');insert into tb1(insertTime) values('2006-12-31 23:59:59.997');insert into tb1(insertTime) values('2007-11-20 23:59:59.997');insert into tb1(insertTime) values('2008-10-10 23:59:59.997');insert into tb1(insertTime) values('2009-08-25 23:59:59.997');insert into tb1(insertTime) values('2010-01-01 23:59:59.997');insert into tb1(insertTime) values('2012-01-01 23:59:59.997');

然后在查询得到如下结果：

                                                                                                                                                                                                                      ——THE END