sql server表分区【最佳实践】

【1】创建表分区

未分区的表，相当于只有一个分区，只能存储在一个FileGroup中；对表进行分区后，每一个分区都存储在一个FileGroup，或分布式存储在不同的FileGroup中。对表进行分区的过程，实际上是将逻辑上完整的一个表，按照特定的字段拆分成多个分区，分散到相同或不同的FileGroup中，每一个部分叫做表的一个分区（Partition），一个分区实际上是一个独立的，内部的物理表。也就是说，分区表在逻辑上是一个表，而在物理上是多个完全独立的表。

分区（Partition）的特性是：

每一个Partition在FileGroup中都独立存储，分区之间是相互独立的
每一个parititon都属于唯一的表对象，
每一个Partition 都有唯一的ID，
每一个Partition都有一个编号（Partition Number），同一个表的分区编号是唯一的，从1开始递增；

当表分区后，加锁的粒度从表级别降低到分区级别，这使得对一个分区执行更新操作，同时不会影响另一个分区的读取操作。因此，分区可以降低并发查询系统产生死锁和阻塞的概率，提高数据操作的并发度。在创建表时，使用On 子句指定table存储的逻辑位置：

ON filegroup | "default" ：表示逻辑存储位置是单一的FileGroup；
ON partition_scheme_name ( partition_column_name ) ：表示逻辑存储位置是分区架构，按照partition_column将table拆分成多个partition，每一个partition都存储在一个指定的Filegroup中；

CREATE TABLE  schema_name . table_name 
(  <column_definition>  )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] 
[ WITH ( <table_option> [ ,...n ] ) ]

从存储空间来理解分区，Partition实际上是表的一部分逻辑存储空间。未分区表的逻辑存储位置是FileGroup，分区表的逻辑存储位置是Partition Scheme，但是，FileGroup指定一个特定的逻辑存储位置，而Partition Scheme是分布式的，能够将数据分布式存储到不同的FileGroup中去。

跟逻辑存储空间相对应的是物理存储空间，物理存储空间是由File指定的，FileGroup是File的集合，每一个File都属于唯一的FileGroup。将table的存储空间拆分到不同的FileGroup中，将table的物理存储空间分布到不同的File中，只不过，不再是某一个FileGroup。实际存储数据的文件仍然是File。

在SQL Server中，File Group和Partition Scheme统称为Data Sapce（数据空间），默认的Data Space是Primary，即主文件组。

Step0，准备工作：构建文件组和文件

--添加文件组
alter database testSplit add filegroup db_fg1

--添加文件到文件组
alter database testSplit add file 
(name=N'ById1',filename=N'J:Work数据库dataById1.ndf',size=5Mb,filegrowth=5mb)
to filegroup db_fg1

一，新建分区表分为三步

Step1，创建分区函数

分区函数的作用是提供分区字段的类型和分区的边界值，进而决定分区的数量

CREATE PARTITION FUNCTION [pf_int](int) 
AS RANGE LEFT 
FOR VALUES (10, 20)

分区函数pf_int 的含义是按照int类型分区，分区的边界值是10，20，left表示边界值属于左边界。两个边界值能够分成三个分区，别是(-infinite，10],（10，20],(20,+infinite)。

Step2，创建分区架构（Scheme）

分区架构的作用是为Parition分配FileGroup，在逻辑上，Partition Scheme和FileGroup是等价的，都是数据存储的逻辑空间，只不过Partition Scheme指定的是多个FileGroup。

CREATE PARTITION SCHEME [ps_int] 
AS PARTITION [pf_int] 
TO ([PRIMARY], [db_fg1], [db_fg1])

不管是在不同的FileGroup中，还是在相同的FileGroup中，分区都是独立存储的。

分区scheme的所有分区都存储到相同的文件组中：

CREATE PARTITION SCHEME [ps_int] 
AS PARTITION [pf_int] 
ALL TO ([PRIMARY])

Step3，新建分区表

新建分区表，实际上是在创建Table时，使用on子句指定数据存储的逻辑位置是分区架构（Partition Scheme）

create table dbo.dt_test
(
    ID int,
    code int
)
on [ps_int] (id)

查看分区编号（Partition Number）

分区编号（Partition Number）从1开始，从最左边的分区向右依次递增+1，边界值最小的分区编号是1，

