Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Querying SQL Server Temporal Table MetaData


By:   |   Last Updated: 2017-12-21   |   Comments   |   Related Tips: More > 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.

temporal_type temporal_type_desc
0 NON_TEMPORAL_TABLE
1 HISTORY_TABLE
2 SYSTEM_VERSIONED_TEMPORAL_TABLE

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;
   
name object_id temporal_type temporal_type_desc
Customer 901578250 2 SYSTEM_VERSIONED_TEMPORAL_TABLE
CustomerHistory 933578364 1 HISTORY_TABLE
CustomerDetail 949578421 0 NON_TEMPORAL_TABLE

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
   
Use OBJECTPROPERTY() to determine SQL Server Temporal Tables

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
   
Query sys.periods for SQL Server Temporal Tables

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

dbo customer

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'
   
Query sys.columns for Temporal Tables

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


Last Updated: 2017-12-21


get scripts

next tip button



About the author
MSSQLTips author Ameena Lalani 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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools