背景：

在前面两篇博文《SQL Server 大数据管理——数据归档(主文件备份)》、《SQL Server 大数据管理——数据归档(段落备份)》中，表分区在其中起到了主要作用，本文将介绍分区的实现及表分区的相关属性和操作。

一. 创建分区文件组/文件
--创建分区文件组
alter database test add filegroup test2015
alter database test add filegroup test2016
alter database test add filegroup test2017
alter database test add filegroup test2018
--创建分区文件
alter database test
add file(name='test2015'
,filename='D:\DB\testPartion\test2015.ndf'
,size=1mb
,filegrowth=1mb)
to filegroup test2015;
alter database test
add file(name='test2016'
,filename='D:\DB\testPartion\test2016.ndf'
,size=1mb
,filegrowth=1mb)
to filegroup test2016;
alter database test
add file(name=N'test2017'
,filename=N'D:\DB\testPartion\test2017.ndf'
,size=1mb
,filegrowth=1mb)
to filegroup test2017
alter database test
add file(name=N'test2018'
,filename=N'D:\DB\testPartion\test2018.ndf'
,size=1mb
,filegrowth=1mb)
to filegroup test2018
二. 创建分区函数
--创建分区函数
create partition function f\_TestDate(datetime)
as range right for values('2016-01-01','2017-01-01','2018-01-01')
注意：

1. F\_TestDate 为分区函数名，分区的字段是datetime类型


2. Right 表示该分区包含右边界值，上面分区函数会把数据分为

小于2016.1.1
大于等于2016.1.1 且小于2017.1.1
大于等于2017.1.1 且小于2018.1.1
大于等于2018.1.1
四个分区，若把right换为left，则分区变为

小于等于2016.1.1
大于2016.1.1 且小于等于2017.1.1
大于2017.1.1 且小于等于2018.1.1
大于2018.1.1
三. 创建分区方案
--创建分区方案
create partition scheme s\_TestDate
as partition f\_TestDate to (test2015,test2016,test2017,test2018)
注意：

1. 分区方案是建立在分区函数的基础上的，所以先建立分区函数，再建立分区方案

2. 分区个数比分区边界值多1

3. 本分区方案每个分区建在一个文件组上，当然也可以把所有分区建立在一个文件组上

--创建分区方案,所有分区均建立在主文件组上
create partition scheme s\_TestDate
as partition f\_TestDate all to ([primary])
两种方案的优劣待续……

四. 创建分区表
4.1 新建分区表
create table tradelog
(
ID int,
productID int,
tradedate datetime
) on s\_TestDate(tradedate)
注：创建分区表，用的是s\_TestDate分区方案名称

4.2 对已有表分区
若表上没有聚集索引，可以通过创建聚集索引，对表进行分区

CREATE CLUSTERED INDEX [CLI\_tn\_TestDate] ON [dbo].[tradelog\_noClusterIndex]
(
[tradedate]
) ON [s\_TestDate]([tradedate])

--如果不需要聚集索引,删除聚集索引
DROP INDEX [CLI\_tn\_TestDate] ON [dbo].[tradelog\_noClusterIndex]
若表上已有聚集索引，删除聚集索引，再通过上面脚本重建聚集索引。或者通过WITH（DROP\_EXISTING=ON）重建聚集索引，脚本如下：

CREATE CLUSTERED INDEX [CLI\_tn\_TestDate] ON [dbo].[tradelog\_noClusterIndex]
(
[tradedate]
)WITH (DROP\_EXISTING = ON) ON [s\_TestDate]([tradedate])
五. 增加分区
增加分区的方法是将某个现有的分区“拆分”为两个分区并重新定义新分区的边界。

--向分区表插入1000W行数据
DECLARE @max AS INT, @rc AS INT;
SET @max = 10000000;
SET @rc = 1;
INSERT INTO tradelog(id,productID,tradedate) VALUES(1,1,'2014-01-01');
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.tradelog(id,productID,tradedate) SELECT id + @rc,id + @rc+1,DATEADD(mi,id,tradedate) FROM dbo.tradelog;
SET @rc = @rc * 2;
END
INSERT INTO dbo.tradelog (id,productID,tradedate)
SELECT id + @rc,id + @rc+1,DATEADD(mi,id,tradedate) FROM dbo.tradelog WHERE id + @rc <= @max;
go

