T-SQL Tuesday #079 : It’s 2016!

This month’s T-SQL Tuesday is hosted by Michael J Swart (b/t), and is on SQL Server 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):

Expression	Description	Example
AS OF [point in time]	Returns the version of the data at the specified point in time.
FROM [start time] TO [end time]	Returns data that is greater than the start time and less than the end time.
BETWEEN [start time] AND [end time]	Returns data that is greater than the start time and less than or equal to the end time.
CONTAINED IN ([start time], [end time])	Returns data that is greater than or equal to the start time and less than or equal to the end time.

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!

Lori Edwards

Lori (@loriedwards) has been with SentryOne since 2013, and has recently transitioned from her role as Senior Solutions Engineer to Training Manager. She is responsible for all learning strategy and architecture, including building, executing, measuring, and evaluating training for SentryOne. Lori is also currently serving as a Director at Large for PASS.

Lori shares tips and tidbits for DBAs picked up over years of working as a DBA, as well as stories about her job here. Whether you are a new DBA who wants to learn or someone interested in learning about what it is like to work for SentryOne, be sure to check out Lori’s blog.