Select Page

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.

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.

Bobby Ellis | April 10, 2018

Hyperion Myth #9: SOX Audit Requests Are Time-consuming

With serious financial penalties, SOX audits can be intimidating — but they don’t have to be. Find out how you can use Datavail’s software to automatically prove SOX compliance.

Jonathan Berry | March 13, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS