SQL Server 2016 Temporal Tables

Tom

SQL Server 2016 will have temporal tables as a new feature: https://msdn.microsoft.com/en-us/library/dn935015.aspx

The temporal tables support one time dimension and are storing the revision history of the records in the table automatically. Behind scenes the temporal tables consist of 2 tables, one containing the current data and a second table which just contains the historic records. For normal queries just the table with the current records is queried. For queries which have to look back in time there are some extension to the classical SQL syntax which allows to formulate queries like ..... FROM myTemporalTable AS OF

During the last week, I've tried to do a bit of work to use temproal tables in the code which is generated from the anchor modelling tool.

I have started to modify the SISULA files for the unitemporal model to generate temporal tables instead of normal tables. For a anchor table this looks like this:

-- Anchor table -------------------------------------------------------------------------------------------------------

-- $anchor.name table (with ${(anchor.attributes ? anchor.attributes.length : 0)}$ attributes)

-----------------------------------------------------------------------------------------------------------------------

IF Object_ID('$anchor.capsule$.$anchor.name', 'U') IS NULL

CREATE TABLE [$anchor.capsule].[$anchor.name] (

$anchor.identityColumnName $anchor.identity $anchor.identityGenerator not null,

$(schema.METADATA)? $anchor.metadataColumnName $schema.metadata.metadataType not null, : $anchor.dummyColumnName bit null,

SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,

SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,

PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime) ,

constraint pk$anchor.name primary key (

$anchor.identityColumnName asc

)

) WITH

(

SYSTEM_VERSIONING = ON (HISTORY_TABLE = [$anchor.capsule].[$anchor.name~*//*~_History])

);

One problem when using temporal tables is the fact that most triggers are not allowed for temporal tables. These means that the INSTEAD OF INSERT triggers on the attribute and tie tables cant be created. I guess the logic of those triggers will have to be put into the triggers on the views to make things work as expected.

So far havent done ony work to solve this problem, since my main goal for now was to create a abstraction layer for the entity framework which is using its own set of stored procs for insert, update and deletes (POC seems to work fine, will soon write a bit about this in a separate post).

Perormance wise the use of temporal tables seems to be a good choice. My unitemporal model which uses the temporal tables for the second time dimension, seems to be quite abit faster than the same model using the CRT approach. Another good thing about temporal table is the packing of the history table by default. This means that queries which receive data from the history tables will be a bit slower, but at the same time the history tables occupy less space. At least in my planned scenario this is entirely acceptable, since revision revision history queries will be required rarely.

Just for curiosity, are there any plans to support SQL Server 2016 temporal tables "officially" in the anchor modeller?

If yes, I'll be happy to share my code.

Roenbaeck

Nice work! I would love to do some testing with your code and if there are benefits with respect to performance I would also want to add it to the tool. It's a shame they don't allow triggers yet, but those really only are icing on the cake :)

Do you have a github account? I can invite you to the project if you'd like?

Tom

Sounds good to me :)

At the moment the things I did are still in a quite hackish state. Will try to clean up the mess a bit and will let you have a copy of it for testing as soon as this is ready.

Even after that cleanup execise there will still be a lot of work to be done. IMHO most important are the replacement of the not allowed triggers and the addition of table valued functions to query the revision history (Right now only table valued functions on the current data are created).

Once these things are done, I'll be happy to push it to github.

Tom

Bump.

Hi Lars

Did you get the mail I sent about 3 weeks ago to l.......r......@anchormodeling.com

Regrds

Tom

Anonymous

Bump ;-)