Partitioning of Temporal Tables in SQL Server 2016
Microsoft SQL Server 2016 introduces system-versioned temporal tables, which allow for automatically keeping a history of data changes in a separate history table and to retrieve the status of your data at any time in the past. Here I don’t want to go into all the gory details of their implementation. See the relevant BOL pages https://msdn.microsoft.com/en-US/library/dn935015.aspx or https://blogs.technet.com/b/dataplatforminsider/archive/2016/01/11/effortlessly-analyze-data-history-using-temporal-tables.aspx for an exhaustive description of temporal tables.
In this Blog I just want to show how table partitioning affects temporal tables. Like any other table, temporal tables can be partitioned (at least, if you are running on Enterprise Edition of SQL Server). In order to partition a temporal table while creating the table, simply add the ON (<partition scheme>) clause to the CREATE TABLE statement, e.g.
CREATE TABLE MyTemporalTable (
Id int PRIMARY KEY,
Data nvarchar(1000) NULL,
ValidFrom datetime2 GENERATED ALWAYS AS ROW START,
ValidTo datetime2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) ON MyPartScheme (Id)
WITH (SYSTEM_VERSIONING = ON)
But note, when doing so, the partitioning column has to be part of the primary key that has to be specified with each temporal table. If you do not explicitly specify a name for the history table, one will be generated automatically for you with the name MSSQL_TemporalHistoryFor_nnnn, where nnnn is the object ID of the temporal table. And, this history table is going to be partitioned the same way as the underlying temporal table, i.e. using the same partitioning column and the same partition scheme and function. But, having more than one table partitioned using the same partition function is not a good practice, since every Merge or Split operation on the partition function affects both tables at the same time and may cause undesired data movements if you do not pay attention to it. Therefore, I strongly advise not to partition a temporal table while creating it together with the associated history table. And, by the way, I do not see any good reason for partitioning the temporal table and its history table on the very same column and using the same partition scheme and function, since both tables serve different purposes.
Partitioning the Temporal Table
So let us view the aspects of partitioning for each of the tables separately. For the temporal table you typically might consider partitioning if you want to balance data storage on different filegroups and their associated physical data files, or if you want to utilize partitions for fast data loading. Note, that adding new rows to a temporal table has no effect on the history table. Therefore, switching data into a partition of a temporal table is a valid operation. For a table to be switched in the PERIOD FOR SYSTEM_TIME columns must be defined identically, except for the HIDDEN attribute, and the indexes must match.
If the temporal table is being created like:
CREATE TABLE MyTemporalTable (
Id int CONSTRAINT PK_MyTemporalTable PRIMARY KEY,
Data nvarchar(1000) NULL,
ValidFrom datetime2 GENERATED ALWAYS AS ROW START,
ValidTo datetime2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON)
You may partition this table by recreating the primary key:
CREATE UNIQUE CLUSTERED INDEX PK_MyTemporalTable ON MyTemporalTable (Id)
WITH (DROP_EXISTING = ON) ON MyPartScheme (Id)
Next, create the staging table to be switched in and fill it with data (declaring the system_time columns as HIDDEN might be beneficial here):
CREATE TABLE MyTemporalStaging (
Id int PRIMARY KEY,
Data nvarchar(1000) NULL,
ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN,
ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
And finally, after splitting the last partition, switch the staging table into the new last partition of the temporal table:
ALTER TABLE MyTemporalStaging SWITCH TO MyTemporalTable PARTTION @last_partition
On the other hand, switching partitions out from the temporal table effectively means deleting rows and therefore must be reflected in the history table as well. Because this cannot be handled by the system, switching partitions out of a temporal table is not a supported operation and returns error 13546 when doing so. If you really need to switch out partitions from a temporal table, you first have to disable system versioning on the temporal table, and turn it on again after the switch.
Partitioning of the History Table for Managing Data Retention
A temporal history table steadily grows as you perform data modifications on your temporal table. Hence, the history table might become really big, and at some point you may face the need to remove old and no longer needed row versions from your history table in order to reclaim space. As of CTP 3 automatic data retention isn’t yet implemented for temporal history tables. So you have to do it on your own. See also https://msdn.microsoft.com/en-us/library/mt637341.aspx.
But, simply deleting rows from the history table isn’t allowed. You have to disable system versioning prior to this, and you better perform these steps within a transaction in order to block any data modification on the temporal table while system versioning is being disabled. So, this would look like:
BEGIN TRANSACTION
ALTER TABLE <temporal table> SET (SYSTEM_VERSIONING = OFF)
DELETE FROM <history table> WHERE <period end column> <= @cut_off_date
ALTER TABLE <temporal table> SET (SYSTEM_VERSIONING = ON
(HISTORY_TABLE = <history table>, DATA_CONSISTENCY_CHECK = OFF))
COMMIT TRANSACTION
Depending on the amount of rows being deleted, this might cause lock escalation and result in an exclusive table lock on the history table, which is further propagated to the temporal table, and thus, also blocks all read operations on the temporal table for the duration of this transaction. Moreover, this operation may result in a huge amount of rows being generated in the transaction log. Thus, for a big history table this is not the recommended procedure for data cleansing.
Since switching partitions out of the history table is a supported operation, partitioning of the history table is the preferred method to implement such a data retention scheme on a temporal history table. Simply create a partition function that covers your envisioned retention period and deletion frequency and partition the history table on the end column of the system time period. Note, that the clustered index that is created automatically on the history table has the two system time period columns as key columns and therefore, is perfectly suited for partitioning on this column.
If your temporal table has been defined as
CREATE TABLE dbo.TemporalTable (
Id int PRIMARY KEY,
Data nvarchar(1000) NULL,
ValidFrom datetime2 GENERATED ALWAYS AS ROW START,
ValidTo datetime2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalHistory))
the history table, if it does not yet exist, will be created equivalent to
CREATE TABLE dbo.TemporalHistory (
Id int NOT NULL,
Data nvarchar(1000) NULL,
ValidFrom datetime2 NOT NULL,
ValidTo datetime2 NOT NULL,
INDEX ix_TemporalHistory CLUSTERED (ValidTo, ValidFrom) WITH (DATA_COMPRESSION = PAGE)
)
Therefore, if you’re going to partition the history table on column ‘ValidTo’ using the previously created partition scheme ‘MyPartScheme’, you can accomplish this by recreating the clustered index:
CREATE CLUSTERED INDEX ix_TemporalHistory ON dbo.TemporalHistory (ValidTo, ValidFrom)
WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, DROP_EXISTING = ON) ON MyPartScheme (ValidTo)
If you have created your own indexes on the history table to replace or supplement the default clustered index, the procedure to partition the history table might look slightly different. Especially, if you have defined a columnstore index, that index has to be dropped prior to partitioning the history table, and recreated afterwards.
Now, whenever you are going to delete the data in the partition with the oldest ‘ValidTo’ values, you have to create an archive table that matches the structure and the clustered index of the history table, switch the partition out to this archive table, merge the two left most partitions, and finally drop the archive table. Let’s say ‘MyPartFunction’ is the name of the partition function our partition scheme ‘MyPartScheme’ is based on, then the following batch would accomplish this maintenance job (with RANGE RIGHT partitions, the leftmost partition should be left empty):
DECLARE @v datetime2, @p int;
SELECT @v = CONVERT(datetime2, prv.value), @p = 1 + pf.boundary_value_on_right
FROM sys.partition_functions pf JOIN sys.partition_range_values prv
ON prv.function_id = pf.function_id AND prv.parameter_id = 1 AND prv.boundary_id = 1
WHERE pf.name = 'MyPartFunction';
CREATE TABLE dbo.TemporalHistoryArchive (
Id int NOT NULL,
Data nvarchar(1000) NULL,
ValidFrom datetime2 NOT NULL,
ValidTo datetime2 NOT NULL
INDEX ix_TemporalHistoryArchive CLUSTERED (ValidTo, ValidFrom) WITH (DATA_COMPRESSION = PAGE)
);
ALTER TABLE dbo.TemporalHistory SWITCH PARTITION @p TO dbo.TemporalHistoryArchive;
ALTER PARTITION FUNCTION MyPartFunction() MERGE RANGE (@v);
DROP TABLE dbo.TemporalHistoryArchive;
Although SQL Server 2016 added the WITH PARTTITIONS clause to the TRUNCATE TABLE statement to allow for operations like that, using the statement “TRUNCATE TABLE TemporalHistory WITH (PARTITIONS (@p))” returns error 13545. Hence, even if you do not intend to keep the archived data, you have to follow the more cumbersome process of creating an archive table, and dropping it again after having switched out the partition to it.
Note, that the next version of PTF on https://ptf.codeplex.com will support SQL Server 2016 and temporal tables. Thereby, the above maintenance script could be substituted with the single statement:
EXEC ptf.DropPartitions 'dboTemporalHistory'
Summary
With temporal tables, both the temporal table itself, as well as its associated history table can be partitioned. On the partitioned temporal table, the only supported operation is the switch in, while on the partitioned history table we only may switch out partitions without disabling system versioning.
Especially, partitioning the history table is recommended if you want to implement some means of data cleansing in response to excessive data growth in this table.