Partition：Partiton Scheme是否指定Next Used？

在SQL Server中，為Partition Scheme多次指定Next Used，不會出錯，最後一次指定的FileGroup是Partition Scheme的Next Used，建議，在執行Partition Split操作之前，都要為Partition Scheme指定Next Used。

但是，SQL Server是否提供metadata，查看Partiton Scheme是否指定Next Used FileGroup？答案是系統視圖：sys.destination_data_spaces。如果存在FileGroup被指定為Next Used ，那麼視圖返回的Partition的個數會比Partition Function劃分的分區數量多1個。

一，分析視圖：sys.destination_data_spaces

該視圖返回三列，表示Partition Scheme的每個Partition和FileGroup之間的關係：

• partition_scheme_id ：ID of the partition-scheme that is partitioning to the data space.                  
• destination_id ：ID (1-based ordinal) of the destination-mapping, unique within the partition scheme.     
• data_space_id ：ID of the data space to which data for this scheme's destination is being mapped.

從表的存儲結構來分析這三列的意義：

• partition_scheme_id ：是數據表存儲的空間，該空間不是具體的某個FileGroup。普通的表只有一個分區，只能存儲在單個FileGroup中，但是，通過Partition Scheme，將表數據分割成多個分區，每個分區存儲到指定的FileGroup中，在物理存儲上，每個分區都是分開（separate）存儲的。
• destination_id：是Partition Number，每個分區的編號
• data_space_id：是FileGroupID，分區存儲的FileGroup。

二，測試用例

1，創建分區函數

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

2，創建分區scheme

--create partition scheme
CREATE PARTITION SCHEME PS_int_Left
AS 
PARTITION pf_int_Left
TO ([primary], [primary], [primary]);

3，在split partition之前，必須使用alter partition scheme 指定一個Next Used FileGroup。如果Partiton Scheme沒有指定 next used filegroup，那麼alter partition function split range command 執行失敗，不改變partition scheme。

--split range and add new one boudary value
ALTER PARTITION FUNCTION pf_int_Left ()
split range (30);

Msg 7710, Level 16, State 1, Line 2
Warning: The partition scheme 'PS_int_Left' does not have any next used filegroup. Partition scheme has not been changed.

4，如果檢查 Partiton Scheme是否指定Next Used FileGroup？

使用sys.destination_data_spaces視圖來檢查，該系統視圖返回Partition 和filegroup之間的Mapping關係。如果一個FileGoup被alter partition scheme 標記為next used Filegroup，那麼Partition 的個數會比多Partition function劃分的分區多一個。

select ps.name as PartitionSchemeName,
    ps.data_space_id as PartitionSchemeID,
    pf.name as PartitionFunctionName,
    ps.function_id as PartitionFunctionID,
    pf.boundary_value_on_right,
    dds.destination_id as PartitionNumber,
    dds.data_space_id as FileGroupID
from sys.partition_schemes ps
inner join sys.destination_data_spaces dds
    on ps.data_space_id=dds.partition_scheme_id
inner join sys.partition_functions pf
    on ps.function_id=pf.function_id
where ps.name='PS_int_Left'

上述腳本返回3個partition，說明沒有next used filegroup。

5，使用 alter partition scheme標記 next used filegroup

--alter partition scheme to mark next used filegroup
ALTER PARTITION SCHEME PS_int_Left 
NEXT USED [db_fg1];

查看分區個數

select ps.name as PartitionSchemeName,
    ps.data_space_id as PartitionSchemeID,
    pf.name as PartitionFunctionName,
    ps.function_id as PartitionFunctionID,
    pf.boundary_value_on_right,
    dds.destination_id as PartitionNumber,
    dds.data_space_id as FileGroupID
from sys.partition_schemes ps
inner join sys.destination_data_spaces dds
    on ps.data_space_id=dds.partition_scheme_id
inner join sys.partition_functions pf
    on ps.function_id=pf.function_id
where ps.name='PS_int_Left'

可以看到，多了一個partition，partition number=4，存放的FileGroupID=2。

6，將 FileGroup 取消標記為 next used filegroup

--alter partition scheme to cancel next used filegroup
ALTER PARTITION SCHEME PS_int_Left 
NEXT USED;

7,Merge Range移除FileGroup

--merge range
ALTER PARTITION FUNCTION pf_int_Left ()
merge range (20);

查看Partition Function指定的Boundary Value

select pf.name as PartitionFunctionName,
    pf.function_id,
    pf.type,
    pf.type_desc,
    pf.boundary_value_on_right,
    pf.fanout,
    prv.boundary_id,
    prv.value
from sys.partition_functions pf
inner join sys.partition_range_values prv
    on pf.function_id=prv.function_id
where pf.name='pf_int_Left'

綁定到Partition Scheme的Filegroup如下

select ps.name as PartitionSchemeName,
    ps.data_space_id as PartitionSchemeID,
    pf.name as PartitionFunctionName,
    ps.function_id as PartitionFunctionID,
    pf.boundary_value_on_right,
    dds.destination_id as PartitionNumber,
    dds.data_space_id as FileGroupID
from sys.partition_schemes ps
inner join sys.destination_data_spaces dds
    on ps.data_space_id=dds.partition_scheme_id
inner join sys.partition_functions pf
    on ps.function_id=pf.function_id
where ps.name='PS_int_Left'

參考文檔：

How to Remember the Next Used Filegroup in a Partition Scheme