I am implementing a bitemporal solution for a few of our tables, using the native temporal table features, and some custom columns and code to handle the application/valid time.
However, I just stumbled across a reference to something which is supposedly in the SQL:2011 standard:
From wikipedia:
As of December 2011, ISO/IEC 9075, Database Language SQL:2011 Part 2: SQL/Foundation included clauses in table definitions to define "application-time period tables" (valid time tables), "system-versioned tables" (transaction time tables) and "system-versioned application-time period tables" (bitemporal tables)
This pdf actually has code to do this (application-time):
CREATE TABLE Emp(
ENo INTEGER,
EStart DATE,
EEnd DATE,
EDept INTEGER,
PERIOD FOR EPeriod (EStart, EEnd)
)
This code will not run in SSMS. Has something changed that makes this invalid SQL now? It looks like what used to be undocumented support for application-time/bitemporal tables has now been removed?
Just because it's in the standard doesn't mean it's in any particular implementation. Each vendor has a stretch goal of full standard coverage, but not one of them is there yet, and I doubt it will happen in my lifetime.
Currently SQL Server supports system time, but it does not support application time. There may be another vendor who does; I'm not sure, as I don't follow all the various RDBMS platforms as they mature. I know it's on the SQL Server radar but there have been no formal announcements to date.
The example in the PDF is just that: an example of what could be done by a platform that supports application time. The next example is this...
INSERT INTO Emp
VALUES (22217,
DATE ‘2010-01-01’,
DATE '2011-11-12', 3)
...which also isn't valid in SQL Server for more than one reason, and violates a few best practices to boot. Maybe this stuff is all valid in DB2, as you suggest, but the standard is not supposed to be vendor-specific. I mean, by definition, if nothing else.