如何更改现有分区( 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中，而是在主文件组中。

现在的数据分发

如何将该分区的文件组更改为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:\temp\FG_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:\temp\FG_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:\temp\FG_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;