Specialized IT Services focused on Data Management | Speak with Us 877-634-9222
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
|
1 |
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.
|
1 2 3 4 5 6 7 8 9 |
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.
Contact Us
Senior Vice President of Database Services
Eric Russo is SVP of Database Services overseeing all of Datavail’s database practices including project and managed services for MS SQL, Oracle, Oracle EBS, MySQL, MongoDB, SharePoint and DB2. He is also the Product Owner for Datavail Delta, a database monitoring tool. He has 21 years’ experience in technology including 16 years in database management. His management success and style has attracted top DBAs from around the world to create one of the most talented and largest SQL Server teams. He has been with Datavail since 2008: previous to that his work experiences include DBA Manager at StrataVia, Senior Web Developer at Manifest Information Systems and SQL Server DBA at Clark County, Nevada.
