I also referred to Temporal Tables which are available to us in the brave new world of SQL 2016.

Temporal tables aren’t just about monitoring change, they also provide really nice methods for being able to query historical data – to see what the values were at a particular point in time e.g.

SELECT * FROM dbo.SomeData FOR SYSTEM_TIME AS OF '1900-01-01';

This makes them easy to work with, but also opens up other applications for them over and above auditing purposes – for instance being able to maintain configuration data that changes over time, but where we need to reference what the value was historically.

I wanted to look in more detail about the process for changing an existing table so that it becomes a temporal one.

Let’s start by quickly looking at how we would define a temporal table for a fresh deployment:

There are a few things here additional to a normal table definition. First thing to note is we need two columns to support the Temporal versioning. I’ve called my columns StartDate and EndDate, these both have to be of DATETIME2 datatype to the highest precision (100 nanoseconds). These are specified as GENERATED ALWAYS AS ROW START\END so SQL will take care of assigning values when new rows are inserted or updated.

I’ve specified the PERIOD FOR SYSTEM TIME, so SQL knows that these are the fields to use for the temporal versioning.

Finally I’ve specified SYSTEM_VERSIONING for the table and what the history table should be called.

When I execute this you can find the table and corresponding history table displayed in SSMS as below:
We have the table itself, and also the history table has been created for us. This is where the previous versions of rows of data will be stored.

So that’s pretty straightforward – but what if you want to change an existing table?

First of all let’s drop this one:

DROP TABLE dbo.SomeData;

Unfortunately that gives me an error message:Msg 13552, Level 16, State 1, Line 90 Drop table operation failed on table ‘TestAudit.dbo.SomeData’ because it is not supported operation on system-versioned temporal tables.

As the message says, you can’t just drop Temporal tables. First you must turn system versioning off, then you can go ahead:

So I can’t do this in an alter statement. What I’ll actually have to do is create a new column specified to work with the system versioning, copy the data over, drop the old column and rename the new one.

First let’s create the additional columns and define the PERIOD FOR SYSTEM_TIME:

Note that I’ve had to specify defaults for the new columns so that the existing data gets values populated (I didn’t have to do that when I simply defined the Temporal Table from scratch).

For the StartDate I’ve specified the current UTC date – this could be any date and time that’s not in the future, though note it should be a UTC one. If I’d tried to use GETDATE, as I’m currently on British Summer Time, I would get the following error:Msg 13542, Level 16, State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on table ‘TestAudit.dbo.SomeData’ failed because there are open records with start of period set to a value in the future.

For the end date I have put the maximum value a DATETIME2 column can hold, this is required and is a way of saying that this record is (at the moment) valid indefinitely. If I try to put in an earlier date I receive this error:Msg 13575, Level 16, State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME failed because table ‘TestAudit.dbo.SomeData’ contains records where end of period is not equal to MAX datetime.

Now I’ve got the columns I could try copying my data over:

UPDATE dbo.SomeData SET StartDate = ModifiedDate;

I get another error:Msg 13537, Level 16, State 1, Line 70 Cannot update GENERATED ALWAYS columns in table ‘TestAudit.dbo.SomeData’.

It turns out you can get around this by temporarily dropping the period for system time:

Your logical thought might be here that I didn’t need to create the period for system time when I altered the table to add the new columns. I could have just added the columns, migrated the data, then only added the period afterwards. Trust me, I tried that too:Msg 13509, Level 16, State 1, Line 51 Cannot create generated always column when SYSTEM_TIME period is not defined.

Here’s what it looks like in SSMS:
You can see this is the same as the original fresh creation of a temporal table. The history table has been created for us. Just with an additional column in both tables.

What if I use SQL Server Data Tools (SSDT) to make my change?

If you’re working with Database Projects in Visual Studio then you will be used to publishing changes straight from there – or deploying them using a DACPAC file.

In that case SSDT handles all this for you – though it does use a slightly different method. Rather than creating a new column, copying that data over, dropping the old column and renaming the new one, it does it at the table level.
That’s the same way some other changes are made with SSDT (or if you do them through the GUI in SSMS). The steps are broadly the same, just with tables instead of columns:

1) Create new temporal table
2) Turn SYSTEM_VERSIONING off
3) Copy across the data from the old table to the new
4) Turn SYSTEM_VERSIONING back on
5) Drop the old table
6) Rename the new table to the original name

If you’re doing this by “hand”, you could do it this way if you prefer, but it is probably a heavier operation that will require more down time. For instance you’ll need the whole table to be locked while the operation progresses as you don’t want to risk data changes once you’ve started copying the data over. The column method will also lock the table, but potentially there is less to update\copy so it should hopefully be quicker.

As a final point I should note that you may run into problems dropping the trigger with SSDT, it can be a little quirky around doing that. For the moment I’ll leave off from going into that in more detail – but if you get that issue feel free to give me a yell!

Interesting feature. I migrated our environment away from trigger-based-auditing to Change Data Capture. The original uses for the trigger-based-audit data were varied but principally wanted to capture changes to data and capture “who-dunnit.” Well, CDC only solves one part of that problem. The other was enabling SQL Server Audits for who-dunnit. It works well, for the most part. And that population is asynchronous from the live workload, that is also a perk. CDC is still a little tedious to manage particularly when modifying an object. Also, if there are problems with the log reader due to replication, CDC, something … it can cause problems that may lead to emergency use of sp_replrestart, etc. This means gaps in data. This temporal table feature can solve many of our needs and seems to be more flexible minus having to add two columns per table to support it. We’ll definitely be keeping an eye for this!

Thank you Matthew for the great article and i was able to resolve a part of my situation. We are loading data based on the extract date in each file and for that reason,

Ii need to set ValidTo(EndDate) to the extract date, i got an idea from your blog to Turn OFF the SYSTEM_VERSIONING and Turrng on after the load, renaming the tables etc, it didn’t well and still struggling to set the EndDate as our ExtractDate

You don’t want to set an end date as that would imply there is a version after the one you are updating/inserting and there isn’t yet. Usually for the row(s) you are inserting/updating the StartDate is the point at which you are adding the row and the end date is the maximum datetime (9999-12-31 23:59:59.9999999). You need to let SQL Server manage these values for you (i.e. you don’t specify what they should be). Then if you update that row the old version gets copied to the history table (with an EndDate which was the point you updated it) and the new version of the row gets the current datetime as its StartDate and the maximum datetime as its EndDate. If you need the extract date for other reasons then just store that as a separate column.