SQL Server 2016: Temporal Tables a first class citizen
Eric Russo | | October 26, 2015

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.
Subscribe to Our Blog
Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.
Popular Posts
ORA-12154: TNS:could not resolve the connect identifier specified
Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.
Oracle EPM Cloud Vs. On-Premises: What’s the Difference?
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.
12c Upgrade Bug with SQL Tuning Advisor
Learn the steps to take on your Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.