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.

New features

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

  • Time Period definitions use two standard table columns as the start and end of a named time period, with closed-open semantics. This provides compatibility with existing data models, application code, and tools
  • Definition of application time period tables (elsewhere called valid time tables), using the PERIOD FOR annotation
  • Update and deletion of application time rows with automatic time period splitting
  • Temporal primary keys incorporating application time periods with optional non-overlapping constraints via the WITHOUT OVERLAPS clause
  • Temporal referential integrity constraints for application time tables
  • Application time tables are queried using regular query syntax or using new temporal predicates for time periods including CONTAINS, OVERLAPS, EQUALS, PRECEDES, SUCCEEDS, IMMEDIATELY PRECEDES, and IMMEDIATELY SUCCEEDS (which are modified versions of Allen's interval relations)
  • Definition of system-versioned tables (elsewhere called transaction time tables), using the 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 rows
  • Syntax for time-sliced and sequenced queries on system time tables via the AS OF SYSTEM TIME and VERSIONS BETWEEN SYSTEM TIME ... AND ... clauses
  • Application time and system versioning can be used together to provide bitemporal tables

Support in DBMS

SAP HANA 2.0 SP03 supports system-versioned tables[4] using the standard select syntax FOR SYSTEM_TIME AS OF '<timestamp1>'[5]

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",[6][7] although they use the alternative syntax FOR SYSTEM_TIME AS OF.

Oracle Oracle 12c supports temporal functionality in compliance with SQL:2011.[8] Versions 10g and 11g implement the time-sliced queries in what they call Flashback Queries, using the alternative syntax AS OF TIMESTAMP.[9] 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.[10]

MariaDB 10.3 implements system-versioned tables.[11]

PostgreSQL requires installation of the temporal_tables extension.

See also


  1. ^ Paulley (December 16, 2011), "SQL:2011 is published", IA, Sybase, archived from the 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. ^ https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/20d329a6751910149d5fdbc4800f92ff.html#loio20d329a6751910149d5fdbc4800f92ff__system_versioning_configuration
  5. ^ https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/20fcf24075191014a89e9dc7b8408b26.html
  6. ^ http://www.ibm.com/developerworks/data/library/techarticle/dm-1204whatsnewdb210/index.html
  7. ^ http://www.ibm.com/developerworks/data/library/techarticle/dm-1204db2temporaldata/
  8. ^ http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html
  9. ^ http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm
  10. ^ https://msdn.microsoft.com/en-us/library/dn935015.aspx
  11. ^ https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-103/

External links

  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.



Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.

Manage research, learning and skills at defaultlogic.com. Create an account using LinkedIn to manage and organize your omni-channel knowledge. defaultlogic.com is like a shopping cart for information -- helping you to save, discuss and share.

  Contact Us