例如，对于以下分区函数：

CREATE PARTITION FUNCTION pf_int_Left (int)
AS 
RANGE LEFT 
FOR VALUES (10,20);

分区的边界值（Boundary Value）是10，20，边界值属于左边界（Range Left），该分区函数 pf_int_Left 划分了三个分区（Partition），范围区间是：(-infinite,10], (10,20], (20,+infinite)，（小括号表示不包括边界值，中括号表示包括边界值），系统分配的分区编号分别是：1，2，3。用户可以通过使用$Partition函数 查看分区编号，调用语法格式是：

$Partition.Partition_Function(Partition_Column_Value)

例如，通过$Partition函数 查看分区列值为21时，该行数据所在的分区编号：

select $Partition.pf_int_left(21)

由于分区列值是21，属于范围(20,+infinite)，因此分区编号是：3。

【2】对现有表分区

在SQL Server中，普通表可以转化为分区表，而分区表不能转化为普通表，普通表转化成分区表的过程是不可逆的，将普通表转化为分区表的方法是：

在分区架构（Partition Scheme）上创建聚集索引，就是说，将聚集索引分区。

数据库中已有分区函数（partition function）和分区架构（Partition scheme）：

-- create parition function
CREATE PARTITION FUNCTION pf_int_Left (int)
AS RANGE LEFT 
FOR VALUES (10,20);

--determine partition number
select $Partition.pf_int_left(21)

CREATE PARTITION SCHEME PS_int_Left
AS PARTITION pf_int_Left
TO ([primary], [primary], [primary]);

如果在普通表上存在聚集索引，并且聚集索引列是分区列，那么重建聚集索引，就能使表转化成分区表。聚集索引的创建有两种方式：使用clustered 约束（primary key 或 unique约束）创建，使用 create clustered index 创建。

【2.1】在分区架构（Partition Scheme）上，创建聚集索引

如果聚集索引是使用 create clustered index 创建的，并且聚集索引列就是分区列，使普通表转换成分区表的方法是：删除所有的 nonclustered index，在partition scheme上重建clustered index

1，表dbo.dt_partition的聚集索引是使用 create clustered index 创建的，

create table dbo.dt_partition
(
ID int,
Code int
)

create clustered index cix_dt_partition_ID 
on dbo.dt_partition(ID)

2，从系统表Partition中，查看该表的分区只有一个

select *
from sys.partitions p 
where p.object_id=object_id(N'dbo.dt_partition',N'U')

3，使用partition scheme，重建表的聚集索引

create clustered index cix_dt_partition_ID 
on dbo.dt_partition(ID)
with(drop_existing=on)
on PS_int_Left(ID)

4，重建聚集索引之后，表的分区有三个

select *
from sys.partitions p 
where p.object_id=object_id(N'dbo.dt_partition',N'U')

【2.2】如果表的聚集索引是使用Primary key clustered 来创建，并且primary key 就是分区列

在SQL Server中，不能修改约束，将普通表转换成分区表，有两种方式来实现

（1）第一种方式是：在删除clustered constraint 时，将数据移动到分区scheme上；

（2）第二种方式，删除clustered constraint，在分区scheme上重建clustered constraint。

1，在删除clustered 约束时，将数据移动到分区scheme上

使用 alter table drop constraint 命令，在删除聚集索引时，将数据移动到指定的Partition Scheme上，此时该表变成分区的堆表：

ALTER TABLE schema_name . table_name
DROP [ CONSTRAINT ]  constraint_name  
[ WITH ( MOVE TO { partition_scheme_name(partition_column_name ) | filegroup | [default] } )]

move to 选项的作用是将Table移动到新的Location中，如果新的location 是partition scheme，那么在删除clustered 约束时，SQL Server将表数据移动到分区架构中，这种操作和使用 create table on partition scheme创建分区表的作用相同。

create table dbo.dt_partition_pk
(
ID int not null constraint pk__dt_partition_ID primary key clustered ,
Code int not null
)

alter table dbo.dt_partition_pk
drop constraint pk__dt_partition_ID
with( move to PS_int_Left(ID))

2，删除clustered 约束，在partition scheme上重建clustered 约束

create table dbo.dt_partition_pk
(
ID int not null constraint pk__dt_partition_ID primary key clustered ,
Code int not null
)

