SQL SERVER分区具体例子详解

建立分区方案

这个分区函数将分为6个文件组

CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION [PartitionFunc] TO ([Y2015], [Q1],[Q2],[Q3],[Q4],[PRIMARY])

建立好的分区函数和分区方案如下：

建立分区索引完成分区

分区索引必须是聚集索引，我们建标时用SQL里的主键设置会自动将ID设置为聚集索引这里我们需要把原先的主键改为分聚集索引，在建立分区索引。

CREATE CLUSTERED INDEX [ClusteredIndex_CreateDate] ON [dbo].[SchemTest]
(
    [CreateDate]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartitionScheme]([CreateDate])

这样表分区就完成了。

查询分区中的数据

我们可以查下在不同分区中的数据，语句如下：

select $PARTITION.PartitionFunc(CreateDate) as 分区编号,count(ID) as 记录数 from SchemTest group by $PARTITION.PartitionFunc(CreateDate)  

select * from SchemTest where $PARTITION.PartitionFunc(CreateDate)=1  

这样查询所有2015年的数据。

分区新增和合并

现在 2015年的数据在2015文件组，2016年数据在4个季度的文件组，2017年数据在Primary的文件组，现在要将2016年的数据放在新增的2016文件组，4个季度的文件组放2017年的数据，Primary放2018年后的。

1.新建2016的文件组

2.分区合并
先将所有季度文件组都合并，这样2017年数据之前都在2015文件组

ALTER PARTITION FUNCTION PartitionFunc() MERGE RANGE (N‘2016-01-01T00:00:00‘);
ALTER PARTITION FUNCTION PartitionFunc() MERGE RANGE (N‘2016-04-01T00:00:00‘);  
ALTER PARTITION FUNCTION PartitionFunc() MERGE RANGE (N‘2016-07-01T00:00:00‘);  
ALTER PARTITION FUNCTION PartitionFunc() MERGE RANGE (N‘2016-10-01T00:00:00‘);  

可以在分区方案上查看创建SQL语句，这时的分区方案已经更改为：

CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION [PartitionFunc] TO ([Y2015], [PRIMARY])

3.分区新增

首先将2016年的数据放在Y2016文件组

--选择文件组
ALTER PARTITION SCHEME PartitionScheme  
NEXT USED [Y2016] ;
--修改分区函数  
ALTER PARTITION FUNCTION PartitionFunc()  
SPLIT RANGE (N‘2016-01-01T00:00:00.000‘) ;

同理将2017年的数据分别放在2017年的各个季度中

ALTER PARTITION FUNCTION PartitionFunc()  MERGE RANGE (N‘2017-01-01T00:00:00‘);
ALTER PARTITION SCHEME PartitionScheme  NEXT USED [Q1] ;
ALTER PARTITION FUNCTION PartitionFunc()  SPLIT RANGE (N‘2017-01-01T00:00:00.000‘) ;
ALTER PARTITION SCHEME PartitionScheme  NEXT USED [Q2] ; 
ALTER PARTITION FUNCTION PartitionFunc()  SPLIT RANGE (N‘2017-04-01T00:00:00.000‘) ;
ALTER PARTITION SCHEME PartitionScheme  NEXT USED [Q3] ;
ALTER PARTITION FUNCTION PartitionFunc()  SPLIT RANGE (N‘2017-07-01T00:00:00.000‘) ;
ALTER PARTITION SCHEME PartitionScheme  NEXT USED [Q4] ;
ALTER PARTITION FUNCTION PartitionFunc()  SPLIT RANGE (N‘2017-10-01T00:00:00.000‘) ;
ALTER PARTITION SCHEME PartitionScheme  NEXT USED [PRIMARY] ;
ALTER PARTITION FUNCTION PartitionFunc()  SPLIT RANGE (N‘2018-01-01T00:00:00.000‘) ;

现在查看分区函数和分区方案的创建语句如下：

CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION [PartitionFunc] TO ([Y2015], [Y2016], [Q1], [Q2], [Q3], [Q4], [PRIMARY])
CREATE PARTITION FUNCTION [PartitionFunc](datetime) AS RANGE RIGHT FOR VALUES (N‘2016-01-01T00:00:00.000‘, N‘2017-01-01T00:00:00.000‘, N‘2017-04-01T00:00:00.000‘, N‘2017-07-01T00:00:00.000‘, N‘2017-10-01T00:00:00.000‘, N‘2018-01-01T00:00:00.000‘)

分区记录如下：

如果分区变动比较大不推荐用合并和删除的方法，因为容易出错，如果分12个月建议像下面一样，先将分区表转换为普通表，再把普通表分区。

将分区表转换成普通表

1.删除分区索引

删除分区索引后，并没有编程普通表

2.在原有分区索引字段，建立普通索引

CREATE CLUSTERED INDEX [IX_SchemTest]  ON SchemTest(CreateDate) ON [Primary]

SQL SERVER分区具体例子详解

标签：--   count   测试表   知识   表分区   函数   有助于   existing   sch