Sql-server – Temporal Table that doesn’t use system time

sql serversql-server-2016temporal-tables

We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables.

For the BeginDate, we want it to be dependent on transaction date and not the current getdate time. We are reprocessing a history of transactions. Example below, customer has gym or bank status, and goes from inactive, to active, or pending, dependent on transaction date.

We currently have this.

CREATE TABLE dbo.Department
(
    CustomerId int primary key,
    MembershipStatus int,
    TransactionDate datetime
 );

We we would like to create a table like this.

CREATE TABLE dbo.DepartmentHistory
(
    CustomerId int primary key,
    MembershipStatus int,
    TransactionDate datetime,
    BeginDatetime datetime,
    EndDatettime datetime
 );

Example usage would be:

  1. first Customer Transaction on 3/5/2018 as Pending P
    +------------+--------+-----------+---------+
    | CustomerId | Status | BeginDate | EndDate |
    +------------+--------+-----------+---------+
    | 1          | P      | 3/5/2018  | NULL    |
    +------------+--------+-----------+---------+
  1. second transaction is 4/21/2018 is Active A
    +------------+--------+-----------+-----------+
    | CustomerId | Status | BeginDate | EndDate   |
    +------------+--------+-----------+-----------+
    | 1          | P      | 3/5/2018  | 4/21/2018 |
    | 1          | A      | 4/21/2018 | NULL      |
    +------------+--------+-----------+-----------+

Best Answer

Temporal tables are system-versioned1, so the only way to "manually set" the timestamp of a given history row is to alter the OS time at the moment the row is modified which... you probably don't want to do.

If you want to "manually set" time bounds on a history table so that it supports temporal query syntax, you can do so by manually applying a temporal table on top of your existing data. This is a bit tricky and requires the underlying data to conform to the temporal history versioning rules. Google is peppered with various blog posts on this from back when Temporal Tables were introduced; since I haven't played with that exact use case in a while now I'll arbitrarily link off to this one example that looks promising.

Quickie Demo

Remember that a system-versioned temporal table is actually two tables - a "now" table and a "history" table squished together...

create table dbo.b_now ( 
     i int not null primary key 
    ,info varchar(10)
    ,start_dt datetime2 generated always as row start
    ,end_dt   datetime2 generated always as row end
    ,period for system_time (start_dt, end_dt)
) with (system_versioning = on (history_table = dbo.b_history));
go

insert b_now (i,info)
values 
 (1,'AAA')
,(2,'BBB');
go
update b_now set info = 'XXX' where i = 1
go
select * from b_history
select * from b_now
go

Note that while b_now appears to be the "base table" and supports temporal syntax - it is just a separate object. Removing the system versioning binding between the objects reinforces this.

Using this information to "manually version"

Keeping this in mind - and remembering that we can switch SYSTEM_VERSIONING from ON to OFF and back ON again, we can tell SQL Server to arbitrarily apply (and arbitrarily disregard) system versioning rules to an arbitrary pair of objects so long as...

  1. the schemas match
  2. the data conforms exactly as it would if it had been system versioned

But I want example code!

Yea, I thought you might - so try this...

drop table if exists a_now ,a_history;
go
create table a_now ( 
     i    int not null primary key
    ,info varchar(10)
    ,start_dt datetime2 not null
    ,end_dt   datetime2 not null 
        -- you'll want this CHECK later...
        check (end_dt = convert(datetime2,'9999-12-31 23:59:59.9999999'))
);
go
create table a_history (
     i        int not null
    ,info     varchar(10)
    ,start_dt datetime2 not null
    ,end_dt   datetime2 not null
);
go

declare @end_of_time datetime2 = '9999-12-31 23:59:59.9999999';

insert a_now values 
 (1,'XXX','2017-01-01',@end_of_time)
,(2,'BBB','2017-01-01',@end_of_time)

insert a_history values
 (1,'AAA','2016-01-01','2017-01-01');
go
select * from a_now
select * from a_history

...Looks a lot like b_now and b_history, don't it? Too bad it's not a proper system table...

alter table a_now
    add period for system_time (start_dt, end_dt);
go
alter table a_now
    set (system_versioning = on (history_table = dbo.a_history));
go

Wait wut...? You can even plop a view on top of the "now" table to hide your abuse of the naming conventions.

create or alter view a 
as
select * from a_now
go

select * 
from a 
for system_time as of '2016-06-01'

Okay yea... but how do I add new rows Mr. Smarty Pants?

Very carefully... that's how.

alter table a_now
    set (system_versioning = off);
alter table a_now
    drop period for system_time;
go

declare @end_of_time datetime2 = '9999-12-31 23:59:59.9999999';

insert a_now values 
 (3,'CCC','2018-01-01',@end_of_time);

update a_now set 
     start_dt = '2018-01-01'
    ,info = 'YYYY'
where i = 1;

insert a_history 
values 
  (1,'XXX','2017-01-01','2018-01-01')

alter table a_now
    add period for system_time (start_dt, end_dt);
go
alter table a_now
    set (system_versioning = on (history_table = dbo.a_history));
go

select * from a for system_time as of '2017-06-01'

I leave it to the reader (you) to wrap up the INSERT/UPDATE/DELETE logic and relevant DROP/re-SET system versioning into a re-useable module.

1. N.B. this is hinted at with the syntax the period for SYSTEM_time in the required DDL for creating a temporal table

Related Question