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

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

T-SQL Tuesday

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:

Viewing a temporal table

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. As of example
FROM [start time]
TO [end time]
Returns data that is greater than the start time and less than the end time. From To example
BETWEEN [start time]
AND [end time]
Returns data that is greater than the start time and less than or equal to the end time. Between example
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. Contained in 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.

 

Try it out!

That is my quick introduction into creating and querying with system-versioned temporal tables. Go play around a bit and have fun!

Comments ( 0 )

    Leave A Comment

    Your email address will not be published.