--查看分区表的现状
;with cte as
(select
object\_id
,OBJECT\_NAME(i.object\_id) tableName
,i.index\_id
,dds.partition\_scheme\_id
,dds.destination\_id as partition\_number
,fg.groupid
,fg.groupname
,f.fileid
,f.name
,f.filename
--,p.partition\_id
--,p.rows
from sys.destination\_data\_spaces dds,sys.indexes i,sys.sysfilegroups fg,sys.sysfiles f
where dds.partition\_scheme\_id=i.data\_space\_id
and dds.data\_space\_id=fg.groupid
and fg.groupid=f.groupid
)
,cte1 as(
select
ps.data\_space\_id as partition\_scheme\_id
,ps.name partiton\_schemes\_name
,pf.name partition\_function\_name
,pf.function\_id
--,prv.value AS BoundaryValue
from sys.partition\_schemes ps ,sys.partition\_functions pf
where ps.function\_id=pf.function\_id
--and pf.function\_id=prv.function\_id
)
select cte.tableName,cte.groupname,cte.name,cte.filename
,cte.partition\_number,cte1.partiton\_schemes\_name,cte1.partition\_function\_name,p.rows
,prv.boundary\_id,prv.value BoundaryValue
from cte
inner join cte1 on cte.partition\_scheme\_id=cte1 .partition\_scheme\_id
left join sys.partition\_range\_values prv on cte1.function\_id=prv.function\_id and cte.partition\_number=prv.boundary\_id
left join sys.partitions p on cte.object\_id=p.object\_id and cte.index\_id=p.index\_id and cte.partition\_number=p.partition\_number

where
cte.object\_id=OBJECT\_ID('dbo.tradelog','U')

可以看到tradelog表按交易时间列分为4区，分区边界值为16、17、18三年的1月1日，其中

16年以前的数据存在文件test2015上
16年数据存在文件test2016上
17年数据存在文件test2017上
18年及以后的数据存在文件test2018上
现在增加一个分区，将2019以后的数据分开，或者说将原4分区以2019年1月1日为分区边界拆分为两个分区，具体脚本如下：

--创建新分区文件组
alter database test add filegroup test2019
--创建新分区文件
alter database test
add file(name='test2019'
,filename='D:\DB\testPartion\test2019.ndf'
,size=1mb
,filegrowth=1mb)
to filegroup test2019;
alter partition scheme s\_TestDate
next used test2019

alter partition function f\_TestDate()
split range('2019-01-01 0:00:00')
重新执行分区状态查询脚本，结果如下图：

可以看到，源第4分区被拆分为两个分区，并且2019年以后的数据被移动到新的文件test2019上。

可以有这样一个结论，新增分区后，新增的边界值，到下一个分区边界值之间的数据，将被移动到新的文件上，无论是拆分第1个分区，还是拆分中间的某个分区（如拆分第4个分区），如下图：

六. 合并分区
减少分区的方法是将两个分区的边界“合并”成一个。 减少分区操作将重新填充一个分区而不对另一个分区进行分配。

--分区合并
alter partition function f\_TestDate()
merge range('2018-07-01 0:00:00')

分区合并的数据移动方向刚好和增加分区的方向相反，分区合并后，将合并分界点的后一个分区数据移动到前一个分区的文件中。这个结论在数据自动归档中将极为有用，因为数据归档最后一步是将合并后的空文件、文件组回收，这样就可以确定回收的文件名

七. 分区数据移到普通表
create table tradelog\_partition1
(
ID int,
productID int,
tradedate datetime
) on test2015
alter table tradelog switch partition 1 to tradelog\_partition1
把分区表的某个分区数据转移到普通表，要求

1. 普通表必须和对应的分区在同一个文件组下

2. 普通表和分区表结构相同，包括字段、数据类型、数据长度、索引等

分区表上在tradedate上有聚集索引，但普通表tradelog\_partition1上没有建聚集索引，执行上述脚本就会报如下错误：

八. 普通表数据移到某一分区
alter table tradelog\_partition1 switch to tradelog partition 1

在tradelog\_partition1的tradedate上创建聚集索引，重新执行上面的脚本，又报了如下错误

What happen？？这是因为分区1上有CHECK日期要在2014到2016之间，而tradelog\_partition1上没有这个检查，所以，在表上加上如下检查：

ALTER TABLE dbo.tradelog\_partition1
ADD CONSTRAINT TradeDate\_Switch\_CHECK CHECK
(TradeDate >= CONVERT(DATE,'2014-01-01') AND TradeDate < CONVERT(DATE,'2016-01-01')
AND TradeDate IS NOT NULL);
GO
再执行移动数据，数据又重新移回到分区1中
---------------------
作者：三空道人
来源：CSDN
原文：https://blog.csdn.net/zhoujunah/article/details/79744590

标签: 分区, Server, partition, SQL, 数据管理, id, tradelog, tradedate

相关文章推荐

添加新评论，含*的栏目为必填