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 連接起來創建一個視圖，這個視圖就形成啦分區視同。

很簡單的，這里我主要是說分區表，就不說分區視圖啦。。

【最佳實踐參考】

http://www.rzrgm.cn/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約束一致的表>