SQL:2011

SQL:2011orISO/IEC 9075:2011(under the general title "Information technology – Database languages – SQL" ) is the seventh revision of theISO(1987) andANSI(1986) standard for theSQL database query language.It was formally adopted in December 2011.^[1]The standard consists of 9 parts which are described in detail inSQL. The next version isSQL:2016.

New features[edit]

One of the main new features is improved support fortemporal databases.^[2]^[3]Language enhancements for temporal data definition and manipulation include:

Time period definitionsuse two standard table columns as the start and end of a named time period, withclosed set-open setsemantics. This provides compatibility with existing data models, application code, and tools
Definition ofapplication time period tables(elsewhere calledvalid timetables), using thePERIODFORannotation
Update and deletion of application time rows withautomatic time period splitting
Temporalprimary keysincorporating application time periods with optional non-overlapping constraints via theWITHOUTOVERLAPSclause
Temporalreferential integrityconstraints for application time tables
Application time tables are queried using regular query syntax or using newtemporal predicatesfor time periods includingCONTAINS,OVERLAPS,EQUALS,PRECEDES,SUCCEEDS,IMMEDIATELYPRECEDES,andIMMEDIATELYSUCCEEDS(which are modified versions ofAllen’s interval relations)
Definition ofsystem-versioned tables(elsewhere calledtransaction timetables), using thePERIODFORSYSTEM_TIMEannotation andWITHSYSTEMVERSIONINGmodifier. System time periods are maintained automatically. Constraints for system-versioned tables are not required to be temporal and are only enforced on current rows
Syntax fortime-slicedandsequencedqueries on system time tables via theASOFSYSTEMTIMEandVERSIONSBETWEENSYSTEMTIME...AND...clauses
Application time and system versioning can be used together to providebitemporaltables

Support in database management systems[edit]

SAP HANA2.0 SP03 supports system-versioned tables^[4]using the standard select syntaxFORSYSTEM_TIMEASOF'<timestamp1>'^[5]SAP HANA2.0 SP04 adds (partial) support for application-time versioning^[6]

IBM DB2version 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 syntaxFORSYSTEM_TIMEASOF.

OracleOracle 12c supports temporal functionality in compliance with SQL:2011.^[9]Versions 10g and 11g implement thetime-slicedqueries in what they callFlashback Queries,using the alternative syntaxASOFTIMESTAMP.^[10]Notably both of Oracle's implementations depend on the databasetransaction logand so only allow temporal queries against recent changes which are still being retained for backup.

Microsoft SQL Server(version 2016) implements temporal tables withSYSTEM_VERSIONING.^[11]

MariaDB10.3 implements system-versioned tables.^[12]MariaDB 10.4.3 added support for application-versioned tables.^[13]

PostgreSQLrequires installation of thetemporal_tables extension.Temporal Tables Extension supports the system-period temporal tables only, but does not follow the SQL:2011 design.

EbeanORM supports History AS OF and VERSIONS BETWEEN queries on PostgreSQL andMySQLusingtriggers, history tables and views.

CockroachDBhas supportedASOFSYSTEMTIMEqueries since at leastv1.0.7.^[14]^[15]

References[edit]

^Paulley (December 16, 2011), "SQL:2011 is published",IA,Sybase, archived fromthe original(blog)on July 11, 2012
^Zemke, Fred. "What's new in SQL:2011".ACM SIGMOD Record 41.1 (2012): 67-73
^Kulkarni, Krishna, and Jan-Eike Michels. "Temporal features in SQL: 2011".ACM SIGMOD Record 41.3 (2012): 34-43
^"SAP Help Portal".
^"SAP Help Portal".
^"SAP Help Portal".
^"Data management".IBM.
^"Data management".IBM.
^http:// oracle /webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html^{[bare URL]}
^"Database SQL Reference".
^"Temporal Tables - SQL Server".16 October 2023.
^"Changes & Improvements in MariaDB 10.3".
^"Application-Time Periods".
^"AS OF SYSTEM TIME | CockroachDB Docs".
^"Time-Travel Queries: SELECT witty_subtitle FROM THE FUTURE".Cockroach Labs.22 June 2016.

External links[edit]

"SQL:2011",Catalogue(webshop),ISO.
Part 1: Framework (SQL/Framework)(draft; PDF),JTC1SC32, 2011-08-06.
Part 14: XML-Related Specifications (SQL/XML)(draft; PDF),JTC1SC32, 2011-08-06.
List of further freely available Final Committee Drafts(search).
Oracle 11g Total Recall Whitepaper(PDF),Oracle, 2009-09-01.

[1] Paulley (December 16, 2011), "SQL:2011 is published",IA,Sybase, archived fromthe original(blog)on July 11, 2012

[2] Zemke, Fred. "What's new in SQL:2011".ACM SIGMOD Record 41.1 (2012): 67-73

[3] Kulkarni, Krishna, and Jan-Eike Michels. "Temporal features in SQL: 2011".ACM SIGMOD Record 41.3 (2012): 34-43

[4] "SAP Help Portal".

[5] "SAP Help Portal".

[6] "SAP Help Portal".

[7] "Data management".IBM.

[8] "Data management".IBM.

[9] ttp:// oracle /webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html^{[bare URL]}

[10] "Database SQL Reference".

[11] "Temporal Tables - SQL Server".16 October 2023.

[12] "Changes & Improvements in MariaDB 10.3".

[13] "Application-Time Periods".

[14] "AS OF SYSTEM TIME | CockroachDB Docs".

[15] "Time-Travel Queries: SELECT witty_subtitle FROM THE FUTURE".Cockroach Labs.22 June 2016.

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]

[12]

[13]

[14]

[15]

v t e SQL
Versions	SEQUEL SQL-86 SQL-89 SQL-92 SQL:1999 SQL:2003 SQL:2006 SQL:2008 SQL:2011 SQL:2016 SQL:2023
Keywords	As Case Create Delete From Group by Having Insert Join Merge Null Order by Over Prepare Select Truncate Union Update Where With
Related	Edgar Codd Relational database
ISO/IEC SQL parts	Framework Foundation Call-Level Interface Persistent Stored Modules Management of External Data Object Language Bindings Information and Definition Schemas SQL Routines and Types for the Java Programming Language XML-Related Specifications

New features[edit]

Support in database management systems[edit]

See also[edit]

References[edit]

External links[edit]