按年对 SQL Server 表进行分区，滚动周期为 35 天

SQL Server 当前无法以声明方式指定滑动分区窗口。需要执行 T-SQL 删除旧边界并创建新边界。为此，可以每天安排一个 T-SQL 脚本（可能封装在存储过程中）。

通常最好只拆分和合并空分区，但在您的情况下这是不可能的，因为除了同一张表中的 35 天窗口分区之外，还有历史年度分区。以相对效率完成此任务的一种方法是使用相同的分区方案、函数和登台表。SPLIT这使您可以在 35 天窗口分区上进行操作，而无需MERGE进行非空分区所需的过多数据移动和日志记录。

下面的脚本滑动每日窗口并使用DELETE...OUTPUT（每天 100K 行）将数据移动到活动窗口之外。活动窗口（大约 350 万行）中的数据的繁重工作是使用CREATE INDEX...WITH(DROP_EXISTING=ON). 我希望它在您的 3 小时维护窗口内运行良好，最坏的情况可能是几秒钟或几分钟。

这假设您的表和索引是对齐的。下面是示例 DDL。

CREATE PARTITION FUNCTION RangePartFunction (datetime2)
    AS RANGE RIGHT FOR VALUES ('20150101', '20160101', '20170101', '20180101'); 
CREATE PARTITION FUNCTION RangePartFunction_Staging (datetime2)
    AS RANGE RIGHT FOR VALUES ('20150101', '20160101', '20170101', '20180101');
CREATE PARTITION SCHEME RangePartScheme
    AS PARTITION RangePartFunction TO ([PRIMARY], Part_2015, Part_2016, Part_2017, Part_2018);
CREATE PARTITION SCHEME RangePartScheme_Staging
    AS PARTITION RangePartFunction_Staging TO ([PRIMARY], Part_2015, Part_2016, Part_2017, Part_2018);

--partition for last 35 days
DECLARE @Start35DayWindowDate datetime2 = CAST(DATEADD(day, -35, CAST(SYSDATETIME() AS date)) AS datetime2);
ALTER PARTITION SCHEME RangePartScheme
    NEXT USED Part_35Day;
ALTER PARTITION SCHEME RangePartScheme_Staging
    NEXT USED Part_35Day;
ALTER PARTITION FUNCTION RangePartFunction()
    SPLIT RANGE(@Date);
ALTER PARTITION FUNCTION RangePartFunction_Staging()
    SPLIT RANGE(@Date);
GO
--example tables
CREATE TABLE dbo.Table1(
      Col1 datetime2
    , Col2 int
    , Col3 char(1000) 
) ON RangePartScheme(Col1);
CREATE CLUSTERED INDEX cdx ON dbo.Table1(Col2, Col1)  ON RangePartScheme(Col1);
CREATE NONCLUSTERED INDEX idx ON dbo.Table1(Col2) ON RangePartScheme(Col1);
CREATE TABLE dbo.Table1_Staging(
      Col1 datetime2
    , Col2 int
    , Col3 char(1000) 
) ON RangePartScheme(Col1);
CREATE CLUSTERED INDEX cdx ON dbo.Table1_Staging(Col2, Col1)  ON RangePartScheme_Staging(Col1);
CREATE NONCLUSTERED INDEX idx ON dbo.Table1_Staging(Col2) ON RangePartScheme_Staging(Col1);
GO

每日分区维护脚本（见内联注释）：

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;

    DECLARE @Start35DayWindowDate datetime2  = CAST(DATEADD(day, -35, CAST(SYSDATETIME() AS date)) AS datetime2);
    DECLARE @LastBoundaryDate datetime2 = (
        SELECT MAX(CAST(value AS datetime2))
            FROM  sys.partition_functions AS pf
            JOIN sys.partition_range_values AS prv ON prv.function_id = pf.function_id
            WHERE 
                pf.name =   N'RangePartFunction'
        );

    IF @Start35DayWindowDate > @LastBoundaryDate
    BEGIN

        --switch last partition into staging table
        ALTER TABLE dbo.Table1
            SWITCH PARTITION $Partition.RangePartFunction(@LastBoundaryDate)
            TO dbo.Table1_Staging PARTITION $Partition.RangePartFunction_Staging(@LastBoundaryDate);
        --merge original function (with empty partition)
        ALTER PARTITION FUNCTION RangePartFunction()
            MERGE RANGE(@LastBoundaryDate);

        --create new yearly partition when the 35-day window passes the first of the year
        --NOTE: current year filegroup must be created and this script changed with the new filegroup name before February 6th of each year
        IF @LastBoundaryDate = DATEADD(year, DATEDIFF(year, '', @LastBoundaryDate), '')
        BEGIN
            ALTER PARTITION SCHEME RangePartScheme
                NEXT USED Part_2019;
            ALTER PARTITION FUNCTION RangePartFunction()
                SPLIT RANGE(@LastBoundaryDate);
        END;

        --split original function for new 35-day window boundary
        ALTER PARTITION SCHEME RangePartScheme
            NEXT USED Part_35Day;
        ALTER PARTITION FUNCTION RangePartFunction()
            SPLIT RANGE(@Start35DayWindowDate);

        --rebuild staging table using original scheme
        CREATE CLUSTERED INDEX cdx ON dbo.Table1_Staging(Col2, Col1) WITH(DROP_EXISTING=ON) ON RangePartScheme(Col1);
        CREATE NONCLUSTERED INDEX idx ON dbo.Table1_Staging(Col2) WITH(DROP_EXISTING=ON) ON RangePartScheme(Col1);

        --switch partition with new window from staging table back into original table
        ALTER TABLE dbo.Table1_Staging
            SWITCH PARTITION $Partition.RangePartFunction(@Start35DayWindowDate)
            TO dbo.Table1 PARTITION $Partition.RangePartFunction(@Start35DayWindowDate);

        --move remaining rows into original table historical yearly file group (single day older than 35 days)
        DELETE dbo.Table1_Staging
            OUTPUT deleted.* INTO dbo.Table1;

        --apply same changes to staging function, scheme, and table to prepare for next day
        ALTER PARTITION FUNCTION RangePartFunction_Staging()
            MERGE RANGE(@LastBoundaryDate);
        ALTER PARTITION SCHEME RangePartScheme_Staging
            NEXT USED Part_35Day;
        ALTER PARTITION FUNCTION RangePartFunction_Staging()
            SPLIT RANGE(@Start35DayWindowDate);
        IF @LastBoundaryDate = DATEADD(year, DATEDIFF(year, '', @LastBoundaryDate), '')
        BEGIN
            ALTER PARTITION SCHEME RangePartScheme_Staging
                NEXT USED Part_2019;
            ALTER PARTITION FUNCTION RangePartFunction_Staging()
                SPLIT RANGE(@LastBoundaryDate);
        END;
        CREATE CLUSTERED INDEX cdx ON dbo.Table1_Staging(Col2, Col1) WITH(DROP_EXISTING=ON) ON RangePartScheme_Staging(Col1);
        CREATE NONCLUSTERED INDEX idx ON dbo.Table1_Staging(Col2) WITH(DROP_EXISTING=ON) ON RangePartScheme_Staging(Col1);
    END;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO