SQL Server 2016 SP1 Standard Edition Enhancements

I seldom get excited about service packs but the changes released with SQL Server 2016 SP1 are the most significant I’ve seen in a SQL Server service pack in 20+ years. Microsoft announced this week at the Microsoft Connect(); developer’s conference that SQL Server 2016 SP1, which is available for download immediately, allows features previously available only in Enterprise/Developer Editions to be used in lessor Standard, Web, Express, and LocalDB Editions too. Features like table partitioning, In-Memory OLTP, and columnstore are now options for developers and DBAs using SQL Server Standard Edition and even the free Express Edition in production. See SQL Server 2016 Service Pack 1 (SP1) released !!! for the complete matrix of programmability features by edition along with other cool SP1 information.

The implications are huge now that SQL Server has the same programmability surface area among editions. The choice of the production edition can be made independently based on operational needs rather than programmability features. Developers can use a free edition (i.e. LocalDB, Express or Developer) without fear a feature won’t be available in production as long as prod is running SQL Server 2016 SP1 or greater. DBAs can now choose the appropriate edition for production based on other considerations like advanced high availability, TDE, Auditing as well as performance features like higher supported memory, more number of cores, and advanced scanning. This separation of concerns avoids the need to lock in the production edition early in the application lifecycle, making development easier and production implementation more flexible.

Real World Use Case Scenario
I work with an ISV with hundreds of customers running a mix of Standard and Enterprise Edition. Their needs vary widely and SQL Server Enterprise Edition is not an option for some due to budget constraints. Some tables are often quite large so partitioning is required for manageability and, for their reporting workload, partitioning also improves performance of large scans due to partition elimination. The ugliness though, is that table partitioning (and/or columnstore) is the right tool for the job but was not an option for customers on Standard Edition.

The ISV initially compromised and used view partitioning instead of table partitioning so that the same code would run regardless of edition. Although that provided the expected manageability benefits, there were some downsides. Compilation time increased significantly as the number of partitioned view member tables increased as did the query plan complexity. This sometimes resulted in poor query plans against very large tables and especially impacted larger and most valued customers, most of which were running Enterprise Edition.

To address the problem before SQL Server 2016 SP1, the ISV added conditional code to the application so that either view or table partitioning could be used depending on the SQL Server edition. This wasn’t ideal as it added code complexity and doubled the number of QA test cases for application features that performed partition maintenance. However, since the resultant benefits for their larger customers on Enterprise Edition were quite significant; the additional costs of development and testing were well-justified.

Now that table partitioning is available in SQL Server 2016 SP1 Standard Edition, they plan to require SQL Server 2016 SP1 (or later) going forward, use table partitioning unconditionally, and perhaps introduce usage of other features like columnstore that were previously Enterprise only. Not only will this simplify the code base and test cases, customers on Standard Edition will be happier with their experience and can upgrade to Enterprise if they so choose without reinstalling or reconfiguring the application. It will of course take some time before all their customers upgrade to the latest product version and SQL 2016 SP1+ but the future is much brighter now.

Perform Due Diligence
If you are new to features previously available only in Enterprise Edition, I suggest you perform due diligence before using these features. Memory-optimized features like columnstore and In-Memory OLTP require additional physical memory and insufficient memory with memory-optimized features will be a production show-stopper. Make sure your hardware is sized appropriately regardless of edition and, in the case of editions other than Enterprise or Developer, memory requirements don’t exceed the maximum capacity limits for that edition. Although very powerful, In-Memory OLTP is a fundamentally different paradigm that you might be accustomed to regarding transactional behavior and isolation levels. Be sure you fully understand these features before using it in development or production.

Summary
I hope these changes are enough motivation for you to consider upgrading to SQL Server 2016 SP1, especially if you are running Standard Edition or are currently on an older SQL Server version. Together with the fact that SQL Server 2016 just runs faster, the time and effort spend in upgrading is a solid investment that will pay dividends regardless of edition.

Add or Remove IDENTITY Property From an Existing Column Efficiently

Introduction
Refactoring is often needed to improve schema design or address changes in requirements. Occasionally, these schema modifications require removing the IDENTITY property from an existing non-empty table while retaining the existing values. Unfortunately, there is no DDL command to add or remove IDENTITY from an existing column. Traditionally, one must go through the pain of either recreating the table or jumping through hoops by adding a new column, updating the new column value with the old column value, and dropping the original column. This is especially problematic with large tables and short maintenance windows.

This article details how to use SWITCH as an alternative technique to add or remove IDENTITY from an existing column. I have been using SWITCH for many years but learned only recently that the identical schema restriction for SWITCH does not include IDENTITY column property; one can SWITCH data from one table to another even though the IDENTITY column property differs. This behavior allows the IDENTITY property to be added or removed.

Overview
All tables are partitioned from a database storage engine perspective since SQL Server 2005, although multiple partitions require Enterprise Edition. The implication of this storage architecture is that one can use ALTER TABLE…SWITCH regardless of SQL Server edition to move the entire contents of one table to another without physically moving rows as long as the source and target tables have an identical schema and are physically aligned (indexes and tables on same filegroup). SWITCH performs fast storage meta-data changes so the operation typically takes less than a second regardless of table size. The target table must be empty before SWITCH. After SWITCH, the target table will contain the data and the source table will be empty (similar to TRUNCATE).

Since the identical schema restriction does not include the IDENTITY column property, SWITCH is a handy technique to add or remove IDENTITY from an existing column of a non-empty table. For an empty table, a simple drop and create is easier and more efficient.

Remove IDENTITY from an Existing Column
The script below uses a staging table without the IDENTITY column property along with SWITCH to remove the IDENTITY property from the column and retain previously assigned identity values. I drop the empty original table and rename the staging table to the original name along with constraints and indexes after the operation.

--create sample table and data
CREATE TABLE dbo.RemoveIdentityExample(
	  IncrementalColumn int IDENTITY NOT NULL
		CONSTRAINT PK_RemoveIdentityExample PRIMARY KEY
	, OtherData varchar(20) NULL
	);
CREATE INDEX idx_RemoveIdentityExample_OtherData
	ON dbo.RemoveIdentityExample(OtherData);
INSERT INTO dbo.RemoveIdentityExample VALUES
	  ('Sample data one')
	, ('Sample data two')
	, ('Sample data three');
GO

SET XACT_ABORT ON;
BEGIN TRY
	BEGIN TRAN;
	--create staging table with same schema, indexes, and constraints
	CREATE TABLE dbo.RemoveIdentityExampleStaging(
		  IncrementalColumn int NOT NULL --IDENTITY column property removed
			CONSTRAINT PK_RemoveIdentityExampleStaging PRIMARY KEY
		, OtherData varchar(20) NULL
		);
	CREATE INDEX idx_RemoveIdentityExampleStaging_OtherData
		ON dbo.RemoveIdentityExampleStaging(OtherData);

	ALTER TABLE dbo.RemoveIdentityExample
		SWITCH TO dbo.RemoveIdentityExampleStaging;
	DROP TABLE dbo.RemoveIdentityExample;
	--rename table
	EXEC sp_rename
		  @objname = N'dbo.RemoveIdentityExampleStaging'
		, @newname = N'RemoveIdentityExample'
		, @objtype = 'OBJECT';
	--rename constraints
	EXEC sp_rename
		  @objname = N'dbo.PK_RemoveIdentityExampleStaging'
		, @newname = N'PK_RemoveIdentityExample'
		, @objtype = 'OBJECT';
	--rename indexes
	EXEC sp_rename
		  @objname = N'dbo.RemoveIdentityExample.idx_RemoveIdentityExampleStaging_OtherData'
		, @newname = N'idx_RemoveIdentityExampleStaging_OtherData'
		, @objtype = N'INDEX';
	COMMIT;
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0 ROLLBACK;
	THROW;
END CATCH;
GO

Add IDENTITY to an Existing Column
This script uses a staging table with the IDENTITY column property along with SWITCH to add the IDENTITY property while retaining the existing column values. After the SWITCH operation, DBCC CHECKIDENT seeds the next IDENTITY value greater than the current value in the column.

Note that an IDENTITY column must now allow NULL values so, in the case of a nullable column, the column must first be altered to NOT NULL before using this method.

--create sample table and data
CREATE TABLE dbo.AddIdentityExample(
	  IncrementalColumn int NOT NULL
		CONSTRAINT PK_AddIdentityExample PRIMARY KEY
	, OtherData varchar(20) NULL
	);
CREATE INDEX idx_AddIdentityExample_OtherData
	ON dbo.AddIdentityExample(OtherData);
INSERT INTO dbo.AddIdentityExample VALUES
	  (1, 'Sample data one')
	, (2, 'Sample data two')
	, (3, 'Sample data three');
GO

SET XACT_ABORT ON;
BEGIN TRY
	BEGIN TRAN;
	--create staging table with same schema, indexes, and constraints
	CREATE TABLE dbo.AddIdentityExampleStaging(
		  IncrementalColumn int IDENTITY NOT NULL --IDENTITY column property added
			CONSTRAINT PK_AddIdentityExampleStaging PRIMARY KEY
		, OtherData varchar(20) NULL
		);
	CREATE INDEX idx_AddIdentityExampleStaging_OtherData
		ON dbo.AddIdentityExampleStaging(OtherData);

	ALTER TABLE dbo.AddIdentityExample
		SWITCH TO dbo.AddIdentityExampleStaging;
	DROP TABLE dbo.AddIdentityExample;
	--rename table
	EXEC sp_rename
		  @objname = N'dbo.AddIdentityExampleStaging'
		, @newname = N'AddIdentityExample'
		, @objtype = 'OBJECT';
	--rename constraints
	EXEC sp_rename
		  @objname = N'dbo.PK_AddIdentityExampleStaging'
		, @newname = N'PK_AddIdentityExample'
		, @objtype = 'OBJECT';
	--rename indexes
	EXEC sp_rename
		  @objname = N'dbo.AddIdentityExample.idx_AddIdentityExampleStaging_OtherData'
		, @newname = N'idx_AddIdentityExampleStaging_OtherData'
		, @objtype = N'INDEX';
	--seed IDENTITY with current max column value
	DBCC CHECKIDENT(N'dbo.AddIdentityExample');
	COMMIT;
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0 ROLLBACK;
	THROW;
END CATCH;
GO

Tiered Storage Partition Copy

In my last tiered storage sliding window post, I shared a sliding window script that also moves an older partition to a different filegroup as part of a tiered storage strategy. This approach allows one to keep actively used partitions on the fastest storage available while keeping older less frequently used data on less expensive storage. That version of the script moves data efficiently using a staging table and CREATE INDEX…DROP EXISTING but the downside is the data being moved is unavailable for querying from the time the partition is switched out of the main table until it is switched back in. Consequently, the maintenance needs to be scheduled during a window where data in the partition being moved isn’t needed.

This follow-up article shows an alternative copy technique instead. Data in the partition being moved is left in the main table until the after the copy completes. Once data are copied to the different filegroup, partition maintenance is performed to switch out the original partition and switch in the copied data, now on the older data filegroup. This allows data to remain online except during the final partition switches, which are fast meta-data operations. It is assumed that the data in the partition being moved is read-only during this process.

Copy Data to Slower Storage
Unfortunately, neither ALTER INDEX nor CREATE INDEX…DROP EXISTING provide the capability to repartition an individual partition of a table/index. ALTER INDEX allows one to target a specific partition during a REORGANIZE or REBUILD but not change the filegroup or partition scheme in the process. CREATE INDEX…DROP EXISTING allows a filegroup or partition scheme specification but applies to the entire index; individual partitions cannot be specified. Consequently DML (INSERT…SELECT) instead of DDL must be used in order to keep data online during the copy process.

I’ll assume you’ve already slid the window by purging expired data and preparing for new data as detailed in my last article with the only task remaining to move an older partition to a slower storage filegroup. The following steps will keep the data being moved online during the potentially long copy process. The offline operations performed in steps 3-6 are metadata operations that will complete quickly, once an exclusive table lock (to avoid deadlocks) can be granted. Note that all partition functions and schemes are identical and the staging table is empty before these steps are performed.

