SQL:2011 or ISO/IEC 9075:2011 (under the general title "Information technology – Database languages – SQL") is the seventh revision of the ISO (1987) and ANSI (1986) standard for the SQL database query language. It was formally adopted in December 2011.[1] The standard consists of 9 parts which are described in detail in SQL. The next version is 2016.
One of the main new features is improved support for temporal databases.[2][3] Language enhancements for temporal data definition and manipulation include:
PERIOD FOR
annotationWITHOUT OVERLAPS
clauseCONTAINS
, OVERLAPS
, EQUALS
, PRECEDES
, SUCCEEDS
, IMMEDIATELY PRECEDES
, and IMMEDIATELY SUCCEEDS
(which are modified versions of Allen’s interval relations)PERIOD FOR SYSTEM_TIME
annotation and WITH SYSTEM VERSIONING
modifier. System time periods are maintained automatically. Constraints for system-versioned tables are not required to be temporal and are only enforced on current rowsAS OF SYSTEM TIME
and VERSIONS BETWEEN SYSTEM TIME ... AND ...
clausesSAP HANA 2.0 SP03 supports system-versioned tables[4] using the standard select syntax FOR SYSTEM_TIME AS OF '<timestamp1>'
[5] SAP HANA 2.0 SP04 adds (partial) support for application-time versioning [6]
IBM DB2 version 10 claims to be the first database to have a conforming implementation of this feature in what they call "Time Travel Queries",[7][8] although they use the alternative syntax FOR SYSTEM_TIME AS OF
.
Oracle Oracle 12c supports temporal functionality in compliance with SQL:2011.[9] Versions 10g and 11g implement the time-sliced queries in what they call Flashback Queries, using the alternative syntax AS OF TIMESTAMP
.[10] Notably both of Oracle's implementations depend on the database transaction log and so only allow temporal queries against recent changes which are still being retained for backup.
Microsoft SQL Server (version 2016) implements temporal tables with SYSTEM_VERSIONING
.[11]
MariaDB 10.3 implements system-versioned tables.[12] MariaDB 10.4.3 added support for application-versioned tables.[13]
PostgreSQL requires installation of the temporal_tables extension. Temporal Tables Extension supports the system-period temporal tables only, but does not follow the SQL:2011 design.
Ebean ORM supports History AS OF and VERSIONS BETWEEN queries on PostgreSQL and MySQL using triggers, history tables and views.
CockroachDB has supported AS OF SYSTEM TIME
queries since at least v1.0.7.[14][15]
Wikibooks has a book on the topic of: Structured Query Language |
Original source: https://en.wikipedia.org/wiki/SQL:2011.
Read more |
Categories: [SQL] [Declarative programming languages] [Query languages]