sql server表分区【最佳实践】

【0】表分区介绍

（0.1）SQL Server分区介绍

在SQL Server中，数据库的所有表和索引都视为已分区表和索引，默认这些表和索引值包含一个分区；也就是说表或索引至少包含一个分区。SQL Server中数据是按水平方式分区，是多行数据映射到单个分区。已经分区的表或者索引，在执行查询或者更新时，将被看作为单个逻辑实体；简单说来利用分区将一个表数据分多个表来存储，对于大数据量的表，将表分成多块查询，若只查询某个分区数据将降低消耗提高效率。需要注意的是单个索引或者表的分区必须位于一个数据库中。在使用大量数据管理时，SQL Server使用分区可以快速访问数据子集，减少io提高效率。

同时不同分区可以存放在不同文件组里，文件组若能存放在不同逻辑磁盘上，则可以实现io的并发使用以提高效率。如下图所示：

（0.2）SQL Server分区创建概述

创建分区函数：确定分区方式和界点
创建分区架构：将分区函数指定的分区映射到文件组
新建分区表
_{索引分区知识详解}

（0.3）SQL Server分区管理概述

拆分分区（split）
合并分区（merge）
切换分区（switch）
$PARTION

【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\数据库\data\ById1.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.0】全面查看表分区行、索引、文件、界限

SELECT OBJECT_NAME(p.object_id) AS ObjectName,
      i.name                   AS IndexName,
      p.index_id               AS IndexID,
      ds.name                  AS PartitionScheme,   
      p.partition_number       AS PartitionNumber,
      fg.name                  AS FileGroupName,
      prv_left.value           AS LowerBoundaryValue,
      prv_right.value          AS UpperBoundaryValue,
      CASE pf.boundary_value_on_right
            WHEN 1 THEN 'RIGHT'
            ELSE 'LEFT' END    AS Range,
      p.rows AS Rows
FROM sys.partitions                  AS p
JOIN sys.indexes                     AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces                 AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes           AS ps
      ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions         AS pf
      ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces     AS dds2
      ON dds2.partition_scheme_id = ps.data_space_id 
      AND dds2.destination_id = p.partition_number
JOIN sys.filegroups                  AS fg
      ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number 
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
UNION ALL
SELECT
      OBJECT_NAME(p.object_id)    AS ObjectName,
      i.name                      AS IndexName,
      p.index_id                  AS IndexID,
      NULL                        AS PartitionScheme,
      p.partition_number          AS PartitionNumber,
      fg.name                     AS FileGroupName,  
      NULL                        AS LowerBoundaryValue,
      NULL                        AS UpperBoundaryValue,
      NULL                        AS Boundary, 
      p.rows                      AS Rows
FROM sys.partitions     AS p
JOIN sys.indexes        AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces    AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups           AS fg
      ON fg.data_space_id = i.data_space_id
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber

【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

【4.3】查看分区依据列的指定值所在的分区

--查询分区依据列为10000014的数据在哪个分区上
select $partition.bgPartitionFun(2000000)  --返回值是2，表示此值存在第2个分区

【4.4】查看分区表中，每个非空分区存在的行数

--查看分区表中，每个非空分区存在的行数
select $partition.bgPartitionFun(orderid) as partitionNum,count(*) as recordCount
from bigorder
group by  $partition.bgPartitionFun(orderid)

【4.5】查看指定分区中的数据记录

---查看指定分区中的数据记录
select * from bigorder where $partition.bgPartitionFun(orderid)=2

结果：数据从1000001开始到200W结束

【5】拆分/合并分区/数据移动

---增加新的分区你可以将的分区加到新的文件组里面(也可以是使用过的)

alter partition scheme [bgPartitionSchema] -----先为分区方案分配文件组
next used [primary]

（5.1）拆分/增加分区

在分区函数中新增一个边界值，即可将一个分区变为2个。放到最前或者最后来拆分就是新增分区

--分区拆分
alter partition function bgPartitionFun()
split range(N'1500000')  --将第二个分区拆为2个分区

注意：如果分区函数已经指定了分区方案，则分区数需要和分区方案中指定的文件组个数保持对应一致。

（5.2）合并分区

与拆分分区相反，去除一个边界值即可。

--合并分区
alter partition function bgPartitionFun()
merge range(N'1500000')  --将第二第三分区合并

1、ALTER PARTITION FUNCTION 意思是修改分区函数

2、partfunSale()为分区函数名

3、MERGE RANGE意思是合并界限。事实上，合并界限和删除分界值是一个意思。

如上面代码，如果有1000000 1500000 200000，3个分区，那么运行之后，就去掉了1500000，只有1000000 、20000000 分区了

（5.3）分区中的数据移动

你或许会遇到这样的需求，将普通表数据复制到分区表中，或者将分区表中的数据复制到普通表中。

那么移动数据这两个表，则必须满足下面的要求。

字段数量相同，对应位置的字段相同
相同位置的字段要有相同的属性，相同的类型。
两个表在一个文件组中

1.创建表时指定文件组

--创建表
create table <表名> (
  <列定义>
)on <文件组名>

2.从分区表中复制数据到普通表

--将bigorder分区表中的第一分区数据复制到普通表中
alter table bigorder switch partition 1 to <普通表名>

3.从普通标中复制数据到分区表中

这里要注意的是要先将分区表中的索引删除，即便普通表中存在跟分区表中相同的索引。

--将普通表中的数据复制到bigorder分区表中的第一分区
alter table <普通表名> switch to bigorder partition 1

（5.4）分区视图

分区视图是先建立带有字段约束的相同表,而约束不同,例如,第一个表的id约束为0--100W,第二表为101万到200万.....依次类推。

创建完一系列的表之后，用union all 连接起来创建一个视图，这个视图就形成啦分区视同。

很简单的，这里我主要是说分区表，就不说分区视图啦。。

【最佳实践参考】

https://www.cnblogs.com/chhuang/category/639734.html

（五）SQL Server分区自动化案例

【我的最佳实践】

（1）时间分区

代码：现有表转成分区表

--  创建测试数据，测试表  part_test
use test1;
if object_id('part_test' ) is not null
    drop table part_test;
;with t1 as (
select 1 as id,1 as num ,cast('2021-01-01 00:01:01' as datetime) as day_info
union all
select id+1 ,num+1 ,dateadd(day,1,day_info) from t1
where id<=1000000
)
select * into part_test from t1  option(maxrecursion 0)

-- 分区函数
CREATE PARTITION FUNCTION [pf_datetime](datetime) 
AS RANGE LEFT for values(
'2021-01-01' ,
'2022-01-01' ,
'2023-01-01' ,
'2024-01-01' ,
'2025-01-01' ,
'2026-01-01' ,
'2027-01-01' ,
'2028-01-01' ,
'2029-01-01' ,
'2030-01-01' ,
'2031-01-01' ,
'2032-01-01' ,
'2033-01-01' ,
'2034-01-01' ,
'2035-01-01' ,
'2036-01-01' ,
'2037-01-01' ,
'2038-01-01' ,
'2039-01-01' ,
'2040-01-01' ,
'2041-01-01' ,
'2042-01-01' ,
'2043-01-01' ,
'2044-01-01' ,
'2045-01-01' ,
'2046-01-01' ,
'2047-01-01' ,
'2048-01-01' 
);

-- 分区架构 
CREATE PARTITION SCHEME [ps_datetime] 
AS PARTITION [pf_datetime] 
ALL TO ([PRIMARY])


-- 创建聚集索引和耳机索引
create clustered index PIX_id on part_test(id)
create index ix_dayinfo on part_test(day_info)

-- 查看是否还有二级索引 
-- sp_help part_test

-- 删掉二级索引，重建聚集索引并应用分区架构
drop index ix_dayinfo on  part_test

-- 重建聚集索引=》现有表改成分区表，分区列必须是在主键内，比如这里的 day_info 就必须在主键内
create clustered index PIX_id
on dbo.part_test(ID,day_info)
with(drop_existing=on)
on [ps_datetime](day_info)

--创建索引对齐分区索引
create index id_p_num on part_test(num) on [ps_datetime](day_info)
create index id_p_dayinfo on part_test(day_info) on [ps_datetime](day_info)

select * from part_test where day_info='2021-01-11 00:01:01.000'

-- 拆分分区（最末尾）
-- 在分区函数中新增一个边界值，即可将一个分区变为2个。一般边界值默认是 left ；放到最前或者最后来拆分就是新增分区
alter partition function pf_datetime()
split range('2049-01-01')  --将第二个分区拆为2个分区

-- 归档到历史表
alter table bigorder switch partition 1 to <同表结构、默认值、null约束一致的表>