1) Move the empty staging table partition to the older data filgegroup
2) Load data into the moved partition of the staging table
3) Switch the copied data out of the main table into a second staging table
4) Switch the adjacent partition out of the main table into the first staging table
5) Move the main table partition to the older data filegroup
6) Switch the partitions from the first staging table back into the main table

The complete DDL and script is at the end of this article. It differs from my last article (which I’ll assume you’ve already perused) by the addition of a second staging table and this alternative move technique. Below are the individual steps.

Move the empty staging table partition to the older data filgegroup
Remember, the staging table is partitioned using the secondary partition function/scheme so that it can be repartitioned independently of the main table. The first task is to move the partition of the unused secondary partition scheme to the slower storage filegroup using MERGE and SPLIT:

1) MERGE the secondary partition function boundary of the moved month to remove it from the secondary partition function and scheme:

ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
    MERGE RANGE (@MonthStartDateToMoveFromCurrentToOld);

2) Set the secondary partition scheme NEXT USED to slower storage filegroup:

ALTER PARTITION SCHEME PS_12MonthSlidingWindow_Secondary
    NEXT USED FG_OlderData;

3) SPLIT the secondary partition function for the moved month to recreate the partition on the slower storage filegroup:

ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
    SPLIT RANGE (@MonthStartDateToMoveFromCurrentToOld);

Load the moved partition of the staging table
Now that the staging table partition is on the FG_OlderData filegroup, copy data for the partition to be moved into a staging table using INSERT…SELECT. Note that the WHERE clause specifies the source partition boundaries:

INSERT INTO dbo.SlidingWindowTable_Staging WITH(TABLOCKX) (PartitioningColumn, OtherKeyColumn, OtherData)
    SELECT
          PartitioningColumn
	, OtherKeyColumn
	, OtherData
FROM dbo.SlidingWindowTable
WHERE
        PartitioningColumn >= @MonthStartDateToMoveFromCurrentToOld
    AND PartitioningColumn < DATEADD(month, 1, @MonthStartDateToMoveFromCurrentToOld);

Switch the copied data out of the main table into a second staging table
The second staging table is used to permanently remove data from the original partition, still on the NewerData filegroup.

TRUNCATE TABLE dbo.SlidingWindowTable_Staging2;
ALTER TABLE dbo.SlidingWindowTable
    SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow(@MonthStartDateToMoveFromCurrentToOld)
    TO dbo.SlidingWindowTable_Staging2 PARTITION $PARTITION.PF_12MonthSlidingWindow(@MonthStartDateToMoveFromCurrentToOld);

TRUNCATE TABLE dbo.SlidingWindowTable_Staging2;

Switch the adjacent partition out of the main table into the first staging table
Before we move the main table partition (now empty) to the OlderData filegroup, the adjacent partition is also switched out. This is technically not required but is done to follow Microsoft's recommended best practice of merging only empty partitions.

ALTER TABLE dbo.SlidingWindowTable
    SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow(DATEADD(month, -1, @MonthStartDateToMoveFromCurrentToOld))
    TO dbo.SlidingWindowTable_Staging PARTITION $PARTITION.PF_12MonthSlidingWindow_Secondary(DATEADD(month, -1, @MonthStartDateToMoveFromCurrentToOld));

Move the main table partition to the older data filegroup
The main table partition is moved from the NewerData filegroup to OlderData with merge. Keep in mind that the adjacent partition on the OlderData filegroup is empty. Data for both these partitions are in the staging table.

--remove partition from main table
ALTER PARTITION FUNCTION PF_12MonthSlidingWindow()
    MERGE RANGE (@MonthStartDateToMoveFromCurrentToOld);

--create new empty partition on old data filegroup for moved data
ALTER PARTITION SCHEME PS_12MonthSlidingWindow
    NEXT USED FG_OlderData;
    ALTER PARTITION FUNCTION PF_12MonthSlidingWindow()
    SPLIT RANGE (@MonthStartDateToMoveFromCurrentToOld);

Switch the partitions from the first staging table back into the main table
Both main and staging table partition schemes are now identical. The partitions can now be switch back into the main table.

ALTER TABLE dbo.SlidingWindowTable_Staging
    SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow_Secondary(DATEADD(month, -1, @MonthStartDateToMoveFromCurrentToOld))
    TO dbo.SlidingWindowTable PARTITION $PARTITION.PF_12MonthSlidingWindow(DATEADD(month, -1, @MonthStartDateToMoveFromCurrentToOld));
ALTER TABLE dbo.SlidingWindowTable_Staging
    SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow_Secondary(@MonthStartDateToMoveFromCurrentToOld)
    TO dbo.SlidingWindowTable PARTITION $PARTITION.PF_12MonthSlidingWindow(@MonthStartDateToMoveFromCurrentToOld);

Scripts
Below are the complete example scripts to implement this copy sliding window technique. My last tiered storage sliding window post also includes the CREATE DATABASE, including filegroups.

--main table partition function (before start of new month)
CREATE PARTITION FUNCTION PF_12MonthSlidingWindow (datetime2(0))
AS RANGE RIGHT FOR VALUES (
	  '2014-11-01T00:00:00' --current month -12 on OlderData filegroup
	, '2014-12-01T00:00:00' --current month -11 on OlderData filegroup
	, '2015-01-01T00:00:00' --current month -10 on OlderData filegroup
	, '2015-02-01T00:00:00' --current month -9 on OlderData filegroup
	, '2015-03-01T00:00:00' --current month -8 on OlderData filegroup
	, '2015-04-01T00:00:00' --current month -7 on OlderData filegroup
	, '2015-05-01T00:00:00' --current month -6 on OlderData filegroup
	, '2015-06-01T00:00:00' --current month -5 on OlderData filegroup
	, '2015-07-01T00:00:00' --current month -4 on OlderData filegroup
	, '2015-08-01T00:00:00' --current month -3 on NewerData filegroup
	, '2015-09-01T00:00:00' --current month -2 on NewerData filegroup
	, '2015-10-01T00:00:00' --current month -1 on NewerData filegroup
	, '2015-11-01T00:00:00' --current month on NewerData filegroup
	, '2015-12-01T00:00:00' --next month on NewerData filegroup
	, '2016-01-01T00:00:00' --future month
)
GO

--main table partition scheme
CREATE PARTITION SCHEME PS_12MonthSlidingWindow
AS PARTITION PF_12MonthSlidingWindow
TO (
	  FG_OlderData --older than 12 montths
	, FG_OlderData --current month -12 on OlderData
	, FG_OlderData --current month -11 on OlderData
	, FG_OlderData --current month -10 on OlderData
	, FG_OlderData --current month -9 on OlderData
	, FG_OlderData --current month -8 on OlderData
	, FG_OlderData --current month -7 on OlderData
	, FG_OlderData --current month -6 on OlderData
	, FG_OlderData --current month -5 on OlderData
	, FG_OlderData --current month -4 on OlderData
	, FG_OlderData --current month -3 on NewerData
	, FG_NewerData --current month -2 on NewerData
	, FG_NewerData --current month -1 on NewerData
	, FG_NewerData --current month on NewerData
	, FG_NewerData --next month on NewerData
	, FG_NewerData --future month on NewerData
      );
GO

