Disaster Recovery, High Availability, SQL Server, SQL Server 2016

Temporal Tables Coming in SQL Server 2016

A while back I wrote a piece about how Oracle Flashback was the only Oracle feature remaining that I would love for SQL Server. This after many years early in my career where I was frequently hit with occasional bouts of database envy.

You can see the full details of why I hanker for this feature here, https://rule30.wordpress.com/2014/04/23/the-only-oracle-feature-i-actually-still-want/ – but in short its because it lets you do this:

AS OF TIMESTAMP -- Say Wwwwhat!!
TO_TIMESTAMP('2014-04-23 09:30:00', 'YYYY-MM-DD HH:MI:SS')

The great news is that Satya Nadella has clearly been checking out the blog here and “flashback” queries are going to be included in SQL Server 2016 under the name Temporal Tables or System Versioned tables. Full details here:

Temporal Tables in SQL Server 2016

The key syntax is shown below:

-- To the Delorian!
FOR SYSTEM_TIME AS OF '2014-04-23 09:30:00' 

Fantastic! The key use case I have currently is in providing a recovery option for managing VLDBs however there are very obvious applications for this in BI and Auditing considering the feature has been implemented as a simple first class concept in standard T-SQL.

A couple of observations:

  • As of CTP 2 Management Studio tooling doesn’t seem to have any support for enabling temporal/system versioned tables. Opting to create a System Versioned table throws you out to a standard coding window where you’ll have to get your DDL on. I’m guessing the tooling will catch up before release
  • I’m really liking the simplicity of the implementation. Enabling and managing Flashback in Oracle seemed like a bit of an ordeal to me so MS are really playing to their strengths. Once created in SQL Server a temporal table is really just a standard table with a secondary hidden table being maintained for you behind the scenes – its that simple
  • I’m not clear which versions of SQL Server will come with temporal tables. It “feels like” an Enterprise only feature given its high availability/big data applications but I haven’t seen anything on that yet. Even at SQL Server Enterprise pricing though its almost certainly going to be a bargain compared to Oracle.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s