Querying SQL Server Temporal Table MetaData
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.
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'
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.
- You can read other MSSQLTips tips on temporal tables.
About the author
View all my tips