在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