一步一步在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:disk1test_01.ndf')TO FILEGROUP [test_fg_01];
ALTER DATABASE [test] ADD FILE (NAME='[test_f_02]',FILENAME='c:disk2test_02.ndf')TO FILEGROUP [test_fg_02];
ALTER DATABASE [test] ADD FILE (NAME='[test_f_03]',FILENAME='c:disk3test_03.ndf')TO FILEGROUP [test_fg_03];
ALTER DATABASE [test] ADD FILE (NAME='[test_f_04]',FILENAME='c:disk4test_04.ndf')TO FILEGROUP [test_fg_04];
ALTER DATABASE [test] ADD FILE (NAME='[test_f_05]',FILENAME='c:disk5test_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个分区
)

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])

CREATE PARTITION SCHEME [PartionStruct] AS PARTITION [PartionFunction] TO 
([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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!