SQL 2016 - Performance of Temporal Tables

One of the new features in SQL 2016 is Temporal Tables, whilst confusing Google when you search on it (Google seems to want to point you to Temporal Tables occasionally) they are very useful.

Suppose you had a table (called Person):

PersonID	Firstname	Lastname	Notes
1	Test	Person
2	Test	Person
3	Test	Person

Temporal Tables will create another table that stores all of the changes over time and uses slowly changing dimensions so you can track back what it was at a certain point in time.

Slowly changing dimensions are date/time fields that allow you to see when that information was used, normally a From and To date is used.

Considering the table above, the dates would be need to be appended so they would appear in the history table.

PersonID	Firstname	Lastname	Notes	ValidFrom	validTo
1	Test	Person		2016-02-29 15:26:21	2016-02-29 17:19:11
2	Test	Person		2016-02-29 15:26:21	2016-02-29 17:19:11
3	Test	Person		2016-02-29 15:26:21	2016-02-29 17:19:11

The history table is maintained automatically, you update the main table and the history table is dealt with via SQL, nice huh?

So how do I create this?

When creating your original table there are a couple of new additions:

For the ValidFrom and ValidTo columns I've used DATETIME2 it is the bit after it that is interesting:

validFrom DATETIME2(0) *GENERATED ALWAYS AS ROW START HIDDEN* NOT NULL,

GENERATED ALWAYS AS ROW START (and ROW END) allow SQL to keep a track of the time the row was first used and when it was changed.  The StartTime is the time of the transaction itself, so if a query was to update every row in the table it would all have the same time.

HIDDEN - this allows the fields to be added to the main table but they are not returned in a SELECT * query, although adding the columns to the SELECT clause will  return them:

So what is the performance like?

 I wasn't sure either so I thought I'd find out.  Firstly I created a new Azure VM (Standard D2 0 2 cores and 7GB and SSD) with the SQL image CTP3.3 applied.

Firstly I created a new database and then inserted some data into it (1 million rows).  Then I created a loop and did 1 million updates to random rows in the table.
I did the same thing to a non temporal table and compared the results:
Temporal Query:

Non-Temporal Query:

 The results!

	Non Temporal Table	Temporal Table
Insert Data	01:43:54	01:39:46
Update data	01:46:39	01:52:50

This surprised me greatly as I expected the Temporal table updates to be significantly slower (as there was twice the work required), however the results seem to imply that they are almost identical, impressive huh!.