jump to navigation

Temporal Tables in SQL Server 2016 November 26, 2016

Posted by fofo in Sql Server, SQL Server 2016.
Tags:
trackback

This is the second post in a series of posts about the new features of SQL Server 2016. You can find the first post in the SQL Server 2016 (JSON support in SQL Server 2016) series here .

SQL Server 2016 was officially released back in June 2016.

There are many new features in SQL Server 2016 and in  my opinion the most important are:

  • Always Encrypted
  • Query Store
  • Row Level Security
  • Temporal Tables
  • Stretch Database
  • Dynamic Data Masking
  • PolyBase
  • JSON Support

I am going to discuss the “Temporal Tables” in SQL Server 2016 in this post.

I have installed SQL Server 2016 Enterprise edition in my machine but you can use the SQL Server 2016 Developer edition as well.

You can download the SQL Server 2016 Developer edition by joining the Visual Studio Dev Essentials which is free and simple. After joining you will be able to download SQL Server 2016 developer edition in your machine.

Temporal tables is another brilliant feature that will make work with historical databases in SQL Server much easier.  Temporal is a database feature that was introduced in SQL 2011 and is now supported in SQL Server 2016.

A system-versioned temporal table is a new type of user table in SQL Server 2016, designed to keep a full history of data changes and allow easy point in time analysis.

Please do not confuse temporal tables with temporary tables.

We can use them to look at historical data in an easy way by having the ability to reconstruct the data at any point in time, or even to compare data from two periods without having this history in our current table.

We can use them to understand business trends over time. This is very important for people in the Sales or Marketing Departments of a big business.

Sometimes developers, DBAs can accidentally delete data. Temporal tables can help us recover accidental changes.We can also debug errors and understand data behavior.

Sometimes when we make a mistake and delete or update data by accident the only way to recover data is to restore a valid (recently taken backup).

That takes time and surely temporal tables give us the ability to recover data in a much easier and efficient way.

Another important thing to note is that we can create a new temporal table or alter the schema of an existing table. In order to create a system-versioned table we need to

  1. Have a table that has a primary key (that goes without saying)
  2. The table option SYSTEM_VERSIONING to be set to ON
  3. The two non-nullable DATETIME columns representing the start and the end of the row’s validity period
  4. Designation of the period columns
  5. A linked history table which naturally holds the past states of the modified rows

Also bear in mind that all T-SQL statements INSERT, UPDATE,SELECT are totally valid.

Let’s move on and demonstrate the temporal tables functionality with a hands-on demo.

Type (copy paste) the following query in a new query window and execute it.


CREATE DATABASE TheTemporalDB
GO

USE TheTemporalDB
GO
CREATE TABLE Project 
(
 ProjectID int NOT NULL IDENTITY(1,1) PRIMARY KEY, 
 ProjectName varchar(50) NOT NULL, 
 ManagerID int NULL, 
 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

In the code above, I create a sample database and a temporal table.

If you look at the picture below you will notice the temporal able and the system generated history table that is linked with it.

 

temporal-table-2

You can see the two tables have the exact same columns. The ProjectID column in the history table has not a Primary key constraint. The history table schema reflects the main table schema except for constraints.

Type (copy paste) the following query in a new query window and execute it.


SELECT
 object_id,
 name,
 temporal_type,
 temporal_type_desc,
 history_table_id
 FROM
 sys.tables
 WHERE
 object_id = OBJECT_ID('dbo.Project ', 'U') OR
 object_id = ( 
 SELECT history_table_id 
 FROM sys.tables
 WHERE object_id = OBJECT_ID('dbo.Project ', 'U')
)

GO

Executing the code above you can get information about the main table and the temporal history table.

Have a look at the picture below

 

temporal-table-3

 

If you want to delete those tables you must first turn off system versioning and then drop tables individually.

Type (copy paste) the following query in a new query window and execute it.


ALTER TABLE Project SET (SYSTEM_VERSIONING = OFF)
DROP TABLE Project
DROP TABLE[dbo].[MSSQL_TemporalHistoryFor_565577053] 

Now lets recreate the our temporal table specifying the name of the custom history table.

Type (copy paste) the following query in a new query window and execute it.

USE TheTemporalDB
GO
CREATE TABLE Project
(
 ProjectID int NOT NULL IDENTITY(1,1) PRIMARY KEY, 
 ProjectName varchar(50) NOT NULL, 
 ManagerID int NULL, 
 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 (HISTORY_TABLE = dbo.ProjectHistory))

The name of the history table is now ProjectHistory.

Now let’s create a simple table, populate it and then convert it to a temporal table

Type (copy paste) the following query in a new query window and execute it.


USE TheTemporalDB
GO

ALTER TABLE Project SET (SYSTEM_VERSIONING = OFF)
DROP TABLE Project
DROP TABLE[dbo].[ProjectHistory]

CREATE TABLE Project(
 ProjectId int PRIMARY KEY,
 ProjectName varchar(20) NOT NULL,
 DepartmentName varchar(50) NOT NULL
)
GO

INSERT INTO Project (ProjectId, ProjectName, DepartmentName) VALUES
 (1, 'NopCommerce E-shop', 'E-commerce SubUnit'),
 (2, 'Elastic Search', 'NoSQL DBs SubUnit'),
 (3, 'Adwords Campaign', 'Digital Marketing SubUnit')
GO

I drop the tables I created earlier. I create a simple table and insert some sample values in it.

Now I will convert the table to a temporal table.

Type (copy paste) the following query in a new query window and execute it.


USE TheTemporalDB
GO

ALTER TABLE Project ADD
 StartDate datetime2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT CAST('1900-01-01 00:00:00.0000000' AS datetime2),
 EndDate datetime2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2),
 PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
GO

ALTER TABLE Project 
 SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProjectHistory))
GO

Then I query the two tables. The history table naturally has not data in it.

Then I update (3 times) the first row of the table where ProjectID is 1.

Then I query the two tables. The history table has data in it this time. All changes are stored in the table.

Type (copy paste) the following query in a new query window and execute it.


USE TheTemporalDB
GO

SELECT * FROM [dbo].[Project]
SELECT * FROM [dbo].[ProjectHistory]

UPDATE Project SET ProjectName = 'Custom ASP.Net MVC' WHERE ProjectId = 1


UPDATE Project SET ProjectName = 'ERP Integration' WHERE ProjectId = 1


UPDATE Project SET ProjectName = 'Theme Conversion' WHERE ProjectId = 1


SELECT * FROM [dbo].[Project]
SELECT * FROM [dbo].[ProjectHistory]

Have a look at the picture below.

temporal-table-4

You can see that the history table has all the changes ( all 3 updates) in the ProjectName column.

Finally one should note that when comes to triggers and temporal tables INSTEAD triggers are unsupported while AFTER Triggers are supported only on the current table and not on the history table.

Tables with foreign key constraints that use Cascade updates and deleted are also not supported.

In Memory OLTP tables and FILESTREAM tables cannot become temporal tables.

 

Hope it helps!!!

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: