Categories
SQL

Additional Temporal Table Features

We’ve recently been looking at temporal tables, how they work, we query them and how to modify them. All of these are consistent since the feature was added in SQL Server 2016. There are however a couple of extra features which were added in 2017 to make these even more usable.

Let’s use our previous example as a starting point:

CREATE TABLE dbo.TemporalSample
(
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100),
  StockQuantity INT,
  StartDate DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN,
  EndDate DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN,
  PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
 )
WITH (
	SYSTEM_VERSIONING = ON (
		HISTORY_TABLE = dbo.TemporalSampleHistory
	));
	
INSERT INTO TemporalSample (
	ProductID,
	ProductName,
	StockQuantity
)
VALUES (1, 'Apples', 10),
	(2, 'Pears', 15),
	(3, 'Oranges', 8);

Cascading foreign keys

The first of these new features is the ability to use the CASCADE clause in foreign key definitions to propagate changes from the reference table. As an example we can specify that deleting a record from the reference table will delete related records from any table which references that key. In the initial SQL Server 2016 release foreign keys are allowed against the temporal tables without this clause such as below:

CREATE TABLE dbo.ProductType (
	TypeID INT PRIMARY KEY,
	TypeName VARCHAR(50)
);

INSERT INTO dbo.ProductType (
	TypeID,
	TypeName
)
VALUES (1, 'Fruit'), 
	(2, 'Vegetables');

ALTER TABLE dbo.TemporalSample
ADD ProductTypeID INT NULL;

UPDATE dbo.TemporalSample
SET ProductTypeID = 1;

ALTER TABLE dbo.TemporalSample
ADD CONSTRAINT FK_ProductTypeID
FOREIGN KEY (ProductTypeID)
REFERENCES dbo.ProductType (TypeID);

That’s all well and good, however when we try to replace this with a new foreign key with cascading deletes in SQL Server 2016 we run into a bit of an issue:

ALTER TABLE dbo.TemporalSample
DROP CONSTRAINT FK_ProductTypeID;

ALTER TABLE dbo.TemporalSample
ADD CONSTRAINT FK_ProductTypeID
FOREIGN KEY (ProductTypeID)
REFERENCES dbo.ProductType (TypeID)
ON DELETE CASCADE;

Cannot define foreign key constraint ‘FK_ProductTypeID’ with cascaded DELETE or UPDATE on table ‘TemporalSample’ because table is system-versioned.

Note that this is running on the SQL Server 2016 database engine. Running the same on SQL Server 2017 or later will allow this to be done regardless of the compatibility level of the database.

Running the same statement on a SQL Server 2017 or higher instance will allow the foreign key to be created and we can now remove the records and see the results cascading through the current and history tables:

ALTER TABLE dbo.TemporalSample
ADD CONSTRAINT FK_ProductTypeID
FOREIGN KEY (ProductTypeID)
REFERENCES dbo.ProductType (TypeID)
ON DELETE CASCADE;

DELETE FROM dbo.ProductType
WHERE TypeID = 1;

/* The table is now empty */
SELECT *
FROM dbo.TemporalSample;

/* The deleted records show here */
SELECT *
FROM dbo.TemporalSampleHistory

Data retention

When using temporal tables, we can end up with the history table growing quite large in situations where we see a lot of data movement. In some cases we may want to look at reducing the amount of history to retain.

In the original implementation in SQL Server 2016 the only way to help manage these data volumes was through manual intervention and purging of the data we didn’t want. This would typically mean one of the following:

  • Writing a custom clean-up script
  • Partitioning the table and switching partitions out
  • Stretching your history table into Azure

However come SQL Server 2017 a new feature was added for automated data retention via a policy which can be specified against a temporal table. This can be specified when applying the SYSTEM_VERSIONING to the table and can accept the time period in units DAYS, WEEKS, MONTHS, YEARS and INFINITE (the default), for example:

ALTER TABLE dbo.TemporalSample
SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE dbo.TemporalSample
SET (SYSTEM_VERSIONING = ON (
		HISTORY_TABLE = dbo.TemporalSampleHistory,
		HISTORY_RETENTION_PERIOD = 12 MONTHS
	));

Once this is in place then the engine will automatically look to remove records older than the retention period.

We previously looked at the system view for tables to identify temporal tables in our database. From SQL Server 2017 onwards there are a few new fields in here where we can see how the retention is configured:

SELECT
	t.[name],
	t.temporal_type,
	t.temporal_type_desc,
	t.history_retention_period,
	t.history_retention_period_unit_desc
FROM sys.tables t
	INNER JOIN sys.partitions p
		ON t.[object_id] = p.[object_id]
WHERE [type] = 'U';

It should be noted that the database flag is_temporal_history_retention_enabled needs to be enabled for the automatic retention to work however it is applied by default on databases so you shouldn’t need to be changed unless it’s already been altered.

Also in the event that the SYSTEM_VERSIONING on a table is removed and re-added then the retention will need to be re-specified in the declaration as this won’t be maintained when the versioning is removed.

You can find more details about the data retention and other methods for managing the retention of data in the history table in the official documentation.

Wrap up

The points above were a couple of extra features I’ve come across with temporal tables over time. Although I can’t seem to find any specific release notes covering these in SQL Server 2017 or later it’s a good sign to see the feature given some attention.

These features are a little more edge-case than the other points we’ve covered. It’s sometimes nice to dive a little deeper into these areas to see what we find even if they aren’t immediately useful to us, at least we’ll have a little something in our back pockets if we need to call on it.

In this series

Leave a comment