alter table dbo.dt_partition_pk
drop constraint pk__dt_partition_ID

alter table  dbo.dt_partition_pk
add constraint pk__dt_partition_ID primary key clustered(ID)
on PS_int_Left(ID);

【2.3】将堆表转换成分区表

使堆表转换成分区，只需要在堆表上创建一个分区的clustered index

create table dbo.dt_partition_heap
(
ID int not null,
Code int not null
)

create clustered index cix_partition_heap_ID
on dbo.dt_partition_heap(ID)
on PS_int_Left(ID)

【2.4】普通表=》分区表，不可逆

普通表转化成分区表的过程是不可逆的，普通表能够转化成分区表，而分区表不能转化成普通表。

普通表存储的Location是FileGroup，分区表存储的Location是Partition Scheme，在SQL Server中，存储表数据的Location叫做Data Space。

通过在Partition Scheme上创建Clustered Index ，能够将已经存在的普通表转化成partition table，但是，将Clustered index删除，表仍然是分区表，转化过程（将普通表转换成分区表）是不可逆的；

一个Partition Table 是不能转化成普通表的，即使通过合并分区，使Partiton Table 只存在一个Partition，这个表的仍然是Partition Table，这个Table的Data Space 是Partition Scheme，而不会转化成File Group。

从 sys.data_spaces 中查看Data Space ，共有两种类型，分别是FG 和 PS。

FG是File Group，意味着数据表的数据存储在File Group分配的存储空间，一个Table 只能存在于一个FileGroup中。PS 是Partition Scheme，意味着将数据分布式存储在不同的File Groups中，存储数据的File Group是根据Partition column值的范围来分配的。对于分区表，SQL Server从指定的File Group分配存储空间，虽然一个Table只能指定一个Partition Scheme，但是其数据却分布在多个File Groups中，这些File Groups由Partition Scheme指定，可以相同，也可以不同。

查看Table的Data Space，通过索引的data_space_id 字段来查看各个索引（聚集索引是表本身）数据的存储空间：

select o.name as TableName,o.type_desc,
    i.name as IndexName,
    i.index_id,i.type_desc,i.data_space_id,
    ds.name as DataSpaceName,ds.type_desc
from sys.indexes i
inner join sys.objects o
    on o.object_id=i.object_id
inner join sys.data_spaces ds 
    on i.data_space_id=ds.data_space_id
where i.object_id=object_id(N'[dbo].[dt_test_partition]') 
and i.index_id=0

在分区之前，查看Data_space是Name是 Primary File Group

在分区之后，查看Table的 Data Space 是ps_int Partition Scheme

目前无法将Table的Data Space 转化成FG

【3】分区切换

在SQL Server中，对超级大表做数据归档，使用select和delete命令是十分耗费CPU时间和Disk空间的；

SQL Server必须记录相应数量的事务日志，而使用switch操作归档分区表的老数据，十分高效，switch操作不会移动数据，只是做元数据的置换；

因此，执行分区切换操作的时间是非常短暂的，几乎是瞬间完成，但是，在做分区切换时，源表和靶表必须满足一定的条件：

表的结构相同：列的数据类型，可空性（nullability）相同；
索引结构必须相同：索引键的结构，聚集性，唯一性，列的可空性必须相同；
- 主键约束：如果源表存在主键约束，那么靶表必须创建等价的主键约束；
- 唯一约束：唯一约束可以使用唯一索引来实现；
- 索引键的结构：索引键的顺序，包含列，唯一性，聚集性都必须相同；
存储的数据空间（data space）相同：源表和靶表必须创建在相同的FileGroup或Partition Scheme上；

分区切换是将源表中的一个分区，切换到靶表（target_table）中，靶表可以是分区表，也可以不是分区表，switch操作的语法是：

ALTER TABLE schema_name . table_name 
SWITCH [ PARTITION source_partition_number_expression ]
TO target_table  [ PARTITION target_partition_number_expression ]

【3.1】创建示例数据

-- create parition function
create partition function pf_int_left (int)
as range left 
for values (10,20);

--create partition scheme
create partition scheme ps_int_left
as 
partition pf_int_left
all to ([primary]);

--create partitioned table
create table dbo.dt_partition
(
ID int null,
Code int null
)
on ps_int_left (id)

--Create staging table
create table dbo.dt_SwitchStaging
(
ID int null,
Code int null
)
on [primary]

