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.

Please Consider Subscribing

Leave a Reply