Replacing Filegroup Used in Last Partition Slice for RANGE LEFT-based Partition Scheme

Question

I have a simple date based partition function using RANGE LEFT...

CREATE PARTITION FUNCTION [PF_YEAR_LEFT](date) AS RANGE LEFT 
    FOR VALUES (N'1992-12-31', N'1993-12-31', N'1994-12-31')
GO

CREATE PARTITION SCHEME [PS_YEAR_LEFT] AS PARTITION [PF_YEAR_LEFT] 
    TO ([DATA_1992], [DATA_1993], [DATA_1994], [DATA_1995])
GO

In the above code, filegroup [DATA_1995] would retain the values beyond the last partition boundary. So, if I elect to add new filegroups and split the function for additional boundaries in this fashion...

ALTER PARTITION SCHEME [PS_YEAR_LEFT] NEXT USED [DATA_1995]
GO

ALTER PARTITION FUNCTION [PF_YEAR_LEFT]() SPLIT RANGE (N'1995-12-31')
GO

ALTER PARTITION SCHEME [PS_YEAR_LEFT] NEXT USED [DATA_1996]
GO

ALTER PARTITION FUNCTION [PF_YEAR_LEFT]() SPLIT RANGE (N'1996-12-31')
GO

The filegroup [DATA_1995] remains in the last partition position for values beyond the upper-most boundary.

Can this last filegroup be changed after the partition scheme is initially created or is the only way through creation of a new partition scheme and rebuilding all indexes utilizing the old scheme into the new scheme?

Answer 1

Can this last filegroup be changed after the partition scheme is initially created or is the only way through creation of a new partition scheme and rebuilding all indexes utilizing the old scheme into the new scheme?

The last partition of a RANGE LEFT function is a permanent partition that cannot be removed nor can the first partition of a RANGE RIGHT function. This is one of the considerations detailed in my Table Partitioning Best Practices article and why I recommend a more intuitive RANGE RIGHT function for incremental temporal types along with explicit boundaries for data within the expected range.

You don't need to go so far as to rebuild the existing table and indexes to remediate the misnamed filegroup. If you add new partitions using your script (taking care to ensure that the SPLIT partitions are empty), you'll end up with this scheme:

CREATE PARTITION SCHEME PS_YEAR_LEFT AS PARTITION PF_YEAR_LEFT TO (
      [DATA_1992]
    , [DATA_1993]
    , [DATA_1994]
    , [DATA_1995]
    , [DATA_1996]
    , [DATA_1995]
    );

You could create a new scheme with the last partition on a dummy filegroup (or PRIMARY), create a new aligned table, switch all partitions but the last one (which will be empty if you have no 1997+ data) into the new table, drop the old table, rename, etc. Example below.

CREATE PARTITION SCHEME PS_YEAR_LEFT_NEW AS PARTITION PF_YEAR_LEFT TO (
      [DATA_1992]
    , [DATA_1993]
    , [DATA_1994]
    , [DATA_1995]
    , [DATA_1996]
    , [PRIMARY]
    );
GO
CREATE TABLE dbo.YourTable(
    DateColumn date
) ON PS_YEAR_LEFT(DateColumn);
GO
CREATE TABLE dbo.YourTable_New(
    DateColumn date
) ON PS_YEAR_LEFT_NEW(DateColumn);
GO
DECLARE @Date date = '19921231';
WHILE @Date <= '19961231'
BEGIN
    ALTER TABLE dbo.YourTable
        SWITCH PARTITION $PARTITION.PF_YEAR_LEFT(@Date)
        TO dbo.YourTable_New PARTITION $PARTITION.PF_YEAR_LEFT(@Date);
    SET @Date = DATEADD(year,1,@Date);
END;
GO
--drop original table
--drop original partition scheme
--rename new table, constraints, indexes to original name
--update statistics