创建靶表 dt_SwitchStaging，用于存储分区表的数据

【3.2】源表和目标表的结构必须相同

1，数据列的可空性必须相同（nullability）

2，数据列的数据类型必须相同

1，数据列的可空性必须相同（nullability）

由于靶表的ID列是非空的（not null），源表的ID列是可空的（null），可空性不同，在切换分区时，SQL Server会抛出错误消息：

alter table dbo.dt_SwitchStaging
alter column ID int not null;

--swith partition 2 to staging table
alter table dbo.dt_partition
switch partition 2 
to dbo.dt_SwitchStaging

ALTER TABLE SWITCH statement failed because column 'ID' does not have the same nullability attribute in tables 'dbo.dt_partition' and 'dbo.dt_SwitchStaging'.

2，数据列的数据类型必须相同

在执行分区切换时，源表和靶表的数据类型必须相同，即使数据类型相兼容，SQL Server会抛出错误消息：

alter table  dbo.dt_SwitchStaging
alter column ID bigint null

ALTER TABLE SWITCH statement failed because column 'ID' has data type int in source table 'dbo.dt_partition' which is different from its type bigint in target table 'dbo.dt_SwitchStaging'.

【3.3】隐式的Check约束，实现分区的可空属性

分区列（Partition Column）允许为NULL，SQL Server在分区时，将NULL值作为最小值，存储在最左边的第一个分区中，其Partition Number是1。

Any data with a NULL in the partition column will reside in the leftmost partition. NULL is considered smaller than the minimum value of the data type’s values.

分区函数（Partition Function）定义分区列（Partition Column）在每一个分区的取值区间（Value Range），在SQL Server内部，取值区间是使用Check约束来实现的，每一个Partition都有一个check 约束，用于限定Partition column的取值范围：

Partition Number=1，Partition column允许存在null；
其他Partition，Partition column不允许存在null；

对于Unknown值，Check约束认为逻辑结果是True，例如，check(ID>1 and ID<10)，如果ID=Null，那么表达式ID>1 and ID<10 返回Unknown（或null），但是，Check约束返回的结果是True，即不违反check约束。

【3.4】表的索引结构必须相同，唯一性和聚集性也必须相同

在执行分区切换时，表的索引结构，唯一性和聚集性必须相同，在SQL Server中，使用unique index 实现unique 约束的唯一性。

1，索引的聚集性

2，唯一约束

3，主键约束

1，索引的聚集性

在分区表上创建一个聚集索引（clustered index），在切换分区时，SQL Server抛出错误信息，要求靶表必须创建聚集索引

--create clustered index
create clustered index cix_dt_partition_ID
on dbo.dt_partition(ID)

ALTER TABLE SWITCH statement failed. The table 'dbo.dt_partition' has clustered index 'cix_dt_partition_ID' while the table 'dbo.dt_SwitchStaging' does not have clustered index.

2，唯一约束

在分区表上创建唯一聚集约束（unique clustered），在切换分区时，SQL Server抛出错误消息，要求靶表必须创建唯一索引

alter table dbo.dt_partition
add constraint UQ__dt_partition_ID_Code
unique clustered(ID,Code)

ALTER TABLE SWITCH statement failed. The table 'dbo.dt_partition' has clustered index 'UQ__dt_partition_ID_Code' while the table 'dbo.dt_SwitchStaging' does not have clustered index.

Workaround1：在靶表上创建唯一聚集索引（unique clustered），而不是创建unique clustered 约束，switch 成功；

--create unique clustered index
create unique clustered index ucix_dt_SwitchStaging_ID_Code
on dbo.dt_SwitchStaging(ID,Code)

Workaround2：在靶表上创建unique clustered 约束，switch 成功；

3，主键约束

在创建Primary key 约束时，主键列是不可空的

--drop table
drop table dbo.dt_partition
go
drop table dbo.dt_SwitchStaging
GO
--create partitioned table
create table dbo.dt_partition
(
ID int not null,
Code int null,
)
on PS_int_Left (ID)
go
--Create staging table
create table dbo.dt_SwitchStaging
(
ID int not null,
Code int null
)
on [primary]
go

为分区表创建主键约束，使用唯一聚集索引（unique clustered）实现，跟唯一聚集约束的唯一区别是唯一约束列允许为NULL

