CODEWITHSUNDEEP
sql-server-2016temporal-databasebi-temporal
Crogacht
Crogacht

Reputation: 90

Application-time period tables

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?

Upvotes: 1

Views: 681

Answers (2)

Chris Golledge
Chris Golledge

Reputation: 392

IBM DB2 supports what you are asking about. Think of the SQL standard as a definition of the recommended way a vendor should expose a feature if they support it, well at least after SQL 92, which is kind of a core. In the history of SQL dialects, sometimes vendors get ahead of the standard and dialects diverge. A vendor would be kind of foolish to implement a feature in a non-standard way after it has been standardized, but sometimes they do. Hot on the left, cold on the right; that is a standard. It works the other way around, but people tend to get burned.

In this case, it looks like IBM decided to implement the feature and make their way of implementing it part of the standard in one fell swoop. Microsoft has not yet decided it is worth their trouble.

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

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.

Upvotes: 2

Related Questions