Temporal Tables (SQL 2016 new database feature)

Temporal is a database feature that was included in ANSI SQL 2011.  SQL 2016 now supports this database feature. Previously, you would keep history of what happened to your data either by using triggers on a table that will move the original data to your history table or you would enable CDC and move the data to another table as CDC is time bound. Now, you don’t need to do any of this as SQL Server will be able to do this for you.  

I will walk though some of the syntax and what you will expect when inserting new rows, updating existing data, deleting existing data, adding new columns to the table, dropping old columns.

Creating temporal tables in SQL 2016

Syntax for creating temporal table is a bit different from standard table creation (standard DDL). You need to include two additional columns that will store data change history and how long it will be kept in the system.  Also, at the end you need to include the SYSTEM_VERSIONIG = ON syntax. Giving history table name is optional but if you don't implicitly specify SQL server will create a name for you which you will have no control over. I like to keep control of the naming of my tables hence included in the DDL.

CREATE TABLE dbo.Products
(ProductId int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  ProductName varchar(50) NOT NULL,
  ListPrice Money not null,
  HistFrom  datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
  HistTo datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,
  PERIOD FOR SYSTEM_TIME (HistFrom, HistTo)  ) WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHisotry) );

GO

When you run the above script and refresh your management studio you will see both Products and Product history table. See screenshot below. The history table sits under products table and the productions table shows as system-versioned)

 
Inserting Data

insert into Products(ProductName,ListPrice)

VALUES ('PL1', 100), ('PL2', 10), ('PL3', 20), ('PL3', 30)
go

select * from Products

go

select * from [dbo].[ProductHisotry]

go

As you can see below no history recorded. That makes sense as insert is a new row and the actual data is stored on the main table. 

Updating Data

update Products

set listPrice = 40 where productid = 1
go
select * from [dbo].[ProductHisotry]
As you can see the old value is recorded in history table. 

 

 Delete Data 

delete Products where productid = 1 

go 

select * from [dbo].[ProductHisotry] 

As you see below the data deleted from the main table is now stored in history table.

Add new column 

Now let us see what will happen if we try to run standard DDL to add new column to Product table.
ALTER TABLE [dbo].Products
ADD comment varchar(100)

After running the above DDL statment it is complaining about the standard DDL is not supported by system-versioned temporal table.

 

So, to add a new column or drop an existing column you need to turn off SYSTEM_VERSIONING.
ALTER TABLE [dbo].Products SET (SYSTEM_VERSIONING = OFF)

When you run the above command the history table will move out of the products tree and moves to Table tree as shown below. 

Now we should be able to add column

ALTER TABLE [dbo].Products
add    col1 datetime
NB : column is only added to products table only and not propagated to the corresponding history table

                        

So, to add the same column to history table if you try to run the following statement then you will get error complaining about the number of columns on products table not matching history table.

ALTER TABLE [dbo].Products SET (SYSTEM_VERSIONING =  ON (HISTORY_TABLE = dbo.ProductHisotry) );

So addition of columns should be performed on both the tables

ALTER TABLE [dbo].ProductHisotry
add    col1 datetime
and then running will bring all maintained history and the history table will move back under the products table as shown below

ALTER TABLE [dbo].Products SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHisotry) );

It is much more elegant than using triggers. One more issue addressed by SQL server.