THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Temporal Tables – Part 1 - Simple Single Table Example

In my first entry in my series of posts on DB Design Book Topics, I want to start with a pretty interesting topic, Temporal Tables. It is a very cool new feature that takes something that used to be very painful (capturing history of changes to a table, allowing the user to see the data at a point in time), and makes it very easy.

So if you have a row in a table, and it is created, updated, and then deleted, knowing how the row looked at a given period of time can be very useful. I wanted to start with a very basic example, to show how thing work, and later entries in this series will expand to multiple rows and tables.

select @@version --Features are apt to change. Test if you are using a later CTP

---------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2016 (CTP3.3) - 13.0.1000.281 (X64)   Jan 28 2016 15:11:40   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

First off, we need to create a workspace. I will just call the database testTemporal:

create database testTemporal
go
use testTemporal
go

Nothing needed to be done to allow temporal, just create a database on the 2016 instance. The table needs to have a few new things, highlighted in the next example:

create table dbo.company
(
    companyId    int identity(1,1) primary key,
    name        varchar(30) unique,
    companyNumber char(5) unique,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL, --the time when this row becomes in effect
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,   --the time when this row becomes no longer in effect
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)  
)
WITH (SYSTEM_VERSIONING = ON); --Note that you can use a table of your own. More on that in a later blog entry…
go