alter table dbo.dt_partition
add constraint PK__dt_partition_ID
primary key clustered(ID)

将分区表的第二个分区切换到靶表，SQL Server抛出错误信息，要求靶表必须创建唯一聚集索引，注意，不是创建聚集主键；

--swith partition 2 to staging table
alter table dbo.dt_partition
switch partition 2 
to dbo.dt_SwitchStaging

ALTER TABLE SWITCH statement failed. The table 'dbo.dt_partition' has clustered index 'PK__dt_partition_ID' while the table 'dbo.dt_SwitchStaging' does not have clustered index.

在靶表上创建唯一聚集索引，在执行分区切换时，SQL Server抛出错误消息：没有等价的索引，这是因为聚集主键创建的索引是唯一的，聚集的，非空的，而唯一聚集索引是唯一的，聚集的，可空的，两者不是完全等价的。

--create unique clustered index
create unique clustered index cix_dt_SwitchStaging_ID
on dbo.dt_SwitchStaging(ID)

ALTER TABLE SWITCH statement failed. There is no identical index in source table 'dbo.dt_partition' for the index 'cix_dt_SwitchStaging_ID' in target table 'dbo.dt_SwitchStaging' .

在靶表上创建聚集主键，switch成功

--add primary key clustered constraint
alter table dbo.dt_SwitchStaging
add constraint PK__dt_SwitchStaging_ID
primary key clustered(ID)

【3.5】交换分区：总结

在执行分区操作时，要求源表和靶表必须满足：

表的结构相同：列的数据类型，可空性（nullability）相同；
索引结构必须相同：索引键的结构，聚集性，唯一性，列的可空性必须相同；
- 主键约束：如果源表存在主键约束，那么靶表必须创建等价的主键约束；
- 唯一约束：唯一约束可以使用唯一索引来实现；
- 索引键的结构：索引键的顺序，包含列，唯一性，聚集性都必须相同；
存储的数据空间（data space）相同：源表和靶表必须创建在相同的FileGroup或Partition Scheme上；

【4】表分区的T-SQL查询

【4.1】分区函数、分区架构

--查看分区函数
select * from sys.partition_functions

--查看分区架构
select * from sys.partition_schemes

【4.2】查看分区表每个分区有多少行

select convert(varchar(50), ps.name ) as partition_scheme, 
p.partition_number, 
convert(varchar(10), ds2.name ) as filegroup, 
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary, 
str(p.rows, 9) as rows 
from sys.indexes i 
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id 
join sys.destination_data_spaces dds 
on ps.data_space_id = dds.partition_scheme_id 
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id 
join sys.partitions p on dds.destination_id = p.partition_number 
and p.object_id = i.object_id and p.index_id = i.index_id 
join sys.partition_functions pf on ps.function_id = pf.function_id 
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id 
and v.boundary_id = p.partition_number - pf.boundary_value_on_right 
WHERE i.object_id = object_id('crm.EmailLog') 
and i.index_id in (0, 1) 
order by p.partition_number

SELECT DISTINCT  
       t.name                            AS TableName  
       ,ps.name                          AS PSName  
       ,fg.name                          AS FileGroupName  
       ,f.name                           AS [FileName]  
       ,f.physical_name                  AS [FilePhysicalName]  
       ,dds.destination_id                AS PartitionNumber  --去除注释即可显示文件的分区数  
FROM   sys.tables                        AS t  
INNER JOIN sys.indexes            AS i  
ON  (t.object_id = i.object_id)  
INNER JOIN sys.partition_schemes  AS ps  
ON  (i.data_space_id = ps.data_space_id)  
INNER JOIN sys.destination_data_spaces AS dds  
ON  (ps.data_space_id = dds.partition_scheme_id)  
INNER JOIN sys.filegroups         AS fg  
ON  dds.data_space_id = fg.data_space_id  
INNER JOIN sys.database_files f  
ON  f.data_space_id = fg.data_space_id

【参考文档】

水平分区：https://www.cnblogs.com/gered/p/8856585.html

文章转自：

【1】新建分区表：https://www.cnblogs.com/ljhdo/p/5016007.html

【2】对现有表分区：https://www.cnblogs.com/ljhdo/p/5036346.html

【3】分区切换：https://www.cnblogs.com/ljhdo/archive/2016/12/01/5040150.html