One of the new interesting features in SQL Server 2016 is the system-versioning with a catchy name, Temporal Tables. The name of the feature offers a hint to its purpose, to collect and to keep historical information about the data changes inside the database over a period of time. Instead of having just the latest data, we now have a way to know what it has been in any previous point in time. The most obvious use case for Temporal Tables is auditing, or at least it is to me, but they can be used for other purposes as well. Some examples are going back in time in case you need to perform a data recovery from an error or a data change operation gone bad, or you might want to use it for reporting purposes.
There are few things about the Temporal Tables that are good to know. First of all the work needed to keep track of the changes is done by the database engine when the system-versioning feature is enabled. Another thing some of you might appreciate is that the feature is available also in Standard Edition. And finally my favorite, it’s actually rather easy to start using the system-versioning as we will discover shortly.
System-versioning requirements
There are few requirements for using system-versioned temporal tables, not many, but still something you need to reconsider when thinking about using Temporal Tables.
- A table that is going to be system-versioned must have a primary key
- You need to add two datetime2 columns to this table
- FILESTREAM and FILETABLE are not supported
- History table must be created inside the same database as the system-versioned table
- No support for Temporal querying over Linked Server
- TRUNCATE and DROP TABLE commands will not work when system-versioning is enabled
There are also few additional requirements which can be found from the MSDN article here.
Making existing table a system-versioned one
If you already have a table and want to add system-versioning into it, the simplest approach is to ALTER TABLE to create a new schema, add two columns and SET SYSTEM_VERSIONING to ON. In the example below I have a table called SomeItems to which I create a schema called ItemHistory and add two columns, SysStartTime and SysEndTime (I prefer descriptive naming, but you can call them what you want) that are sometimes referred to as period columns to support system-versioning. These columns are used for tracking the period where data changes have occurred . Bit later on we’ll look at how these data changes can be queried using the new FOR SYSTEM_TIME clause.
USE ItemDatabase
GO
CREATE SCHEMA ItemHistory;
GO
ALTER TABLE SomeItems
ADD
SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_StartTime DEFAULT SYSUTCDATETIME(),
SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_EndTime DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO
ALTER TABLE SomeItems
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ItemHistory.SomeItems));
GO
Let’s see what has changed, on left is the original database and on the right side the one with system-versioning turned on.
The noticeable changes here are that in SQL Server Management Studio the table is now marked to be (System-Versioned) and there’s the new schema, ItemHistory, we created. If we also look at the contents, we’ll also see some differences there. Even if you have legacy applications that get all upset and start spitting out errors when you add columns to their tables, you can still use system-versioning if you create the datetime2 columns as HIDDEN. This can be done by minor alteration to the ALTER TABLE query like this:
SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_StartTime DEFAULT SYSUTCDATETIME(), SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_EndTime DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
When you create the columns as hidden their contents will not be visible in queries unless you explicitly include them. So any SELECT * FROM query will not show these columns, but a query that has SELECT SysStartTime, SysEndTime FROM will display them. They’re indeed so well hidden that even the IntelliSense had trouble finding them! I find the ability to hide columns a nice addition to the feature.
Creating a history table
In the example above we simply created a new schema but it is also possible to create an actual table to be used with system-versioning. There are couple reasons why you’d like to do this, for example it’ll give you more options for deciding on the storage, you can create additional indexes on it and you might also like to use data compression on the table. If you’re expecting to generate a lot of historic information it’s definitely nice to have this options available. To create a history table to be used by a table that already has data in it, is rather straightforward. You create the table, add whatever indexes you might need and then proceed with ALTER TABLE commands to enable system-versioning.
USE ItemDatabase
GO
CREATE TABLE ItemHistory
(
ItemID INT NOT NULL,
ItemName nvarchar(100) NOT NULL,
ItemsInStorage INT NOT NULL,
SysStartTime datetime2(0) NOT NULL,
SysEndTime datetime2(0) NOT NULL
);
GO
CREATE NONCLUSTERED INDEX TimeMachine_IX ON ItemHistory (SysStartTime, SysEndTime);
GO
ALTER TABLE SomeItems
ADD
SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START CONSTRAINT DF_StartTime DEFAULT SYSUTCDATETIME(),
SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END CONSTRAINT DF_EndTime DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO
ALTER TABLE SomeItems
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ItemHistory));
GO
Comparing to first example where we just created the schema we can see that there aren’t that many differences. There are however few limitations when using tables for system-versioning and they are.
- The history table you create cannot have a primary key
- No foreign keys, unique indexes, table constraints or triggers
- Cannot be used for Change Data Capture or Change Tracking
- Cannot be used for Transactional or Merge Replication
Not that many limitations and at least from my perspective nothing that’d make me want to skip using this feature when comparing both pros and cons.
Creating a new system-versioned table
Previously we looked at how to add system-versioning to already existing table, but what if you’re in the position of creating a completely new table and you wish to add system-versioning there? The easiest way is to do it with anonymous history table. In this case all you need to do is to create the table with the required datetime2 columns and WITH (SYSTEM_VERSIONING = ON).
CREATE TABLE SomeItems2
(
ItemID INT NOT NULL PRIMARY KEY CLUSTERED,
ItemName nvarchar(100) NOT NULL,
ItemsInStorage INT NOT NULL,
SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_StartTime2 DEFAULT SYSUTCDATETIME(),
SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_EndTime2 DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON);
And that’s it. If we look at the table we see that it’s been created, marked as system-versioned and there’s dbo.MSSQL_TemporalHistoryFor_741577680 (History) named temporal table now available.
While this is very easy way to create a Temporal Table, I personally don’t like automatically generated names inside my databases especially when they can avoided with little effort. So with a minor change to query above we can simply define the name we want to use.
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ItemHistory2))
And that’ll make the outcome so much better! If you want you can of course create a table to hold the history data by following the ALTER TABLE example above.
Going back in time!
Now that we’ve created a system-versioned table, let’s have a quick look on how they work. I did few updates on the SomeItems table so we have some history. If we query the table we see what the current value is.
SELECT * FROM SomeItems
The next thing we do is to query the table where our change history is kept, it’s called ItemHistory.
SELECT * FROM ItemHistory
So we now see the current value on the left and on the right we have all the changes that have happened there since we set the system-versioning on.
Not really exciting, so lets see how we can use some of the system-versioning specific queries. This is done by using the FOR SYSTEM_TIME clause that’s new and only usable for temporal tables. First we can look at what the value was in any specific time using AS OF sub-clause.
SELECT ItemID, ItemName, ItemsInStorage FROM SomeItems FOR SYSTEM_TIME AS OF '2016-09-13 07:40:00'
SELECT ItemID, ItemName, ItemsInStorage FROM SomeItems FOR SYSTEM_TIME AS OF '2016-09-13 07:50:00'
Now we can see what the values were during 7:40 and 7:50 on 2016-09-13. You can also monitor how values have changed over a period of time using BETWEEN sub-clause. Lets have a look at how the value for Blue Items has changed in a week.
SELECT ItemID, ItemName, ItemsInStorage, SysStartTime FROM SomeItems FOR SYSTEM_TIME BETWEEN '2016-09-13 07:50:00' AND '2016-09-20 07:50:00' WHERE ItemID = 1 ORDER BY SysStartTime
Admittedly, I should have probably used some code to generate bit more random values over the time instead of these few manual changes, but hopefully you’ll get the point.
And with that final example I believe it’s a good time to finish with this introduction post to Temporal Tables. While there’s a bit more to this feature (more than I could fit into one post anyway) this should help you to get started with Temporal Tables. One important topic we didn’t touch yet, but you should be aware of, is the maintenance. Over the time you’ll likely have more historical data than you will need and having it will take up storage, so you do need to create a mechanism for cleaning up. That however is a topic for a new post.
Leave a Reply