sql server表分区(五)

refs:

https://www.cnblogs.com/yaosuc/p/4721572.html

 

普通表转分区表

上面介绍了如何在创建表的时候进行分区，但往往我们需要的是将现有的普通表在数据保留的情况下进行分区。

普通表一般都有主键，同时还是聚集索引。分区是以某个字段为条件进行的，而除了这个字段其他字段是不可以创建聚集索引的。所以需要先删除表中的聚集索引，再新建一个聚集索引。

 

 

--删除主键，自动同时删除索引
alter table newTable drop constraint PK_newTable 

--创建主键，但不创建聚集索引
alter table newTable add constraint PK_newTable
primary key nonclustered --非聚集
(
    id asc
) on [primary]

--然后给我们亲爱的时间创建一个聚集索引
create clustered index CT_newTable on newTable(createTime)
on schemeFenqu(createTime) --并调用分区方案

--然后再查询分区，发现数据保留情况下，已经将数据按规则进行分区了
select $partition.fenqu(createTime) as 分区,count(id) as 数量
from newTable group by $partition.fenqu(createTime)

 

 

添加分区

向上面只分了3个区，而15年以后的都存在第三个分区中，到16年还是会存在这个分区中，这时候需要再新加一个16年的分区。

添加新的分区意味着要新建一个文件组和文件来存放这个分区表，然后在分区方案中用到这个文件组，最后再修改一下分区函数的规则即可。文件组和分区数量要保持一致。

 

 

--创建文件组
alter database webDB add filegroup group2016

--添加数据库文件
alter database webDB add file
(
    name='web2016',
    filename='D:\web2016.ndf',
    size=5mb,
    filegrowth=5mb
)
to filegroup group2016

--修改分区方案
alter partition scheme SchemeFenqu
next used group2016

--修改分区函数
alter partition function fenqu()
split range('2016-01-01')

--添加2016年数据
insert into newTable (name,createTime) values ('16年小明','2016-03-05')

--查看分区及统计
select $partition.fenqu(createTime) as 分区,count(id) as 数量
from newTable group by $partition.fenqu(createTime)

 

删除分区

删除分区就是将分区函数中多余的边界值删除。

如：2013，2014，2015，2016 现在需要将13年和14年进行合并，删除13年的分区。

 

--查看分区及统计
select $partition.fenqu(createTime) as 分区,count(id) as 数量
from newTable group by $partition.fenqu(createTime)
--分区    数量
--1        4
--2        1
--3        3
--4        1

--删掉该边界值
alter partition function fenqu() merge range('2014-01-01')

--再次查询
select $partition.fenqu(createTime) as 分区,count(id) as 数量
from newTable group by $partition.fenqu(createTime)
--分区    数量
--1        5
--2        3
--3        1

 

拆分分区

有的时候某一分区中数据量过大，需要将这个分区再次拆分为多个分区，以加快访问速度。

拆分分区的操作其实与添加分区类似，首先要添加文件组、文件、修改分区方案、修改分区函数(新增一个边界值)。

如：2014，2015，2015年6月份以上一个 6月份一下一个。

 

 

--查看分区及统计
select $partition.fenqu(createTime) as 分区,count(id) as 数量
from newTable group by $partition.fenqu(createTime)
--分区        数量
--1            5
--2            3
--3            1

--创建文件组
alter database webDB add filegroup group2014_2015

--添加数据库文件
alter database webDB add file
(
    name='web2014_2015',
    filename='D:\web2014_2015.ndf',
    size=5mb,
    filegrowth=5mb
)
to filegroup group2014_2015

--修改分区方案
alter partition scheme SchemeFenqu
next used group2014_2015

--修改分区函数
alter partition function fenqu()
split range('2015-06-01')

--查看分区及统计
select $partition.fenqu(createTime) as 分区,count(id) as 数量
from newTable group by $partition.fenqu(createTime)
--分区        数量
--1            5
--2            2
--3            1
--4            1

 

 

分区表转普通表

 

 

--修改分区函数 将边界值都删除
alter partition function fenqu()
merge range('2013-01-01')
alter partition function fenqu()
merge range('2014-01-01')
alter partition function fenqu()
merge range('2015-01-01')
alter partition function fenqu()
merge range('2015-06-01')

select $partition.fenqu(createTime) as 分区,count(id) as 数量
from newTable group by $partition.fenqu(createTime)
--这时只有一个分区了
--分区    数量
--1        9

 

 

这样虽然只有一个分区了，但是查看数据表存储位置，是否进行分区：True，分区数1.

--重新建立聚集索引
create clustered index CT_newTable on newTable(createTime)
    with(drop_existing=on) --如果存在则删除
on [primary]

删除分区索引后，重新建立聚集索引，这时再此查看数据表的存储位置，是否分区：Flase。