Querying SQL Server Temporal Table MetaData

By: Ameena Lalani   |   Updated: 2017-12-21   |   Comments   |   Related: > Temporal Tables

Problem

Temporal tables, introduced in SQL Server 2016 and enhanced in SQL Server 2017, provide an automated capability to track data changes in a table. It also provides easy mechanism to query this historical data. There are quite a few Metadata (data about data) tables, views and functions that were added in the SQL Server engine to integrate and optimize this feature. In this tip we are going to learn about them with code examples.

Solution

There are various system tables and functions that we can use to view temporal table metadata. At the very minimum, a “View Definition” permission is required at the object level to view the object properties.

Query sys.tables for SQL Server Temporal Tables

First let us try to find out all the tables in a database for which temporal feature (system versioning) is turned on. We will get this information from sys.tables where new columns such as “temporal_type” and “temporal_type_desc” are added starting SQL Server 2016.  Following are the 3 types of tables.

In this example we have a database called “TemporalDB”. It has a “Customer” table that has system_versioning = ON and the corresponding history table called “CustomerHistory”. There is also a non-temporal (normal) table called “CustomerDetail” in this database. Querying sys.tables shows us following information about these tables.

USE TemporalDB
GO
SELECT name, object_id,temporal_type,temporal_type_desc FROM sys.tables;
   

Use OBJECTPROPERTY() to determine SQL Server Temporal Tables

Next, we can use OBJECTPROPERTY() function to find out if a table is a history, temporal or normal table. This is a built in SQL Server function. It takes 2 parameters, object id and object property. From our previous knowledge of temporal tables, we know that history table does not have primary key. We can also use this information to identify table type. Here “CustomerHistory” is a history table that is attached to a temporal table.

SELECT  OBJECTPROPERTY(OBJECT_ID('dbo.Customerhistory'),'TabletemporalType') AS Temporal_Type

SELECT  OBJECTPROPERTY(OBJECT_ID('dbo.Customerhistory'),'TableHasPrimaryKey') AS Does_Tbl_has_PK
   

Query sys.periods in SQL Server for System_Time of Temporal Tables

A new way to find temporal tables is through a new system table called sys.periods. This table holds the information about the system_time of all temporal tables in a database. Observe the highlighted code in the below abbreviated temporal table creation script.

CREATE TABLE [dbo].[Customer](
   [CustomerId] [INT] IDENTITY(1,1) NOT NULL,
   [FirstName] [VARCHAR](30) NULL,
   [LastName] [VARCHAR](30) NULL,
   [Amount_purchased] [DECIMAL](18, 0) NULL,
   [StartDate] [DATETIME2](7) GENERATED ALWAYS AS ROW START NOT NULL,
   [EndDate] [DATETIME2](7) GENERATED ALWAYS AS ROW END NOT NULL,
   [Age] [INT] NULL,
PRIMARY KEY CLUSTERED 
(
   [CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
   PERIOD FOR SYSTEM_TIME ([StartDate], [EndDate])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[CustomerHistory] ))
   

When we query sys.periods table, this is the result we see:

select  OBJECT_NAME(object_id) as table_name,* from sys.periods
   

In this example, the StartDate column is the fifth column and EndDate is the sixth column in the Customer temporal table.

Query sys.columns for Temporal Tables

Here is one more system table that will provide column level information about SQL Server Temporal Tables. This table keeps track of all the column properties. The new properties added to this system table are “generated_always_type” and “generated_always_desc”. Please note the types for StartDate and EndDate columns for the Customer table. Here type 1 means the column is designated as start of the time period for which this value is going to be valid. When rows are inserted into a temporal table, they get these system generated time values. Then type 2 means the end of validity period. By default this value is 9999-12-31 23:59:59.9999999. But when a row gets updated or deleted, column of type “AS_Row_End” gets the new system generated time value.

SELECT name AS 'Column_Name', generated_always_type, generated_always_type_desc 
FROM sys.columns WHERE  OBJECT_NAME(object_id) = 'Customer'
   

Summary

Temporal Tables are a great new SQL Server feature. This option keeps track of history and allows you to find trends and anomalies in your data over a period of time. This is a great tool to audit data changes. It is also useful in an application where data changes over a long period of time such as Slowly Changing Dimensions. Another great use case for temporal tables is when an incorrect update or delete statement is issued. You simply retrieve the previous value from the history table and update the record. This tip gives more options and information to SQL Server Data Professionals about retrieving information from Temporal Tables. This will hopefully help them manage Temporal Tables more granularly and to write more efficient code.

Next Steps

• You can read other MSSQLTips tips on temporal tables.

About the author

Ameena Lalani is a MCSA on SQL Server 2016. She is a SQL Server veteran and started her journey with SQL Server 2000. She has implemented numerous High Availability and Disaster Recovery solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips