SQL Server 2016: Temporal Tables a first class citizen

Working with data inside any RDBMS has been the forte for database administrators. While traditional databases store the data that is considered to be valid at the current time only, temporal tables provide correct information about stored facts at any point in time. For me this is like a snapshot of the data at a particular point. If we have to implement this in a normal database, it will be super difficult and we need to create this as a logging table.

Logging is only one part of the infrastructure, if we need to get to the values at a particular point in time – it will involve tons of date and time datatype manipulation. I am sure you will try to build a number of functions as helper classes.

With the release of SQL Server 2016 CTP2.0, I thought we’d start off with temporal tables. With this release it is important to keep full history of changes in the context of system (transaction) time, i.e. time when data modification became actual in the database. In this case, RDBMS (SQL Server) is fully responsible for keeping immutable history and maintaining timestamps for current and previous record versions.

Let’s create a database and create our Temporal table:

CREATE DATABASE TemporalDB
GO
USE TemporalDB
GO
--Simple temporal table with history table automatically created (name of history table is MSSQL_TemporalHistoryFor_<object_id of table>) :
CREATE TABLE dbo.TemporalExample
(
	ID INT NOT NULL PRIMARY KEY,
	ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
	ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL ,
	Period for system_time (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON)
GO

Once this table gets created, we will find the same in our Object Explorer tree as shown below:

In this example, we have created a temporal table where the history table to track the data is created.

As it shown, for the sake of simplicity as we design temporal table it is implemented using two user tables related to each other. We can identify the related table by querying the DMVs:

SELECT name [Original_Table], OBJECT_NAME(history_table_id) [History_Table_Name] FROM sys.tables WHERE history_table_id IS NOT NULL

If we need to drop the table, it cannot be done till there is a related table. This can be done using the following command.

USE [TemporalDB]
GO
ALTER TABLE [dbo].[TemporalExample] SET ( SYSTEM_VERSIONING = OFF )
GO
DROP TABLE [dbo].[TemporalExample]
GO
-- Add the history table which was created in your databases
DROP TABLE [dbo].[MSSQL_TemporalHistoryFor_933578364]
GO

I’m interested in knowing how many of you are planning to use temporal tables inside your database? What is your use case for using them? Do let me know via your comments.