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()
)
GOThen 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'

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'

-- 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 ALL

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

Please Consider Subscribing

Subscribe