T-SQL Tuesday #079 : It’s 2016!
This post is going to be short and sweet because, to be honest, I haven't played that much with SQL Server 2016. There is, however, one feature that I wanted to look into - system-versioned temporal tables. I just realized though, that our friend and colleague, Rob Farley (b/t), just wrote a T-SQL Tuesday article about gotchas in Temporal Tables. So, if you aren't familiar with them, read my post first, and then go to Rob's post for some further information.
Many times, when things change, we don't necessarily capture or document those changes the way we probably should. Sometimes data accidentally gets changed - a where clause wasn't used or something insane like that. I've never, ever, ever done that personally, but I've heard tell of such things... System-versioned temporal tables allow us to view data at a particular point in time regardless of how often it has changed since then.
Each temporal table is actually two table: One that changes with inserts/updates/deletes, and one history table that holds all versions of historical rows. Both the current table and the history table have columns representing the start and end times when that row in the table was current. In the current table, the end time will always be
'9999-12-31 23:59:59.9999999', because that data is current.
Creating temporal/system versioned tables
Creating a temporal table is exactly like creating a regular table, except you'll add two tables and turn on system versioning like so:
CREATE TABLE dbo.Beers ( BeerID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, Brewery nvarchar(50), City nvarchar(50), [State] nvarchar(5), BeerName nvarchar(50), [Abv%] decimal(4,1), Ibu nvarchar(10), Srm nchar(10), [Type] nvarchar(150), Style varchar(100), /* temporal information below */ SysStartTime datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL, SysEndTime datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL, PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime) ) WITH (SYSTEM_VERSIONING = ON);
The column names for the start and end times can be different, but the data types and generation must be the same. This will create a history table with a name like
'MSSQL_TemporalHistoryFor_...'. Here is what your temporal table will look like in SQL Server Management Studio:
If you want to specify an existing table instead of accepting the default, you can create a history table first, and point the temporal table to that using similar syntax to the above:
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history_schema.history_table) );
Additionally, you can convert existing tables to temporal tables by running the following code:
ALTER TABLE dbo.TableName ADD SysStartTime datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT SYSUTCDATETIME(), SysEndTime datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CONVERT(datetime2(7), '9999-12-31T23:59:59.9999999'), PERIOD FOR SYSTEM TIME (SysStartTime, SysEndTime); GO ALTER TABLE dbo.TableName SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history_schema.history_table));
If adding these start and end times could potentially break an application (perhaps it's using
SELECT *), they can be created as hidden, meaning that they won't show up in query responses unless they're specified.
Querying from System-Versioned Temporal tables
Querying these tables for the current data is exactly like querying any other table. If you want to see the data at a certain point in time, though, there is some additional syntax you'll need to add. This will be after the
FROM clause, and will start with
FOR SYSTEM_TIME. There are four options to use for querying data at a point in time or during a range (click on any image to enlarge the example):
Koen Verbeeck (t) wrote about these expressions in more detail over at MSSQLTips.com. The most important thing I took from this:
BETWEEN doesn't behave exactly like it does in other T-SQL constructs, so be careful there.
That is my quick introduction into creating and querying with system-versioned temporal tables. Go play around a bit and have fun!