如何更改现有分区(SQL Server)的FILEGROUP

Question

我有一个很大的表，我按月份划分。我还为每年创建了文件组，因此每个文件组在当年最多可容纳12个分区。这是分区函数：

CREATE PARTITION FUNCTION [PF_MonthlyPartition](datetime) 
AS RANGE RIGHT FOR VALUES (N'2019-06-01T00:00:00.000', 
N'2019-07-01T00:00:00.000', N'2019-08-01T00:00:00.000', N'2019-09-01T00:00:00.000', 
N'2019-10-01T00:00:00.000', N'2019-11-01T00:00:00.000', N'2019-12-01T00:00:00.000', 
N'2020-01-01T00:00:00.000', N'2020-02-01T00:00:00.000', N'2020-03-01T00:00:00.000', 
N'2020-04-01T00:00:00.000', N'2020-05-01T00:00:00.000', N'2020-06-01T00:00:00.000', 
N'2020-07-01T00:00:00.000', N'2020-08-01T00:00:00.000', N'2020-09-01T00:00:00.000', 
N'2020-10-01T00:00:00.000', N'2020-11-01T00:00:00.000', N'2020-12-01T00:00:00.000', 
N'2021-01-01T00:00:00.000', N'2021-02-01T00:00:00.000')

问题是，我的最后一个分区不是在FG_2021中，而是在PRIMARY文件组中。

数据分发现在

如何将该分区的文件组更改为FG_2021？

我当前的分区显示结果是这样的：

CREATE PARTITION SCHEME [PS_MonthWise] AS PARTITION [PF_MonthlyPartition] 
TO ([FG_2019], [FG_2019], [FG_2019], 
[FG_2019], [FG_2019], [FG_2019], 
[FG_2019], [FG_2019], [FG_2020], 
[FG_2020], [FG_2020], [FG_2020], 
[FG_2020], [FG_2020], [FG_2020], 
[FG_2020], [FG_2020], [FG_2020], 
[FG_2020], [FG_2020], [PRIMARY], [FG_2021])

Answer 1

当数据被移动到正确的FG时，您是否有能力让错误的分区不可用/为空？如果是，则将分区切换到空表，发出必要的create index... with (drop_existing = on...) on [FG_2021]命令以物理移动数据，更改分区函数以合并出"；坏的"；分区，再次更改分区函数以重新添加分区边界(这次使用右侧FG(，然后将分区(从步骤1(切换回主表。错误的分区看起来没有那么大，所以这应该很快。

---

更新

这里有一个在虚拟数据库中的快速复制：

/*setup*/
use master;
drop database if exists prtTest;
create database prtTest;
alter database prtTest add FILEGROUP [FG_2019];
alter database prtTest add file (
name = 'FG_2019', 
filename = 'c:tempFG_2019.ndf', 
size = 10mb) 
to filegroup [FG_2019];
alter database prtTest add FILEGROUP [FG_2020];
alter database prtTest add file (
name = 'FG_2020',
filename = 'c:tempFG_2020.ndf',
size = 10mb
) to filegroup [FG_2020];
alter database prtTest add FILEGROUP [FG_2021];
alter database prtTest add file (
name = 'FG_2021',
filename = 'c:tempFG_2021.ndf',
size = 10mb
) to filegroup [FG_2021];
use prtTest;
go
CREATE PARTITION FUNCTION [PF_MonthlyPartition](datetime) 
AS RANGE RIGHT FOR VALUES (N'2019-06-01T00:00:00.000', 
N'2019-07-01T00:00:00.000', N'2019-08-01T00:00:00.000', N'2019-09-01T00:00:00.000', 
N'2019-10-01T00:00:00.000', N'2019-11-01T00:00:00.000', N'2019-12-01T00:00:00.000', 
N'2020-01-01T00:00:00.000', N'2020-02-01T00:00:00.000', N'2020-03-01T00:00:00.000', 
N'2020-04-01T00:00:00.000', N'2020-05-01T00:00:00.000', N'2020-06-01T00:00:00.000', 
N'2020-07-01T00:00:00.000', N'2020-08-01T00:00:00.000', N'2020-09-01T00:00:00.000', 
N'2020-10-01T00:00:00.000', N'2020-11-01T00:00:00.000', N'2020-12-01T00:00:00.000', 
N'2021-01-01T00:00:00.000', N'2021-02-01T00:00:00.000');
CREATE PARTITION SCHEME [PS_MonthWise] AS PARTITION [PF_MonthlyPartition] 
TO ([FG_2019], [FG_2019], [FG_2019], 
[FG_2019], [FG_2019], [FG_2019], 
[FG_2019], [FG_2019], [FG_2020], 
[FG_2020], [FG_2020], [FG_2020], 
[FG_2020], [FG_2020], [FG_2020], 
[FG_2020], [FG_2020], [FG_2020], 
[FG_2020], [FG_2020], [PRIMARY], [FG_2021]
);
create table dbo.foo (dt datetime) on [PS_MonthWise](dt);
create clustered index [IX_foo] on dbo.foo(dt) on [PS_MonthWise](dt);
insert into dbo.foo (dt)
values 
('2021-01-01'), 
('2021-01-02'), 
('2021-02-01'), 
('2021-02-02');
select *, $partition.[PF_MonthlyPartition](dt)
from dbo.foo;
/*end setup*/
/*run this query after any partition function changes*/
select p.partition_number, prv.[value], fg.name, p.[rows]
from sys.partitions as p
left join sys.allocation_units as au
on au.container_id = p.hobt_id
and au.[type] in (1, 3)
left join sys.filegroups as fg
on fg.data_space_id = au.data_space_id
left join sys.partition_range_values as prv
on prv.boundary_id = p.partition_number
where object_id = object_id('dbo.foo')
order by partition_number;
create table dbo.temp_foo (dt datetime) on [PRIMARY];
create clustered index [IX_foo] on dbo.temp_foo(dt) on [PRIMARY];
alter table dbo.foo switch partition 21
to dbo.temp_foo;
create clustered index [IX_foo] on dbo.temp_foo(dt) 
with (drop_existing = on) on [PS_MonthWise](dt);
alter partition function [PF_MonthlyPartition]()
merge range (N'2021-01-01T00:00:00.000');
alter partition scheme [PS_MonthWise]
next used [FG_2021];
alter partition function [PF_MonthlyPartition]()
split range (N'2021-01-01T00:00:00.000');
alter table dbo.temp_foo switch partition 21
to dbo.foo partition 21;