--secondary partition function identical to main function
CREATE PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary (datetime2(0))
AS RANGE RIGHT FOR VALUES (
	  '2014-11-01T00:00:00' --current month -12 on OlderData filegroup
	, '2014-12-01T00:00:00' --current month -11 on OlderData filegroup
	, '2015-01-01T00:00:00' --current month -10 on OlderData filegroup
	, '2015-02-01T00:00:00' --current month -9 on OlderData filegroup
	, '2015-03-01T00:00:00' --current month -8 on OlderData filegroup
	, '2015-04-01T00:00:00' --current month -7 on OlderData filegroup
	, '2015-05-01T00:00:00' --current month -6 on OlderData filegroup
	, '2015-06-01T00:00:00' --current month -5 on OlderData filegroup
	, '2015-07-01T00:00:00' --current month -4 on OlderData filegroup
	, '2015-08-01T00:00:00' --current month -3 on NewerData filegroup
	, '2015-09-01T00:00:00' --current month -2 on NewerData filegroup
	, '2015-10-01T00:00:00' --current month -1 on NewerData filegroup
	, '2015-11-01T00:00:00' --current month on NewerData filegroup
	, '2015-12-01T00:00:00' --next month on NewerData filegroup
	, '2016-01-01T00:00:00' --future month
)
GO

--secondary partition scheme identical to main scheme
CREATE PARTITION SCHEME PS_12MonthSlidingWindow_Secondary
AS PARTITION PF_12MonthSlidingWindow_Secondary
TO (
	  FG_OlderData --older than 12 montths
	, FG_OlderData --current month -12 on OlderData
	, FG_OlderData --current month -11 on OlderData
	, FG_OlderData --current month -10 on OlderData
	, FG_OlderData --current month -9 on OlderData
	, FG_OlderData --current month -8 on OlderData
	, FG_OlderData --current month -7 on OlderData
	, FG_OlderData --current month -6 on OlderData
	, FG_OlderData --current month -5 on OlderData
	, FG_OlderData --current month -4 on OlderData
	, FG_OlderData --current month -3 on NewerData
	, FG_NewerData --current month -2 on NewerData
	, FG_NewerData --current month -1 on NewerData
	, FG_NewerData --current month on NewerData
	, FG_NewerData --next month on NewerData
	, FG_NewerData --future month on NewerData
      );
GO

--main partitioned table
CREATE TABLE dbo.SlidingWindowTable(
	  PartitioningColumn datetime2(0)
	, OtherKeyColumn int NOT NULL
	, OtherData int NULL
	, CONSTRAINT PK_SlidingWindowTable PRIMARY KEY
		CLUSTERED (PartitioningColumn, OtherKeyColumn)
		ON PS_12MonthSlidingWindow(PartitioningColumn)
	) ON PS_12MonthSlidingWindow(PartitioningColumn);
GO

--The staging table initially uses the same scheme as the main table
--except with maintenance is in progress.
CREATE TABLE dbo.SlidingWindowTable_Staging(
	  PartitioningColumn datetime2(0)
	, OtherKeyColumn int NOT NULL
	, OtherData int NULL
	, CONSTRAINT PK_SlidingWindowTable_Staging PRIMARY KEY
		CLUSTERED (PartitioningColumn, OtherKeyColumn)
		ON PS_12MonthSlidingWindow(PartitioningColumn)
	) ON PS_12MonthSlidingWindow(PartitioningColumn);
GO

--This second staging table uses the same scheme as the main table and
--is used to switch data out of the main table after a partition is copied.
CREATE TABLE dbo.SlidingWindowTable_Staging2(
	  PartitioningColumn datetime2(0)
	, OtherKeyColumn int NOT NULL
	, OtherData int NULL
	, CONSTRAINT PK_SlidingWindowTable_Staging2 PRIMARY KEY
		CLUSTERED (PartitioningColumn, OtherKeyColumn)
		ON PS_12MonthSlidingWindow(PartitioningColumn)
	) ON PS_12MonthSlidingWindow(PartitioningColumn);
GO

WITH
	t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
	,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
	,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) - 1 AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
INSERT INTO dbo.SlidingWindowTable WITH (TABLOCKX) (PartitioningColumn, OtherKeyColumn, OtherData) 
SELECT DATEADD(month, num/1000000, '20141101'), num, num
FROM t16M
WHERE num <= 13000000;
GO

CREATE PROCEDURE dbo.PerformMonthlySlidingWindowMaintenace
	  @ExpiredMonthStartDate datetime2(0) --purge data for this month
	, @MonthStartDateToMoveFromCurrentToOld datetime2(0) --move data from this month from current data filegroup to old data filegroup
	, @FutureMonthStartDate datetime2(0) --create new partition for this date on current data filegroup
