SQL:2011: Difference between revisions

From HandWiki
imported>Wincert
linkage
 
add
 
Line 3: Line 3:


==New features==
==New features==
One of the main new features is improved support for [[Temporal database|temporal database]]s.<ref>Zemke, Fred. "[http://www.sigmod.org/publications/sigmod-record/1203/pdfs/10.industry.zemke.pdf What's new in SQL:2011]". ACM SIGMOD Record 41.1 (2012): 67-73</ref><ref>Kulkarni, Krishna, and Jan-Eike Michels. "[http://www.sigmod.org/publications/sigmod-record/1209/pdfs/07.industry.kulkarni.pdf Temporal features in SQL: 2011]". ACM SIGMOD Record 41.3 (2012): 34-43</ref> Language enhancements for temporal data definition and manipulation include:
One of the main new features is improved support for [[Temporal database|temporal database]]s.<ref>Zemke, Fred. "[http://www.sigmod.org/publications/sigmod-record/1203/pdfs/10.industry.zemke.pdf What's new in SQL:2011] {{Webarchive|url=https://web.archive.org/web/20130810193539/http://www.sigmod.org/publications/sigmod-record/1203/pdfs/10.industry.zemke.pdf |date=2013-08-10 }}". ACM SIGMOD Record 41.1 (2012): 67-73</ref><ref>Kulkarni, Krishna, and Jan-Eike Michels. "[http://www.sigmod.org/publications/sigmod-record/1209/pdfs/07.industry.kulkarni.pdf Temporal features in SQL: 2011] {{Webarchive|url=https://web.archive.org/web/20121115034718/http://www.sigmod.org/publications/sigmod-record/1209/pdfs/07.industry.kulkarni.pdf |date=2012-11-15 }}". ACM SIGMOD Record 41.3 (2012): 34-43</ref> 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 set|closed set]]-[[Open set|open set]] semantics.  This provides compatibility with existing data models, application code, and tools
* '''Time period definitions''' use two standard table columns as the start and end of a named time period, with [[Closed set|closed set]]-[[Open set|open set]] semantics.  This provides compatibility with existing data models, application code, and tools
Line 10: Line 10:
* '''Temporal [[Primary key|primary key]]s''' incorporating application time periods with optional non-overlapping constraints via the {{code|WITHOUT OVERLAPS|sql}} clause
* '''Temporal [[Primary key|primary key]]s''' incorporating application time periods with optional non-overlapping constraints via the {{code|WITHOUT OVERLAPS|sql}} clause
* '''Temporal [[Referential integrity|referential integrity]]''' constraints for application time tables
* '''Temporal [[Referential integrity|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 {{code|CONTAINS|sql}}, {{code|OVERLAPS|sql}}, {{code|EQUALS|sql}}, {{code|PRECEDES|sql}}, {{code|SUCCEEDS|sql}}, {{code|IMMEDIATELY PRECEDES|sql}}, and {{code|IMMEDIATELY SUCCEEDS|sql}} (which are modified versions of [[Allen's interval algebra#Relations|Allen’s interval relations]])
* Application time tables are queried using regular query syntax or using new '''temporal predicates''' for time periods including {{code|CONTAINS|sql}}, {{code|OVERLAPS|sql}}, {{code|EQUALS|sql}}, {{code|PRECEDES|sql}}, {{code|SUCCEEDS|sql}}, {{code|IMMEDIATELY PRECEDES|sql}} and {{code|IMMEDIATELY SUCCEEDS|sql}} (which are modified versions of [[Allen's interval algebra#Relations|Allen’s interval relations]])
* Definition of '''system-versioned tables''' (elsewhere called [[Transaction time|transaction time]] tables), using the {{code|PERIOD FOR SYSTEM_TIME|sql}} annotation and {{code|WITH SYSTEM VERSIONING|sql}} 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
* Definition of '''system-versioned tables''' (elsewhere called [[Transaction time|transaction time]] tables), using the {{code|PERIOD FOR SYSTEM_TIME|sql}} annotation and {{code|WITH SYSTEM VERSIONING|sql}} 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 {{code|AS OF SYSTEM TIME|sql}} and {{code|VERSIONS BETWEEN SYSTEM TIME ... AND ...|sql}} clauses
* Syntax for '''time-sliced''' and '''sequenced''' queries on system time tables via the {{code|AS OF SYSTEM TIME|sql}} and {{code|VERSIONS BETWEEN SYSTEM TIME ... AND ...|sql}} clauses
Line 21: Line 21:
[[Software:IBM DB2|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",<ref>{{Cite web|url=http://www.ibm.com/developerworks/data/library/techarticle/dm-1204whatsnewdb210/index.html|title=Data management|website=[[Company:IBM|IBM]]}}</ref><ref>{{Cite web|url=http://www.ibm.com/developerworks/data/library/techarticle/dm-1204db2temporaldata/|title = Data management|website = [[Company:IBM|IBM]]}}</ref> although they use the alternative syntax {{code|FOR SYSTEM_TIME AS OF|sql}}.
[[Software:IBM DB2|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",<ref>{{Cite web|url=http://www.ibm.com/developerworks/data/library/techarticle/dm-1204whatsnewdb210/index.html|title=Data management|website=[[Company:IBM|IBM]]}}</ref><ref>{{Cite web|url=http://www.ibm.com/developerworks/data/library/techarticle/dm-1204db2temporaldata/|title = Data management|website = [[Company:IBM|IBM]]}}</ref> although they use the alternative syntax {{code|FOR SYSTEM_TIME AS OF|sql}}.


[[Software:Oracle Database|Oracle]] Oracle 12c supports temporal functionality in compliance with SQL:2011.<ref>http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html {{Bare URL inline|date=September 2022}}</ref> Versions 10g and 11g implement the '''time-sliced''' queries in what they call '''Flashback Queries''', using the alternative syntax {{code|AS OF TIMESTAMP|sql}}.<ref>{{Cite web|url=http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm|title = Database SQL Reference}}</ref> Notably both of Oracle's implementations depend on the database [[Transaction log|transaction log]] and so only allow temporal queries against recent changes which are still being retained for backup.
[[Software:Oracle Database|Oracle]] 12c supports temporal functionality in compliance with SQL:2011.<ref>{{cite web | url=http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html | title=Implementing Temporal Validity }}</ref>  Versions 9,<ref>{{Cite web|url=https://www.oracle-developer.net/display.php?id=210|title=flashback query in oracle 9i|website=www.oracle-developer.net|access-date=2024-11-01|lang=en}}</ref><ref>{{Cite web|url=https://oracle-base.com/articles/9i/flashback-query|title=Flashback Query|lang=en|website=oracle-base.com|access-date=2024-11-01}}</ref> 10g and 11g implement the '''time-sliced''' queries in what they call '''Flashback Queries''', using the alternative syntax {{code|AS OF TIMESTAMP|sql}}.<ref>{{Cite web|url=http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm|title = Database SQL Reference}}</ref> Notably both of Oracle's implementations depend on the database's rollback segment and so only allow temporal queries against recent changes which are still being retained for backup.


[[Software:Microsoft SQL Server|Microsoft SQL Server]] (version 2016) implements temporal tables with {{code|SYSTEM_VERSIONING|sql}}.<ref>{{Cite web|url=https://msdn.microsoft.com/en-us/library/dn935015.aspx|title=Temporal Tables - SQL Server|date=16 October 2023 }}</ref>
[[Software:Microsoft SQL Server|Microsoft SQL Server]] (version 2016) implements temporal tables with {{code|SYSTEM_VERSIONING|sql}}.<ref>{{Cite web|url=https://msdn.microsoft.com/en-us/library/dn935015.aspx|title=Temporal Tables - SQL Server|date=16 October 2023 }}</ref>
Line 34: Line 34:


==See also==
==See also==
{{Wikibook|Structured Query Language}}
{{Wikibook|Structured Query Language}}
* [[SQL]]
* [[Slowly changing dimension]]
* [[Slowly changing dimension]]
* [[Temporal database]]
* [[Temporal database]]
Line 50: Line 48:
* {{Citation | url = http://www.oracle.com/technetwork/database/focus-areas/storage/total-recall-whitepaper-171749.pdf | title = Oracle 11g Total Recall Whitepaper | publisher = Oracle | date = 2009-09-01}}.
* {{Citation | url = http://www.oracle.com/technetwork/database/focus-areas/storage/total-recall-whitepaper-171749.pdf | title = Oracle 11g Total Recall Whitepaper | publisher = Oracle | date = 2009-09-01}}.


{{SQL}}


[[Category:SQL|* ]]
[[Category:SQL|* ]]

Latest revision as of 09:51, 24 May 2026

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.

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 set-open set 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 database management systems

SAP 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 12c supports temporal functionality in compliance with SQL:2011.[9] Versions 9,[10][11] 10g and 11g implement the time-sliced queries in what they call Flashback Queries, using the alternative syntax AS OF TIMESTAMP.[12] Notably both of Oracle's implementations depend on the database's rollback segment 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.[13]

MariaDB 10.3 implements system-versioned tables.[14] MariaDB 10.4.3 added support for application-versioned tables.[15]

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.[16][17]

See also

References

  1. ↑ Paulley (December 16, 2011), "SQL:2011 is published" (blog), IA, Sybase, http://iablog.sybase.com/paulley/?p=2612 
  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". https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/20d329a6751910149d5fdbc4800f92ff.html#loio20d329a6751910149d5fdbc4800f92ff__system_versioning_configuration. 
  5. ↑ "SAP Help Portal". https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/20fcf24075191014a89e9dc7b8408b26.html. 
  6. ↑ "SAP Help Portal". https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.04/en-US/20fcf24075191014a89e9dc7b8408b26.html. 
  7. ↑ "Data management". http://www.ibm.com/developerworks/data/library/techarticle/dm-1204whatsnewdb210/index.html. 
  8. ↑ "Data management". http://www.ibm.com/developerworks/data/library/techarticle/dm-1204db2temporaldata/. 
  9. ↑ "Implementing Temporal Validity". http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html. 
  10. ↑ "flashback query in oracle 9i". https://www.oracle-developer.net/display.php?id=210. 
  11. ↑ "Flashback Query". https://oracle-base.com/articles/9i/flashback-query. 
  12. ↑ "Database SQL Reference". http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm. 
  13. ↑ "Temporal Tables - SQL Server". 16 October 2023. https://msdn.microsoft.com/en-us/library/dn935015.aspx. 
  14. ↑ "Changes & Improvements in MariaDB 10.3". https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-103/. 
  15. ↑ "Application-Time Periods". https://mariadb.com/kb/en/application-time-periods/. 
  16. ↑ "AS OF SYSTEM TIME | CockroachDB Docs". https://www.cockroachlabs.com/docs/stable/as-of-system-time.html. 
  17. ↑ "Time-Travel Queries: SELECT witty_subtitle FROM THE FUTURE". Cockroach Labs. 22 June 2016. https://www.cockroachlabs.com/blog/time-travel-queries-select-witty_subtitle-the_future/. 



Retrieved from "https://handwiki.org/wiki/index.php?title=SQL:2011&oldid=4350060"