CREATE PROC dbo.SlideRangeRightWindow_datetime

        @RetentionDays int,

        @RunDate datetime = NULL

/*

      This proc maintains a RANGE RIGHT daily sliding window

      based on the specified @RetentionDays.  It is intended to

      be scheduled daily shortly after midnight. In addition to

      purging old data, the partition function is adjusted to

      account for scheduling issues or changes in @RetentionDays.

      Partitions are split and merged so that the first partition

      boundary is the oldest retained data date and the last

      boundary is the next day.  Other partitions contain current

      and historical data for the specifiednumber of @RetentionDays. 

      After successful execution, (at least) the following

      partitions will exist:

      - partition 1 = data older than retained date (empty)

      - other partitions = hitorical data (@RunDate - 1 and earlier)

      - second from last partition = current data (@RunDate)

      - last partition = future data (@RunDate + 1) (empty)     

*/

AS

SET NOCOUNT, XACT_ABORT ON;

DECLARE

        @Error int,

        @RowCount bigint,

        @ErrorLine int,

        @Message varchar(255),

        @OldestRetainedDate datetime,

        @PartitionBoundaryDate datetime;

SET @Error = 0;

BEGIN TRY

      IF @RunDate IS NULL

      BEGIN

            --use current date (midnight) if no date specified

            SET @RunDate = DATEADD(day, 0, DATEDIFF(day, '', GETDATE()));

      END

      ELSE

      BEGIN

            --set time to midnight of specified date

            SET @RunDate = DATEADD(day, 0, DATEDIFF(day, '', @RunDate));

      END

      --calculate oldest retention date based on @RetentionDays and @RunDate

      SET @OldestRetainedDate = DATEADD(day, @RetentionDays * -1, @RunDate);

      SET @Message =

            'Run date = ' +

            + CONVERT(varchar(23), @RunDate, 121)

            + ', Retention days = '

            + CAST(@RetentionDays AS varchar(10))

            + ', Oldest retained data date = '

            + CONVERT(varchar(23), @OldestRetainedDate, 121);

      RAISERROR (@Message, 0, 1) WITH NOWAIT;

      BEGIN TRAN;

      --acquire exclusive table lock to prevent deadlocking

      --with concurrent activity.

      SELECT TOP 1 @error = 0

      FROM dbo.MyPartitionedTable WITH (TABLOCKX, HOLDLOCK);

      --make sure we have a boundary for oldest retained period

      IF NOT EXISTS(

            SELECT prv.value

            FROM sys.partition_functions AS pf

            JOIN sys.partition_range_values AS prv ON

                  prv.function_id = pf.function_id

            WHERE

                  pf.name = 'PF_MyPartitionFunction'

                  AND CAST(prv.value AS datetime) = @OldestRetainedDate

            )

      BEGIN

            ALTER PARTITION SCHEME PS_MyPartitionScheme

                    NEXT USED [PRIMARY];

            ALTER PARTITION FUNCTION PF_MyPartitionFunction()

                    SPLIT RANGE(@OldestRetainedDate);

            SET @Message =

                    'Created boundary for oldest retained data ('

                    + CONVERT(varchar(30), @OldestRetainedDate, 121) + ')';

            RAISERROR(@Message, 0, 1) WITH NOWAIT;

      END

      ELSE

      BEGIN

            SET @Message =

                    'Oldest retained data boundary already exists ('

                    + CONVERT(varchar(30), @OldestRetainedDate, 121) + ')';

            RAISERROR(@Message, 0, 1) WITH NOWAIT;

      END

      --get earliest expired boundary

      SET @PartitionBoundaryDate = NULL;

      SELECT

            @PartitionBoundaryDate =

                    MIN(CAST(prv.value AS datetime))

      FROM sys.partition_functions AS pf

      JOIN sys.partition_range_values AS prv ON

            prv.function_id = pf.function_id

      WHERE

            pf.name = 'PF_MyPartitionFunction'

            AND CAST(prv.value AS datetime) < @OldestRetainedDate;

      --get rowcount of first partition

      SELECT @RowCount = rows

      FROM sys.partitions

      WHERE

            object_id = OBJECT_ID(N'dbo.MyPartitionedTable')

            AND partition_number = 1

            AND index_id IN(0, 1);

      --purge data from first partition if not empty

      IF @RowCount > 0

      BEGIN

            TRUNCATE TABLE dbo.MyPartitionedTable_Staging;

            ALTER TABLE dbo.MyPartitionedTable SWITCH PARTITION 1

                    TO dbo.MyPartitionedTable_Staging PARTITION 1;

            TRUNCATE TABLE dbo.MyPartitionedTable_Staging;

            SET @Message =

                    'Purged data older than '

                    + CONVERT(varchar(23), @PartitionBoundaryDate, 121)

                    + ' (' + CAST(@RowCount as varchar(20)) + ' rows)';

            RAISERROR(@Message, 0, 1) WITH NOWAIT;

      END

      ELSE

      BEGIN

            SET @Message =

                    'First partition is empty.  No data older than '

                    + CONVERT(varchar(23), @OldestRetainedDate, 121);

            RAISERROR(@Message, 0, 1) WITH NOWAIT;   

      END

      --Switch and merge expired data partitions, starting with the earliest

      WHILE @PartitionBoundaryDate < @OldestRetainedDate

      BEGIN

            --get count of rows to be purged

            SELECT @RowCount = rows

            FROM sys.partitions

            WHERE

                    object_id = OBJECT_ID(N'MyPartitionedTable')

                    AND partition_number = $PARTITION.PF_MyPartitionFunction(@PartitionBoundaryDate)

                    AND index_id IN(0, 1);     

            --purge data, if needed

            IF @RowCount > 0

            BEGIN

                  --move data to staging table

                  TRUNCATE TABLE dbo.MyPartitionedTable_Staging;

                  ALTER TABLE dbo.MyPartitionedTable SWITCH PARTITION $PARTITION.PF_MyPartitionFunction(@PartitionBoundaryDate)

                          TO dbo.MyPartitionedTable_Staging PARTITION $PARTITION.PF_MyPartitionFunction(@PartitionBoundaryDate);

                  --purge data from staging table

                  TRUNCATE TABLE dbo.MyPartitionedTable_Staging;

                  SET @Message =

                          'Purged data for boundary '

                          + CONVERT(varchar(23), @PartitionBoundaryDate, 121)

                          + ' (' + CAST(@RowCount as varchar(20)) + ' rows)';

                  RAISERROR(@Message, 0, 1) WITH NOWAIT;

            END

            ELSE

            BEGIN

                  SET @Message =

                          'Partition for boundary '

                          + CONVERT(varchar(23), @PartitionBoundaryDate, 121)

                          + ' is empty';

                  RAISERROR(@Message, 0, 1) WITH NOWAIT;         

            END

            --remove purged partition

            ALTER PARTITION FUNCTION PF_MyPartitionFunction()

                  MERGE RANGE(@PartitionBoundaryDate);

            SET @Message =

                  'Removed boundary '

                  + CONVERT(varchar(30), @PartitionBoundaryDate, 121);

            RAISERROR(@Message, 0, 1) WITH NOWAIT;

            --get earliest boundary before retention date for next iteration

            SET @PartitionBoundaryDate = NULL;

            SELECT

                  @PartitionBoundaryDate =

                          MIN(CAST(prv.value AS datetime))

            FROM sys.partition_functions AS pf

            JOIN sys.partition_range_values AS prv ON

                  prv.function_id = pf.function_id

            WHERE

                  pf.name = 'PF_MyPartitionFunction'

                  AND CAST(prv.value AS datetime) < @OldestRetainedDate;

      END;

      --Make sure we have an empty partition for tomorrow

      SET @PartitionBoundaryDate = DATEADD(day, 1, @RunDate);

      IF NOT EXISTS

              (

              SELECT prv.value

              FROM sys.partition_functions AS pf

              JOIN sys.partition_range_values AS prv ON

                        prv.function_id = pf.function_id

              WHERE

                        pf.name = 'PF_MyPartitionFunction'

                        AND CAST(prv.value AS datetime) = @PartitionBoundaryDate

              )

      BEGIN

              ALTER PARTITION SCHEME PS_MyPartitionScheme

                        NEXT USED [PRIMARY];

              ALTER PARTITION FUNCTION PF_MyPartitionFunction()

                        SPLIT RANGE(@PartitionBoundaryDate);

              SET @Message =

                        'Created boundary future data '

                        + CONVERT(varchar(30), @PartitionBoundaryDate, 121);

              RAISERROR(@Message, 0, 1) WITH NOWAIT;

      END

      ELSE

      BEGIN

              SET @Message =

                        'Partition already exists for future boundary '

                        + CONVERT(varchar(30), @PartitionBoundaryDate, 121);

              RAISERROR(@Message, 0, 1) WITH NOWAIT;

      END;

      COMMIT;

END TRY

BEGIN CATCH

      SELECT

            @Error = ERROR_NUMBER(),

            @Message = ERROR_MESSAGE(),

            @ErrorLine = ERROR_LINE();

      RAISERROR('Partition maintenenace failed with error %d at line %d: %s', 16, 1, @Error, @ErrorLine, @Message) WITH NOWAIT;

      IF @@TRANCOUNT > 0

      BEGIN

            ROLLBACK;

      END;

END CATCH;

RETURN @Error;

GO