/*
	This proc performs partition maintenance needed to maintain a monthly sliding
	window with filegroups on tiered storage.  The objective is to keep recent frequently
	used data on filegroup FG_NewData and less often accessed data on PS_12MonthSlidingWindow_Secondary.
*/
AS

SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @result int;

BEGIN TRY

	BEGIN TRAN;

	--acquire exclusive lock on main table to prevent deadlocking during partition maintenance
	SET @result = (SELECT TOP (0) 1 FROM dbo.SlidingWindowTable WITH (TABLOCKX));

	--**************************
	--*** Purge expired data ***
	--**************************
	--purge oldest partition from main table (including partiton for data older than first boundary)
	TRUNCATE TABLE dbo.SlidingWindowTable_Staging;
	ALTER TABLE dbo.SlidingWindowTable
		SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow(DATEADD(month, -1, @ExpiredMonthStartDate))
		TO dbo.SlidingWindowTable_Staging PARTITION $PARTITION.PF_12MonthSlidingWindow_Secondary(DATEADD(month, -1, @ExpiredMonthStartDate));
	ALTER TABLE dbo.SlidingWindowTable
		SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow(@ExpiredMonthStartDate)
		TO dbo.SlidingWindowTable_Staging PARTITION $PARTITION.PF_12MonthSlidingWindow_Secondary(@ExpiredMonthStartDate);
	TRUNCATE TABLE dbo.SlidingWindowTable_Staging;

	--remove expired partition boundary from both functions after purge
	ALTER PARTITION FUNCTION PF_12MonthSlidingWindow()
		MERGE RANGE (@ExpiredMonthStartDate);
	ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
		MERGE RANGE (@ExpiredMonthStartDate);

	--*******************************
	--*** Prepare for future data ***
	--*******************************
	--add new partition on new data filegroup for future data to both functions/schemes
	ALTER PARTITION SCHEME PS_12MonthSlidingWindow
			  NEXT USED FG_NewerData;
	ALTER PARTITION FUNCTION PF_12MonthSlidingWindow()
		SPLIT RANGE (@FutureMonthStartDate);
	ALTER PARTITION SCHEME PS_12MonthSlidingWindow_Secondary
			  NEXT USED FG_NewerData;
	ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
		SPLIT RANGE (@FutureMonthStartDate);

	--this will release the exclusve table lock
	COMMIT;

	--********************************************************
	--*** move partition from FG_NewerData to FG_OlderData ***
	--********************************************************
	--alter secondary partition function and scheme to move partition from FG_NewerData to FG_OlderData
	ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
		MERGE RANGE (@MonthStartDateToMoveFromCurrentToOld);
	ALTER PARTITION SCHEME PS_12MonthSlidingWindow_Secondary
		NEXT USED FG_OlderData;
	ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
		SPLIT RANGE (@MonthStartDateToMoveFromCurrentToOld);

	--rebuild staging table using new partition scheme to move partition to filegroup FG_OlderData
	CREATE UNIQUE CLUSTERED INDEX PK_SlidingWindowTable_Staging
		ON dbo.SlidingWindowTable_Staging(PartitioningColumn, OtherKeyColumn)
		WITH(DROP_EXISTING=ON)
		ON PS_12MonthSlidingWindow_Secondary(PartitioningColumn);

	--copy data from main table into corresponding staging table partition, now on FG_OlderData
	INSERT INTO dbo.SlidingWindowTable_Staging WITH(TABLOCKX) (PartitioningColumn, OtherKeyColumn, OtherData)
		SELECT
			  PartitioningColumn
			, OtherKeyColumn
			, OtherData
		FROM dbo.SlidingWindowTable
		WHERE
			PartitioningColumn >= @MonthStartDateToMoveFromCurrentToOld
			AND PartitioningColumn < DATEADD(month, 1, @MonthStartDateToMoveFromCurrentToOld);

	BEGIN TRAN;

	--acquire exclusive lock on main table to prevent deadlocking during partition maintenance
	SET @result = (SELECT TOP (0) 1 FROM dbo.SlidingWindowTable WITH (TABLOCKX));

	--make sure the second staging table is empty
	TRUNCATE TABLE dbo.SlidingWindowTable_Staging2;

	--switch out partition to be moved on FG_NewData into second staging table
	ALTER TABLE dbo.SlidingWindowTable
		SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow(@MonthStartDateToMoveFromCurrentToOld)
		TO dbo.SlidingWindowTable_Staging2 PARTITION $PARTITION.PF_12MonthSlidingWindow(@MonthStartDateToMoveFromCurrentToOld);

	--remove redundant copy of data
	TRUNCATE TABLE dbo.SlidingWindowTable_Staging2;

	--switch the latest partition of main table on the FG_OlderData filegroup to the staging table
	--so that adjacent partitions of the main table are empty for the MERGE and SPLIT needed to 
	--physically move the main table partition to FG_OlderData
	ALTER TABLE dbo.SlidingWindowTable
		SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow(DATEADD(month, -1, @MonthStartDateToMoveFromCurrentToOld))
		TO dbo.SlidingWindowTable_Staging PARTITION $PARTITION.PF_12MonthSlidingWindow_Secondary(DATEADD(month, -1, @MonthStartDateToMoveFromCurrentToOld));

	--remove empty partition
	ALTER PARTITION FUNCTION PF_12MonthSlidingWindow()
		MERGE RANGE (@MonthStartDateToMoveFromCurrentToOld);

	--create new empty partition on old data filegroup for moved data
	ALTER PARTITION SCHEME PS_12MonthSlidingWindow
			  NEXT USED FG_OlderData;
	ALTER PARTITION FUNCTION PF_12MonthSlidingWindow()
		SPLIT RANGE (@MonthStartDateToMoveFromCurrentToOld);

	--now that both partition functions/schemes are identical, switch partitions from staging table back into main table
	ALTER TABLE dbo.SlidingWindowTable_Staging
		SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow_Secondary(DATEADD(month, -1, @MonthStartDateToMoveFromCurrentToOld))
		TO dbo.SlidingWindowTable PARTITION $PARTITION.PF_12MonthSlidingWindow(DATEADD(month, -1, @MonthStartDateToMoveFromCurrentToOld));
	ALTER TABLE dbo.SlidingWindowTable_Staging
		SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow_Secondary(@MonthStartDateToMoveFromCurrentToOld)
		TO dbo.SlidingWindowTable PARTITION $PARTITION.PF_12MonthSlidingWindow(@MonthStartDateToMoveFromCurrentToOld);

	COMMIT;

END TRY
BEGIN CATCH

	IF @@TRANCOUNT > 0 ROLLBACK;

	THROW;

END CATCH;
GO

CHECKPOINT
DBCC DROPCLEANBUFFERS
--when scheduling after the start of a new month, set @RunMonth to the first day of the current month
DECLARE
	--@RunMonth datetime2(0) = DATEADD(day, 1, DATEADD(month, -1, EOMONTH(GETDATE())));
	  @RunMonth datetime2(0) = '20151201';

--calculate boundary dates based on @RunMonth
DECLARE
	  @ExpiredMonthStartDate datetime2(0) = DATEADD(month, -13, @RunMonth)
	, @MonthStartDateToMoveFromCurrentToOld datetime2(0) = DATEADD(month, -4, @RunMonth)
	, @FutureMonthStartDate datetime2(0) = DATEADD(month, 2, @RunMonth);

SELECT
	  @ExpiredMonthStartDate AS ExpiredMonthStartDate
	, @MonthStartDateToMoveFromCurrentToOld AS MonthStartDateToMoveFromCurrentToOld
	, @FutureMonthStartDate AS FutureMonthStartDate;

EXEC dbo.PerformMonthlySlidingWindowMaintenace
	  @ExpiredMonthStartDate = @ExpiredMonthStartDate
	, @MonthStartDateToMoveFromCurrentToOld = @MonthStartDateToMoveFromCurrentToOld
	, @FutureMonthStartDate = @FutureMonthStartDate;
GO