Partition SQL Server Table by Year, 35 days rolling period We are trying to partition SQL Server tables by year (2015, 2016, 2017, 2018) and we want to create one more partition which hold current 35 days period data always.

Partitions: Part_2015, Part_2016, Part_2017, Part_2018, Part_35Day

Create Partition Function RangePartFunction (datetime2)
as Range Right For Values ('20150101', '20160101', '20170101') 


I'm struggling how do we create for 2018 and 35 day rolling period

Can anyone please advise...
SQL Server does not currently have a way to declaratively specify a sliding partition window. It is necessary to execute T-SQL to remove the old boundary and create a new one. A T-SQL script (perhaps encapsulated in a stored procedure) can be scheduled daily for this purpose.

It is generally best to split and merge only empty partitions but this is not possible in your case because of the historical yearly partitions in addition to the 35-day window partition in the same table. One method to accomplish this with relative efficiency is to employ an identical partition scheme, function, and staging table. This allows you to operate on the 35-day window partition without excessive data movement and logging that would be required with SPLIT and MERGE of non-empty partitions.

The script below slides the daily window and moves data outside the active window using DELETE...OUTPUT (100K daily rows). The heavy lifting for the data within the active window (about 3.5M rows) is done with CREATE INDEX...WITH(DROP_EXISTING=ON). I would expect this to run well within your 3 hour maintenance window, probably seconds or minutes at worst.

This assumes your table and indexes are aligned. Below is example 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


Daily partition maintenance script (see inline comments):

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 &gt; @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 &gt; 0 ROLLBACK;
    THROW;
END CATCH;
GO