SQL Temporal - by Cylon2005

Status :

Description

Have you ever had to create databases using Temporal Database design Patterns using any current day Relational Database?
Doesn't SQL Server now have the horse power to handle advanced database designs?
How about adding Temporal capability to SQL Server to either the Engine or as an Add On the way Analysis Services, Reporting Services, etc.
The classic example is to have a contact database that is able to track the changes to last names for married women. You have a contact table, but you also need a table to track the last names of the women and it's effective date. Can't this be transparent or utilize SQL 3's temporal standards?

Assign To

Is there any update on planned support for this? I know this would be a major boon for us.

Posted by KeithWHare on 11/18/2011 at 5:19 AM

The final (FDIS) ballot on the five parts of ISO 9075 SQL:2011 closed on Wednesday, November 16, 2011. I have not yet seen the ballot results but my expectation is that it will pass with no comments to be resolved. SQL/Foundation:2011 includes support for System Versioned Tables and Application Time Period Tables, which are approximately equivalent to Transaction Time and Valid Time.

The temporal support in SQL:2011 is designed so that existing tables could be migrated to take advantage of temporal capabilities when they are added to the underlying SQL implementations.

Keith HareConvenor, ISO/IEC JTC1 SC32 WG3 Database Languages

Posted by TemporalCraig on 11/15/2011 at 7:02 AM

It is my understanding that temporal extensions have been approved for the next version of ANSI/ISO SQL (but not yet published) and that these extensions provide support for both “valid time” and “transaction time” (or system time and business time).

I think this support for “Bitemporal Data” (bi-temporal data) would be a very powerful capability for many companies which would otherwise be very costly for them to develop and maintain themselves. In addition integration of this functionality into the SQL Server DBMS engine would provide performance benefits that would not be attainable through custom code.

Posted by Microsoft on 5/7/2010 at 10:30 AM

Hello Dave,

Thank you for the suggestion on temporal extensions! We will consider this in future releases.