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.
Load comments