Quick Start: SQL Server Temporal Tables
Published on •By Joey Davis
From slowly changing dimensions in data warehousing, to rapidly changing IoT sensor data, having a historical reference of when and how data changes is a valuable asset. Especially in the enterprise database environment, the ability to track data changes is critical for point-in-time analysis, auditing, and even debugging application processes or errors that generate incorrect data. History tables serve this purpose by describing changes to data over time.
Starting with SQL Server 2016, system-versioned temporal tables provide built-in support for historical record management. A history of data changes is automatically maintained for temporal tables and accompanied by several T-SQL extensions for querying and analyzing the data at particular points in time. Here's how to get started.
Temporal Table Setup
Below is an example of the DDL for a temporal table called DeliveryMethods. This is a simplified table for demonstration purposes that might appear in the database for an e-commerce platform. We'll dig into specific details following the example.
_13 CREATE TABLE [dbo].[DeliveryMethods]_13 (_13 [DeliveryMethodID] INT NOT NULL PRIMARY KEY,_13 [DeliveryMethodName] NVARCHAR(100) NOT NULL,_13 [UserSelectionOrder] INT NOT NULL,_13 [ModifiedBy] INT NOT NULL,_13 _13 [SysStartTime] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,_13 [SysEndTime] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,_13 PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])_13 ) WITH (SYSTEM_VERSIONING = ON (_13 HISTORY_TABLE = [dbo].[DeliveryMethodsHistory]_13 ));
Validity Period
If you are familiar with the management of historical data changes, then you are familiar with the concept of a validity period. This period is the range of time for which a given row instance, or fact, is or was considered valid relative to the real world.
The SQL Server database engine manages the period of validity for each row automatically using two columns defined explicitly by the user. The two columns have the DATETIME2 data type and represent the period start (frequently named SysStartTime) and period end (frequently named SysEndTime). While the user defines the names of these columns, users typically specify them as SysStartTime and SysEndTime to indicate that they are system-maintained columns and the system start and end time for each row. In scenarios when the validity period is not managed by the system, these same columns are commonly named ValidFrom and ValidTo.
History Table
Temporal tables contain a reference to a history table having the same schema. The database engine uses the history table to store the previous version of a row when it is updated or deleted from the temporal table. The temporal table always contains the current version of the record, so long as it is currently in existence.
In the DDL provided above, we specified a specific name for the history table. Specifying the history table by name is not required as SQL Server provides a generated history table name in its absence. The table name may already exist, but it must match the temporal table schema; otherwise, the result is an error. When there isn't an existing history table specified, the database engine derives the table from the temporal table schema.
Data Retention
SQL Server temporal tables provide built-in retention policy support for data through the HISTORY_RETENTION_PERIOD parameter. Through this parameter, it is possible to specify the retention period for historical data when defining a temporal table. The retention period specification allows for DAYS, WEEKS, MONTHS, or YEARS. Here is an example:
_13 CREATE TABLE [dbo].[GyroscopeMeasurement]_13 (_13 [SensorID] INT NOT NULL PRIMARY KEY,_13 [ValueX] INT NULL,_13 [ValueY] INT NULL,_13 [ValueZ] INT NULL,_13 [SysStartTime] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,_13 [SysEndTime] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,_13 PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])_13 ) WITH (SYSTEM_VERSIONING = ON (_13 HISTORY_TABLE = [dbo].[GyroscopeMeasurementHistory],_13 HISTORY_RETENTION_PERIOD = 5 DAYS_13 ));
In the example above, we've specified a retention period of 5 days for gyroscope measurements received from an IoT device. Assuming the database has the TEMPORAL_HISTORY_RETENTION option set to ON (the default, but turned off after certain restore operations), a background task in SQL Server manages the cleanup of data as it ages beyond the retention period.
If the temporal table already exists and you would like to configure temporal history retention for the table, you may accomplish this by altering the table:
_10 ALTER TABLE [dbo].[GyroscopeMeasurement] SET (SYSTEM_VERSIONING = ON (_10 HISTORY_TABLE = [dbo].[GyroscopeMeasurementHistory],_10 HISTORY_RETENTION_PERIOD = 5 DAYS_10 ));
Identifying Temporal Tables
In SQL Server Management Studio (SSMS) and Azure Data Studio, temporal tables are indicated by a trailing (System-Versioned) label next to the table name. Also note the history table appears nested beneath its dependent temporal table rather than in the list of other tables. It is indicated by a trailing (History) label next to the table name.
Temporal tables are also discernible from the [sys].[tables] view. The column history_table_id indicates the object_id of the history table.
_10 SELECT _10 [name],_10 [object_id],_10 [temporal_type],_10 [temporal_type_desc],_10 [history_table_id]_10 FROM sys.tables_10 WHERE [temporal_type] = 2;
Temporal Table Data
Querying Data in a Temporal Table
For selecting any current data (the current version), you need not make any adjustments to the query syntax. By default, only current rows are returned. The FOR SYSTEM_TIME clause provides the ability to query rows by validity period or a particular point in time.
ALL Subclause
To get all versions from all time, including both current and deleted records, specify FOR SYSTEM_TIME ALL. Querying in this manner is functionally equivalent to a union query of both the temporal table and its history table.
_10 SELECT_10 * _10 FROM [dbo].[MyTable] FOR SYSTEM_TIME ALL;
AS OF Subclause
To retrieve the rows that were current at a particular point in time, specify FOR SYSTEM_TIME AS OF '2019-12-31' in the query.
_10 SELECT_10 * _10 FROM [dbo].[MyTable] FOR SYSTEM_TIME AS OF '2019-12-31';
FROM..TO Subclause
To retrieve rows that were current within a particular range of time, specify FOR SYSTEM_TIME FROM [start] TO [end]. A query specifying the FROM/TO syntax includes records that were active beginning before, but not including, the value specified for [end].
_10 SELECT_10 *_10 FROM [dbo].[MyTable] FOR SYSTEM_TIME FROM '2019-12-31' TO '2020-01-02';
BETWEEN Subclause
To retrieve rows that were current within a particular range of time (including the upper boundary), specify FOR SYSTEM_TIME BETWEEN [start] AND [end]. This includes rows having a start time equal to the [end] value specified.
SELECT * FROM [dbo].[MyTable] FOR SYSTEM_TIME BETWEEN '2019-12-31' AND '2020-01-02';
Beyond selecting data directly from a table, the FOR SYSTEM_TIME clause may also be passed to views containing a temporal table. This greatly simplifies the logic necessary for constructing a view that provides historical data insight.
Inserting Data into a Temporal Table
Inserting data into a temporal table is identical to any other table in SQL Server. If the period columns appear in the column list of the insert statement, or the column list of the insert statement is unspecified, then only the value DEFAULT should appear as the value for that column in the values list. Since the system manages these columns, the database engine disallows manual modification or creation of these values.
_16 INSERT INTO [dbo].[DeliveryMethods] (_16 [DeliveryMethodID],_16 [DeliveryMethodName],_16 [UserSelectionOrder],_16 [ModifiedBy],_16 [SysStartTime],_16 [SysEndTime]_16 )_16 VALUES (_16 1,_16 '2nd Day Air A.M.',_16 1,_16 0,_16 DEFAULT,_16 DEFAULT_16 )
Here's the recommended method of omitting the period start and end columns while inserting a new record:
_12 INSERT INTO [dbo].[DeliveryMethods] (_12 [DeliveryMethodID],_12 [DeliveryMethodName],_12 [UserSelectionOrder],_12 [ModifiedBy]_12 )_12 VALUES (_12 1,_12 '2nd Day Air A.M.',_12 1,_12 0_12 )
When the database engine inserts a new record into a temporal table, it uses the beginning time from the current transaction as the period start value and the maximum value for the DATETIME2 data type (9999-12-31 23:59:59.9999999) as the period end value. When a record is current (all records in the temporal table), the value from the period end is the same maximum DATETIME2 value.
Updating Data in a Temporal Table
When the database engine updates a record in a temporal table, it inserts the previously current version of the row into the history table and records the system time as the period end time. The row in the current, temporal table receives the updated values and the system time as the period start time. Both the period end column of the old current version (now in the history table) and the period start column of the updated record (in the temporal table) become the time of the current transaction.
The recordset returned by using the SYSTEM_TIME ALL or querying by a period containing multiple versions of a record and ordering by the period start or period end column illustrates this point. You can see how the historical records align by comparing the period start and period end times.
It is possible to update a temporal table using values from the history table. From the earlier example of the DeliveryMethods table, imagine an administrator of the e-commerce application made a change to the delivery method name that was confusing to the other stakeholders involved. The administrator wants to revert the delivery method name to an earlier version, and the application accomplishes this by submitting a query referencing the earlier point in time, as shown below.
_10 UPDATE [DeliveryMethods] SET_10 [DeliveryMethodName] = History.[DeliveryMethodName]_10 FROM [DeliveryMethodsHistory] FOR SYSTEM_TIME AS OF '2019-12-01' AS History _10 WHERE History.DeliveryMethodID = 123 AND [DeliveryMethods].DeliveryMethodID = 123;
Deleting Data in a Temporal Table
When a query results in the deletion of a current row from the temporal table, the row relocates to the history table. The period-end column value becomes the system time from the beginning of the transaction containing the deletion operation.
It is okay for the row's primary key to appear in the temporal table again in the future. Keep in mind that if the period start and period end columns do not line up based on the primary key, this indicates some process deleted the record for a length of time, and then it or some other process later re-created the record. For example, in the DeliveryMethods table from our example, this may indicate a user deleted a delivery method and then later restored it.
Schema Changes
Changes to the schema of temporal tables are reasonably straightforward. In many cases, such as adding a column or setting a new default value, SQL Server applies the change to both the temporal table and its history table automatically. However, there are instances where you must make changes within a transaction to disable system-versioning, make the change, and immediately re-enable system-versioning. No data changes occur for the duration of the transaction, so this is fine to do.
_10 BEGIN TRANSACTION_10 ALTER TABLE [dbo].[MyTable] SET (SYSTEM_VERSIONING = OFF);_10 -- make changes here_10 ALTER TABLE [dbo].[MyTable] SET (SYSTEM_VERSIONING = ON (_10 HISTORY_TABLE = [dbo].[MyTableHistory]_10 ));_10 COMMIT;
Dropping a system-versioned temporal table is not a supported operation. To drop the table, you must first set SYSTEM_VERSIONING to OFF.
_10 ALTER TABLE [dbo].[DeliveryMethods] SET (SYSTEM_VERSIONING = OFF)_10 GO_10 DROP TABLE [dbo].[DeliveryMethods]_10 DROP TABLE [dbo].[DeliveryMethodsHistory]_10 GO
Conclusion
Temporal tables are an excellent method of achieving historical data management with little effort. Using them significantly reduces the boilerplate associated with tracking the history of data changes in a table and simplifies querying the data with the FOR SYSTEM_TIME clause. Consider adding them to SQL Server data projects to simplify processes and provide comprehensive historical data capabilities.