Problem
I just started a new job migrating from Enterprise Edition to Standard Edition, and the databases have temporal tables. I’ve never worked with them and don’t know where to start or even what special considerations I need to be aware of.
Solution
SQL Server temporal tables, often referred to as system versioned tables, were first introduced with SQL Server 2016 and remain in place with current versions of SQL Server. Previously, Microsoft introduced Change Data Capture (CDC), which uses SQL Agent jobs, or for Azure, a service that reads the transaction logs to capture data that is changing.
System-versioned tables (temporal tables) are different than CDC in a couple of ways.
- First, enabling system-version automatically creates a historical table that records older versions of the data. CDC runs off SQL Agent, therefore, the original data is not immediately available.
- Second, system-version tables introduce T-SQL clauses that allow you to review the data recorded without joining to a historical table – the historical table is referenced behind the scenes, making the querying of historical records simpler.
- System versioned tables also have a robust set of clauses available to make it possible to parse the data to look for various changes, such as reviewing the state of a table at a particular time of day.
Considerations and Caveats to Temporal Tables
There are a considerable number of caveats to working with temporal tables. Most of the caveats are typical considerations that developers and DBAs deal with. Here are a few of the major points:
- Temporal tables must have a primary key assigned.
- Availability groups and SQL Server replication are supported.
- The history table cannot have constraints.
- The history table must be in the same database as the temporal table.
- The retention period is infinite if not specified.
- Two DateTime2 columns are required, and the time is stored in UTC.
- Indexes created on the system-versioned table are not automatically created on the archive table other than the primary key.
A full list of caveats and limitations is found on Microsoft’s site: Temporal table considerations and limitations.
Creating Temporal Tables
In the two following examples, we create two temporal tables: one with a history table we name, and the other that has a history table created by SQL Server. Both are valid and do not affect the performance of the system.
--MSSQLTips.com
CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Cities(
[CityID] [int] Identity PRIMARY KEY,
[CityName] [nvarchar](50) NOT NULL,
[StateProvinceID] [nvarchar](20) NOT NULL,
[ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
[ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [Sales].[Cities_Archive]))
GO
CREATE TABLE Sales.Cities2(
[CityID] [int] Identity PRIMARY KEY,
[CityName] [nvarchar](50) NOT NULL,
[StateProvinceID] [nvarchar](20) NOT NULL,
[ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
[ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
) WITH (SYSTEM_VERSIONING = ON)
GOIn SQL Server Management Studio (SSMS), you can see both tables. Note the name of the history table SQL Server created (second), because it was not explicitly named.
Finding Databases that have Temporal Tables
The easiest way to find system-versioned tables is to look for the special icons in SSMS. In the example below, the Sales.Cities table is a temporal (system-versioned) table, while the Sales.Cities_Archive table is the history table.
We can also query the server system tables to determine those configured as temporal tables. Microsoft provides documentation on sys.tables, including whether it’s a system-versioned or history table.
This query, provided by Microsoft, lists the system-versioned tables, as well as the retention period assigned.
--MSSQLTips.com
SELECT DB.is_temporal_history_retention_enabled,
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T1.name AS TemporalTableName,
SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
T2.name AS HistoryTableName,
T1.history_retention_period,
T1.history_retention_period_unit_desc
FROM sys.tables T1
OUTER APPLY (
SELECT is_temporal_history_retention_enabled
FROM sys.databases
WHERE name = DB_NAME()
) AS DB
LEFT JOIN sys.tables T2
ON T1.history_table_id = T2.object_id
WHERE T1.temporal_type = 2;The results show one table with system versioning enabled with a default retention period of infinite.
Querying
Microsoft provides temporal-specific sub-clauses for working with temporal tables. Each of these sub-clauses requires the “FOR SYSTEM_TIME” clause. The sub-clauses are:
- AS OF – Use this sub-clause to get a picture of the data at a specific point in time.
- ALL – Shows all the records.
- FROM TO, BETWEEN and CONTAINED IN – Provide the ability to look at data over the expanse of a range.
Standard T-SQL is available to work with the system-versioned table and requires no changes. However, note that you will always have a ‘ValidFrom’ and ‘ValidTo’ set of columns to contend with. These columns can be hidden when the table is created.
Demo
To demonstrate the capabilities of these clauses, let’s insert data into our table and update it over a period of time to be able to review the changes easier.
--MSSQLTips.com
/******* This script takes about 2 minutes to run *******/
--Add data to Sales.Cities
INSERT INTO Sales.Cities
(CityName, StateProvinceID)
VALUES
('Naples','FL'),
('Ann Arbor', 'MI'),
('Charlotte', 'NC')
select getdate() as AfterFirstInsert
WAITFOR DELAY '00:01:00'
INSERT INTO Sales.Cities
(CityName, StateProvinceID)
VALUES
('Toledo','OH'),
('Saginaw', 'MI'),
('New York', 'NY')
select getdate() as AfterSecondInsert
update Sales.Cities
set CityName = 'Ft Myers' where CityID = 1
waitfor delay '00:00:10'
update Sales.Cities
set CityName = 'Not Ann Arbor' where CityID = 2
waitfor delay '00:00:10'
update Sales.Cities
set CityName = 'Ann Arbor' where CityID = 2
waitfor delay '00:00:10'
update Sales.Cities
set CityName = 'Not Ann Arbor' where CityID = 2
waitfor delay '00:00:10'
update Sales.Cities
set CityName = 'Ann Arbor' where CityID = 2
waitfor delay '00:00:10'
update Sales.Cities
set CityName = 'Not Ann Arbor' where CityID = 2
waitfor delay '00:00:10'
update Sales.Cities
set CityName = 'Ann Arbor' where CityID = 2Example Queries
Let’s compare a traditional Select statement of all the records and a Select statement that includes all the history captured.
--MSSQLTips.com
SELECT * FROM Sales.Cities;
--Use the SYSTEM_TIME clause and ALL subclause
SELECT * FROM Sales.Cities
FOR SYSTEM_TIME ALL
ORDER BY CityID, ValidFrom;The results from our server are the green box with Select * and the yellow using the FOR SYSTEM_TIME ALL clause, which shows all of the versions of the data tracked to that point.
If we look at the purple box, we see the times, in UTC format, when the data was valid for CityID =2. Starting at 00:10:23.99900501, we see the city name = ‘Ann Arbor’ before it was changed 10 seconds later to ‘Not Ann Arbor’.
As we walk through all of the updates, we see the ValidFrom and ValidTo columns keeping track of the data until finally, at 00:12:24.1300161, the data is updated for the last time. We can see the corresponding ValidTo record lists 9999-12-31 23:59:59.9999999 as the time. This is because SQL Server uses that timestamp for all records that are still valid.
The next query shows how to capture what was valid at a particular time by using the FOR SYSTEM_TIME AS OF clause.
--MSSSQLTips.com
--traditional Select * statement
SELECT * FROM Sales.Cities order by ValidFrom
--Use the SYSTEM_TIME AS OF clause
SELECT * FROM Sales.Cities
FOR SYSTEM_TIME AS OF '2025-03-01 00:11:24.0171052' --edit with your UTC needed
order by CityID, ValidFrom;
In the results above, we see CityID =1 and the CityName, ‘Ft Myers’, in the green square from the Select * example, and in the yellow square, the results FOR SYSTEM_TIME AS OF the UTC time, ‘2025-03-01 00:11:24.0171052’. In the yellow square, ‘Ft Myers’ was the current record at that moment. Using this simple syntax, we can grab data at particular times of day, such as would be used with Slowly Changing Dimensions.
There are three other clauses available for reviewing data between points of time. Offering a number of choices to filter based on ValidFrom and ValidTo times, these additional clauses are more complex to work with and are covered fully in this tip: SQL Server 2016 T-SQL Syntax to Query Temporal Tables.
Retention Policy
The default retention policy is infinite, but can be adjusted to either Days, Weeks, Months, or Years as documented by Microsoft: Manage retention of historical data in system-versioned temporal tables.
Additionally, DBAs can set their own process to delete old data using traditional methods, such as a scheduled SQL Agent job. Partitioning can also be used with temporal tables.
To adjust the retention period for an existing table to a period of time, the table must have a clustered index.
--MSSQLTips.com
--Set the retention period to 9 months
ALTER TABLE Sales.Cities
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));When the cleanup process runs, it does not age out old rows with a traditional delete statement. When reviewing old records, the developer needs to understand that the aging-out process can still allow retrieving records that have not been removed from the system yet, but are ready to be aged out. A full explanation of the cleanup process is available via the link previously mentioned in this section.
Dropping Tables
A temporal table cannot be dropped with system versioning enabled. We must disable system-versioning, then we can drop the table as well as the history table. Dropping the table is not an option in SSMS until system versioning is disabled.
--MSSQLTips.com
--disable system versioning
ALTER TABLE [Sales].[Cities] SET ( SYSTEM_VERSIONING = OFF);With system versioning disabled, SSMS now shows two traditional tables.
The tables can now be dropped in SSMS or with T-SQL.
--MSSQLTips.com
drop table Sales.Cities;
drop table Sales.Cities_Archive;Backup and Recovery of Temporal Tables
There are no special requirements to use SQL Server backups that include system-versioned tables. Backup and recovery of the database and the system-versioned tables are fully supported.
Key Points
- System versioned tables have been available since SQL Server 2016 and are also available in Azure SQL.
- Retention period is set to infinite by default, but can be adjusted after table creation.
- These tables are automatically picked up in Availability groups.
- History tables can be queried directly using standard T-SQL, but cannot be updated directly.
Next Steps
- Run the script provided by Microsoft to determine what tables have system-versioning enabled.
- Determine if the retention period and possible growth of the tables are appropriate.
- Review backup and recovery requirements to ensure your recovery methodology recovers the correct amount of system-versioned data.
- Additional Links
- Querying temporal tables on Microsoft.com
- The differences between CDC and Temporal Tables: SQL Temporal Tables vs Change Data Capture vs Change Tracking – part 3.
- How to query with T-SQL: SQL Server 2016 T-SQL Syntax to Query Temporal Tables.
