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:
SELECT * FROM Orders 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:
The key syntax is shown below:
-- To the Delorian! SELECT * FROM Orders 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.