{"id":82175,"date":"2015-06-29T21:26:00","date_gmt":"2015-06-29T21:26:00","guid":{"rendered":"https:\/\/www.webstaging.red-gate.com\/simple-talk\/?p=73542"},"modified":"2018-12-12T11:43:51","modified_gmt":"2018-12-12T11:43:51","slug":"temporal-tables-part-1-simple-single-table-example","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/temporal-tables-part-1-simple-single-table-example\/","title":{"rendered":"Temporal Tables \u2013 Part 1 – Simple Single Table Example"},"content":{"rendered":"

In my first entry in my series of posts on DB Design Book Topics<\/a>, 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.<\/p>\n

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. <\/p>\n

select @@version –Features are apt to change. Test if you are using a later CTP<\/p>\n

—————————————————————————————————————————
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) <\/p>\n

First off, we need to create a workspace. I will just call the database testTemporal:<\/p>\n

create database testTemporal
go
use testTemporal
go<\/p>\n

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:<\/p>\n

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)  
<\/b>)
WITH (SYSTEM_VERSIONING = ON); –Note that you can use a table of your own. More on that in a later blog entry\u2026
<\/b>go<\/p>\n

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<\/a>) as this is a CTP edition and subject to change.<\/p>\n

So let\u2019s create a row in the table:<\/p>\n

insert into dbo.company (name, companyNumber)
values (‘Company1′,’00001’)<\/p>\n

select SCOPE_IDENTITY(); –If you don’t mess up, this will be 1. We will use this in our examples
go<\/p>\n

Now we change something in the table a few times to let us have a few changes to see in the example:<\/p>\n

update company
set    name = ‘Company Name 1’
where  companyId = 1<\/p>\n

And update it again:<\/p>\n

update company
set    name = ‘Company Name 2’
where  companyId = 1<\/p>\n

This time update with no changes:<\/p>\n

update company
set    name = ‘Company Name 2’
where  companyId = 1<\/p>\n

To see the row exactly as it currently exists, just use a normal select statement:<\/p>\n

select *
from   company
where  companyId = 1<\/p>\n

You will see that looks exactly as you expect:<\/p>\n

companyId   name                           companyNumber SysStartTime                SysEndTime
———– —————————— ————- ————————— —————————
1           Company Name 2                 00001         2015-06-05 02:15:32.7938411 9999-12-31 23:59:59.9999999<\/p>\n

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<\/a>:<\/p>\n

select *
from   company  FOR SYSTEM_TIME CONTAINED IN (‘1900-01-01′,’9999-12-31 23:59:59.9999999’)
order by SysEndTime Desc<\/p>\n

This returns all of the row versions that have been created:<\/p>\n

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<\/p>\n

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.<\/p>\n

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\u2019t put the nines out to all seven decimal places, you won’t get the current row due to roundoff:<\/p>\n

select *
from   company  FOR SYSTEM_TIME CONTAINED IN (‘1900-01-01′,’9999-12-31 23:59:59.999999’) \u2013Only six decimal places
order by SysEndTime Desc<\/p>\n

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<\/p>\n

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.)<\/p>\n

select priorCompany.*
from   company FOR SYSTEM_TIME AS OF ‘2015-06-30 00:16:19.1614451’ as priorCompany<\/p>\n

Note that the time was from the SysStartTime, which also was the SysEndTime for a different row. This returns:<\/p>\n

companyId   name                           companyNumber SysStartTime                SysEndTime
———– —————————— ————- ————————— —————————
1           Company Name 1                 00001         2015-06-30 00:16:19.1614451 2015-06-30 00:16:48.2444199<\/p>\n

You can also use FOR SYSTEM_TIME in a JOIN criteria and see multiple versions of the row in your query:<\/p>\n

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<\/p>\n

This will return:<\/p>\n

Name                           PriorName
—————————— ——————————
Company Name 2                 Company Name 1<\/p>\n

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<\/p>\n

Now, checking the data, we see that there is no 9999 row version:<\/p>\n

select *
from   company  FOR SYSTEM_TIME CONTAINED IN (‘1900-01-01′,’9999-12-31 23:59:59.9999999’)<\/p>\n

–There are the same four rows, but now all rows have an end time in the same millienium as we live in:<\/p>\n

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<\/p>\n

So looking for a row at a past time, the row did still exist:<\/p>\n

select priorCompany.*
from   company FOR SYSTEM_TIME AS OF ‘2015-06-30 00:16:19.1614451’ as priorCompany<\/p>\n

With the only difference being that the SysEndTime isn\u2019t what is once was:<\/p>\n

companyId   name                           companyNumber SysStartTime                SysEndTime
———– —————————— ————- ————————— —————————
1           Company Name 1                 00001         2015-06-30 00:16:19.1614451 2015-06-30 00:16:48.2444199<\/p>\n

But looking at the table currently, no row:<\/p>\n

select *
from   company <\/p>\n

companyId   name                           companyNumber SysStartTime                SysEndTime
———– —————————— ————- ————————— —————————<\/p>\n

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\u2026And this has led me to wonder if we can adjust history if the delete was accidental\u2026 Ah, fodder for later)<\/p>\n

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<\/p>\n

And then look at all of the row versions that exist now?<\/p>\n

select *
from   company  FOR SYSTEM_TIME CONTAINED IN (‘1900-01-01′,’9999-12-31 23:59:59.9999999’)
order  by SysStartTime desc<\/p>\n

–You can see that the row now exists, but there is now a gap between the top two rows:<\/p>\n

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<\/p>\n

Looking at the data at the current row\u2019s SysStartTime:<\/p>\n

select priorCompany.*
from   company FOR SYSTEM_TIME AS OF ‘2015-06-30 00:37:07.1375063’ as priorCompany<\/p>\n

companyId   name                           companyNumber SysStartTime                SysEndTime
———– —————————— ————- ————————— —————————
1           Company1                       00001         2015-06-30 00:37:07.1375063 9999-12-31 23:59:59.9999999<\/p>\n

But a very very small amount of time before, not there:<\/p>\n

select priorCompany.*
from   company FOR SYSTEM_TIME AS OF ‘2015-06-30 00:37:07.1375062’ as priorCompany<\/p>\n

companyId   name                           companyNumber SysStartTime                SysEndTime
———– —————————— ————- ————————— —————————<\/p>\n

So there is a quick look at some of the basic functionality that we can expect with temporal data in 2016. <\/p>\n

Note: I won\u2019t 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\u2019t end up with bad information out there.<\/p>\n

Continue to Part 2 \u2013 Changing History<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

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……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82175","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82175","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82175"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82175\/revisions"}],"predecessor-version":[{"id":82224,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82175\/revisions\/82224"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82175"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82175"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82175"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82175"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}