Simple enough, and if you want to see more about the create table syntax, check BOL here (https://msdn.microsoft.com/en-us/library/ms174979.aspx) as this is a CTP edition and subject to change.

So let’s create a row in the table:

insert into dbo.company (name, companyNumber)
values ('Company1','00001')

select SCOPE_IDENTITY(); --If you don't mess up, this will be 1. We will use this in our examples
go

Now we change something in the table a few times to let us have a few changes to see in the example:

update company
set    name = 'Company Name 1'
where  companyId = 1

And update it again:

update company
set    name = 'Company Name 2'
where  companyId = 1

This time update with no changes:

update company
set    name = 'Company Name 2'
where  companyId = 1

To see the row exactly as it currently exists, just use a normal select statement:

select *
from   company
where  companyId = 1

You will see that looks exactly as you expect:

companyId   name                           companyNumber SysStartTime                SysEndTime
----------- ------------------------------ ------------- --------------------------- ---------------------------
1           Company Name 2                 00001         2015-06-05 02:15:32.7938411 9999-12-31 23:59:59.9999999

To see all versions of the rows, use the FOR SYSTEM_TIME clause with CONTAINED IN (read more here about temporal and FOR SYSTEM_TIME): https://msdn.microsoft.com/en-us/library/dn935015(v=sql.130).aspx:

select *
from   company  FOR SYSTEM_TIME CONTAINED IN ('1900-01-01','9999-12-31 23:59:59.9999999')
order by SysEndTime Desc

This returns all of the row versions that have been created:

companyId   name                           companyNumber SysStartTime                SysEndTime
----------- ------------------------------ ------------- --------------------------- ---------------------------
1           Company Name 2                 00001         2015-06-30 00:17:23.9396846 9999-12-31 23:59:59.9999999
1           Company Name 2                 00001         2015-06-30 00:16:48.2444199 2015-06-30 00:17:23.9396846
1           Company Name 1                 00001         2015-06-30 00:16:19.1614451 2015-06-30 00:16:48.2444199
1           Company1                       00001         2015-06-30 00:15:01.6419789 2015-06-30 00:16:19.1614451

There are a few things of note. The first SysStartTime value will be when the row is inserted. The last row will be to 9999-12-31 23:59:59.9999999. Note too that when we updated the row with no actual data changes, we still get a new version.

Also when working with the times and the FOR SYSTEM_TIME clause, be careful to include the time up to the fractional seconds or you may not get what you expect. When using CONTAINED IN, if you don’t put the nines out to all seven decimal places, you won't get the current row due to roundoff:

select *
from   company  FOR SYSTEM_TIME CONTAINED IN ('1900-01-01','9999-12-31 23:59:59.999999') –Only six decimal places
order by SysEndTime Desc

companyId   name                           companyNumber SysStartTime                SysEndTime
----------- ------------------------------ ------------- --------------------------- ---------------------------
1           Company Name 2                 00001         2015-06-30 00:16:48.2444199 2015-06-30 00:17:23.9396846
1           Company Name 1                 00001         2015-06-30 00:16:19.1614451 2015-06-30 00:16:48.2444199
1           Company1                       00001         2015-06-30 00:15:01.6419789 2015-06-30 00:16:19.1614451


The more interesting use will be to work with a row (or rows) at a certain point in time, like to get the second version from 2015-06-30 00:16:19.1614451, we will use FOR SYSTEM_TIME AS OF, which takes a datetime2 value, and returns the row where SysStartTime >= PassedValue > SysEndTime. (The PassedValue can also be a variable.)

select priorCompany.*
from   company FOR SYSTEM_TIME AS OF '2015-06-30 00:16:19.1614451' as priorCompany

Note that the time was from the SysStartTime, which also was the SysEndTime for a different row. This returns:

companyId   name                           companyNumber SysStartTime                SysEndTime
----------- ------------------------------ ------------- --------------------------- ---------------------------
1           Company Name 1                 00001         2015-06-30 00:16:19.1614451 2015-06-30 00:16:48.2444199

You can also use FOR SYSTEM_TIME in a JOIN criteria and see multiple versions of the row in your query:

select company.Name, priorCompany.Name as PriorName
from   company
         join company FOR SYSTEM_TIME AS OF '2015-06-30 00:16:19.1614451' as priorCompany
            on company.companyId = priorCompany.companyId

This will return:

Name                           PriorName
------------------------------ ------------------------------
Company Name 2                 Company Name 1

I expect that may be a pattern that gets used in a later blog to calculate changes since a time period! Finally, lets take a quick look at what happens in a delete:
       
delete from company
where  companyId = 1

Now, checking the data, we see that there is no 9999 row version:

select *
from   company  FOR SYSTEM_TIME CONTAINED IN ('1900-01-01','9999-12-31 23:59:59.9999999')

--There are the same four rows, but now all rows have an end time in the same millienium as we live in:

companyId   name                           companyNumber SysStartTime                SysEndTime
----------- ------------------------------ ------------- --------------------------- ---------------------------
1           Company1                       00001         2015-06-30 00:15:01.6419789 2015-06-30 00:16:19.1614451
1           Company Name 1                 00001         2015-06-30 00:16:19.1614451 2015-06-30 00:16:48.2444199
1           Company Name 2                 00001         2015-06-30 00:16:48.2444199 2015-06-30 00:17:23.9396846
1           Company Name 2                 00001         2015-06-30 00:17:23.9396846 2015-06-30 00:33:39.0978045

So looking for a row at a past time, the row did still exist:

select priorCompany.*
from   company FOR SYSTEM_TIME AS OF '2015-06-30 00:16:19.1614451' as priorCompany

With the only difference being that the SysEndTime isn’t what is once was:

companyId   name                           companyNumber SysStartTime                SysEndTime
----------- ------------------------------ ------------- --------------------------- ---------------------------
1           Company Name 1                 00001         2015-06-30 00:16:19.1614451 2015-06-30 00:16:48.2444199

But looking at the table currently, no row:

select *
from   company

companyId   name                           companyNumber SysStartTime                SysEndTime
----------- ------------------------------ ------------- --------------------------- ---------------------------

So finally, what happens when we replace the row using the same surrogate key value? (Not discussing here if this is a good idea, or bad idea…And this has led me to wonder if we can adjust history if the delete was accidental… Ah, fodder for later)

set identity_insert dbo.company on
go
insert into dbo.company (companyId, name, companyNumber)
values (1, 'Company1','00001')
go
set identity_insert dbo.company off
go

And then look at all of the row versions that exist now?

select *
from   company  FOR SYSTEM_TIME CONTAINED IN ('1900-01-01','9999-12-31 23:59:59.9999999')
order  by SysStartTime desc

--You can see that the row now exists, but there is now a gap between the top two rows:

companyId   name                           companyNumber SysStartTime                SysEndTime
----------- ------------------------------ ------------- --------------------------- ---------------------------
1           Company1                       00001         2015-06-30 00:37:07.1375063 9999-12-31 23:59:59.9999999
1           Company Name 2                 00001         2015-06-30 00:17:23.9396846 2015-06-30 00:33:39.0978045
1           Company Name 2                 00001         2015-06-30 00:16:48.2444199 2015-06-30 00:17:23.9396846
1           Company Name 1                 00001         2015-06-30 00:16:19.1614451 2015-06-30 00:16:48.2444199
1           Company1                       00001         2015-06-30 00:15:01.6419789 2015-06-30 00:16:19.1614451

Looking at the data at the current row’s SysStartTime:

select priorCompany.*
from   company FOR SYSTEM_TIME AS OF '2015-06-30 00:37:07.1375063' as priorCompany

companyId   name                           companyNumber SysStartTime                SysEndTime
----------- ------------------------------ ------------- --------------------------- ---------------------------
1           Company1                       00001         2015-06-30 00:37:07.1375063 9999-12-31 23:59:59.9999999

But a very very small amount of time before, not there:

select priorCompany.*
from   company FOR SYSTEM_TIME AS OF '2015-06-30 00:37:07.1375062' as priorCompany

companyId   name                           companyNumber SysStartTime                SysEndTime
----------- ------------------------------ ------------- --------------------------- ---------------------------

So there is a quick look at some of the basic functionality that we can expect with temporal data in 2016.

Note: I won’t be doing any guessing in these blogs because if I guess right and NDA information is out there that I missed, I could get in trouble accidentally. But I will try to make sure if there are big changes in syntax that I note it, or redo the blog syntax so I don’t end up with bad information out there.

Continue to Part 2 – Changing History

Published Monday, June 29, 2015 9:26 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Marcel said:

How does one maintain the history with error rows etc, or when we want to fake up some data?

June 29, 2015 11:58 PM
 

s.m. said:

thank u

June 30, 2015 2:39 AM
 

drsql said:

Marcel: Not totally sure what you mean about error rows (constraint/trigger/rollbacks? they would go away... Which I will show in a later test I am sure), or do you mean rows at a time? At any given slice of time, all constraints and such that were in effect should show up as in effect.

Faking up some data I am not 100% sure. You can specify your own table, but I haven't tried to see what you can do with that yet. Definitely on my list.  Thanks!

s.m Thanks!

June 30, 2015 10:56 AM
 

Peter Schott said:

Definitely interesting. I'll have to play with this to see what sort of overhead we incur by using this (where needed, of course). I'm also curious about the effects of trying to ALTER a table to add a column, drop a column, etc. It would mostly be for some sort of "ADD Column" statement, though we've done drops or even renames from time to time. If this can work even through those, that's pretty powerful.

July 2, 2015 3:36 PM
 

Nigel said:

Sounds cool - thanks for posting.

Slight typo in "FOR SYSTEM_TIME AS OF, which takes a datetime2 value, and returns the row where SysStartTime >= PassedValue > SysEndTime" though. Think you meant "SysStartTime <= PassedValue < SysEndTime"...

October 12, 2015 9:12 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Archives

Privacy Statement