To configure the retention policy, you need to specify a value for the HISTORY_RETENTION_PERIOD parameter during table creation or after the table is created. Use the following code to create and populate a sample temporal table:
USE WideWorldImporters;
GO
CREATE TABLE dbo.T1(
Id INT NOT NULL PRIMARY KEY CLUSTERED,
C1 INT,
Vf DATETIME2 NOT NULL,
Vt DATETIME2 NOT NULL
)
GO
CREATE TABLE dbo.T1_Hist(
Id INT NOT NULL,
C1 INT,
Vf DATETIME2 NOT NULL,
Vt DATETIME2 NOT NULL
)
GO
--populate tables
INSERT INTO dbo.T1_Hist(Id, C1, Vf, Vt) VALUES
(1,1,'20171201','20171210'),
(1,2,'20171210','20171215');
GO
INSERT INTO dbo.T1(Id, C1, Vf, Vt) VALUES
(1,3,'20171215','99991231 23:59:59.9999999');
GO
Here is the content of both tables after the script execution:
Id C1 Vf Vt
--- ------- --------------------------- ---------------------------
1 3 2017-12-15 00:00:00.0000000 9999-12-3 23:59:59.9999999
Id C1 Vf Vt
--- -------- --------------------------- -----------------------------
1 1 2017-12-01 00:00:00.0000000 2017-12-10 00:00:00.0000000
1 2 2017-12-10 00:00:00.0000000 2017-12-15 00:00:00.0000000
The actual value in the C1 column for the row with the ID of 1 is 3, and there are two historical values. You will now convert the T1 table into a temporal table and so define a retention policy that states historical entries should be retained for one day only. Here is the appropriate code:
ALTER TABLE dbo.T1 ADD PERIOD FOR SYSTEM_TIME (Vf, Vt);
GO
ALTER TABLE dbo.T1 SET
(
SYSTEM_VERSIONING = ON
(
HISTORY_TABLE = dbo.T1_Hist,
HISTORY_RETENTION_PERIOD = 3 DAYS
)
);
You had to use two statements to achieve this task: the first one is used for adding a period to the table, and the second converts the table to a temporal table with a retention policy of 3 days. However, when you execute the query, you will see the following message:
Msg 13765, Level 16, State 1, Line 31
Setting finite retention period failed on system-versioned temporal table 'WideWorldImporters.dbo.T1' because the history table 'WideWorldImporters.dbo.T1_Hist' does not contain required clustered index. Consider creating a clustered columnstore or B-tree index starting with the column that matches end of SYSTEM_TIME period, on the history table.
The history table must have a row clustered index on the column representing the end of period; it won't work without it. Use this code to create the index and run the previous code again:
CREATE CLUSTERED INDEX IX_CL_T1_Hist ON dbo.T1_Hist(Vt, Vf);
GO
ALTER TABLE dbo.T1 SET
(
SYSTEM_VERSIONING = ON
(
HISTORY_TABLE = dbo.T1_Hist,
HISTORY_RETENTION_PERIOD = 3 DAYS
)
);
The execution was successful. This section is written on 17th December, so the results of the further actions will be shown according to this date. In this example, there are two historical records with the end period dates 10.12. and 15.12. According to the retention policy, only one row should be shown as a history row. To check this, use the following query:
SELECT * FROM dbo.T1 FOR SYSTEM_TIME ALL;
The query returns the following result:
Id C1 Vf Vt
---- ------- --------------------------- -------------------------
1 3 2017-12-15 00:00:00.0000000 9999-12-31 23:59:59.9999999
1 2 2017-12-10 00:00:00.0000000 2017-12-15 00:00:00.0000000
Rows that do not meet the retention policy have been removed from the result set. The execution plan for the query is shown in the following screenshot:
When you look at the execution plan, you can see that the retention policy filter is automatically applied. Therefore, historical rows older than three days are removed from the result set and it contains one actual and one historical row. Rows that meet the following criteria are not shown:
Vt < DATEADD (Day, -3, SYSUTCDATETIME ());
However, when you query the history table directly, you can still see all rows:
SELECT * FROM dbo.T1
UNION ALL
SELECT * FROM dbo.T1_Hist;
The query returns the following result:
Id C1 Vf Vt
--- ------ --------------------------- ---------------------------
1 3 2017-12-15 00:00:00.0000000 9999-12-31 23:59:59.9999999
1 1 2017-12-01 00:00:00.0000000 2017-12-10 00:00:00.0000000
1 2 2017-12-10 00:00:00.0000000 2017-12-15 00:00:00.0000000
This is not what you expected! How does SQL Server remove the aged rows? Identification of matching rows and their removal from the history table occur transparently, alongside the background tasks that are scheduled and run by the system. Since aged rows can be removed at any point in time and in arbitrary order, you should use Transact-SQL extensions for querying temporal data, as shown in the previous section.
In this example, you have used three days in the retention policy. This information is stored in the sys.tables catalog. Run the following query to check retention policy settings for the T1 table:
SELECT temporal_type_desc, history_retention_period, history_retention_period_unit
FROM sys.tables WHERE name = 'T1';
The previous query returns this result set:
The second 3 in the results represents days. If you include the other tables, you can see that all temporal tables in the WideWorldImporters database have a value of -1 in this column, which means that the retention period is not defined for a temporal table, thus the historical rows will be kept forever. Here is the list of possible retention period units you can define in an SQL Server 2017 retention policy:
- -1: INFINITE
- 3: DAY
- 4: WEEK
- 5: MONTH
- 6: YEAR
A history retention policy is good and lets you manage and control data size. However, currently you can use only one criteria to define aged rows: the end of period. If you have a lot of historical entries for some rows and a few for most of the others, this will not help you. It would be nice if you could choose additional criteria such as the number of rows or table size. You also cannot define where aged data will be moved; currently it is simply cleaned up.