SQL 2016 – Temporal Tables the way we are most likely to end up using it

In this post I am quickly going show how to convert the existing tables into temporal tables. This is a common requirement I expect we will all encounter when we migrate to SQL 2016 at some point in the future. In this example a take an existing OLTP table and create a history table for it. Into this history table I simulate a behavior of updates happening on the oltp table which in turn inserts records into the history table.

Once I have records on both the oltp and the history table I will convert the tables into the temporal table scenario discussed above.

First I create a database

-- creating a database for this example
create database temporaldb
GO
use temporaldb
GO

Then I create the tables – you will notice that the datetime2 has a precision of (0) this will become important later.


-- the current table is the temporal table
-- the old setup is listed below , i haven't included the trigger logic since that isnt the focus of the post.
create table visits
( id int Primary key clustered ,
first_name varchar(50),
last_name varchar(50),
email varchar(50),
gender Char(1),
ip_address varchar(50)
)
GO
-- creating a pre-exisiting archive table
Create table visits_history
( id int NOT NULL ,
first_name varchar(50),
last_name varchar(50),
email varchar(50),
gender char(1),
ip_address varchar(50),
starttime datetime2(0) NOT NULL DEFAULT GETUTCDATE(),
endtime datetime2(0) NOT NULL DEFAULT GETUTCDATE()
)
GO
Then Insert some data into the tables and verify if everything is fine so far.


-- Inserting some sample data into the current and historical tables
insert into visits_history (id, first_name, last_name, email, gender, ip_address)
Select 1 , 'Jayanth' , 'Kurup', 'jayanth.kurup@a.com','M','192.168.1.0'

insert into visits (id, first_name, last_name, email, gender, ip_address)
Select 1 , 'Jayanth' , 'Kurup', 'jayanth.kurup@a.com','M','192.168.1.1' union all
Select 2 , 'James' , 'Watson', 'james.watson@a.com','M','192.168.1.2' union all
Select 3 , 'Alex' , 'Bell', 'alex.bell@a.com','M','192.168.1.3'

GO

Then we convert the tables into temporal table as shown below


-- Verifiying the data
select * from visits
select * from visits_history

-- Enabling temporal nehaviour of the current table while reusing the existinh history table
-- datetime2(0) is what is required here , setting to date datetime2 is not going to work
-- and will throw the error
-- ADD PERIOD FOR SYSTEM_TIME failed because table 'temporaldb.dbo.visits' contains records where end of period is not equal to MAX datetime
Alter table visits
Add starttime datetime2(0) generated always as row start CONSTRAINT DF_starttime DEFAULT GETUTCDATE(),
endtime datetime2(0) generated always as row end CONSTRAINT DF_endtime DEFAULT CONVERT(datetime2(0), '9999-12-31 23:59:59'),
PERIOD FOR SYSTEM_TIME (starttime , endtime)
GO
-- Binding the temporal table with the historical table - remeber to remove the trigger (if you have one) before this step.
Alter table visits
SET (System_versioning=ON (HISTORY_TABLE= dbo.visits_history))

Followed by inserting some new data to test that everything is working as expected


-- insert some new data to temporal table
insert into visits (id, first_name, last_name, email, gender, ip_address)
Select 4 , 'Isaac' , 'Newton', 'isaac.newton@a.com','M','192.168.1.4'

-- Verifiying the data - testing impact of insert
select * from visits -- notice the two new columns are not visible becuase we have set the hidden property.
select * from visits_history
-- no change

-- update exisiting data in temporal table
Update visits
set ip_address= '192.168.1.5'
where id = 1
-- Verifiying the data - testing impact of insert
select * from visits -- notice the two new columns are not visible becuase we have set the hidden property.
select * from visits_history
-- new row added to historical table

Querying the temporal data


--querying temporal records
select * from visits
FOR SYSTEM_TIME AS OF '2016-01-07 12:56:30'

select * from visits
FOR SYSTEM_TIME BETWEEN '2016-01-07 12:51:54' and '2016-01-07 14:51:54'

select * from visits
FOR SYSTEM_TIME FROM '2016-01-07 12:51:54' TO '2016-01-07 14:51:54'

select * from visits
FOR SYSTEM_TIME CONTAINED IN ('2016-01-07 12:51:54','2016-01-07 14:51:54')

select * from visits
FOR SYSTEM_TIME ALL

In the next post I explain a possible bug I found in the way temporal tables are implemented.