Sql server tutorial 13 Working with Temporal Data - XADAT.COM
post-template-default,single,single-post,postid-15810,single-format-standard,qode-quick-links-1.0,ajax_fade,page_not_loaded,,qode-theme-ver-11.1,qode-theme-bridge,wpb-js-composer js-comp-ver-5.1.1,vc_responsive

Sql server tutorial 13 Working with Temporal Data No ratings yet.

Sql server tutorial 13 Working with Temporal Data

Key Skills & Concepts

  • Create and Use System-Versioned Tables
  • Create and Use Application-Time Period Tables
  • Create and Use System-Versioned Application-Time Period Tables

In this chapter, I discuss the temporal features that were added to the SQL:2011 standard.
Temporal data is data that changes over time. In SQL, the term temporal data refers to
any data that is defined with one or more associated time periods during which that data is
considered to be effective or valid. You may find it helpful to visualize a timeline where
each temporal data record (row) has a specific point on the timeline at which it becomes
effective and a second point on the timeline when the record ceases to be effective.
However, for records that are currently effective, the point at which it is no longer
effective may be unknown.

It is perhaps an understatement to say the recent temporal data features of the SQL
standard have been long awaited. The need to support temporal data in SQL was
recognized in the early 1980s, as evidenced by books, research papers, and conference
publications from that period. In 1995, the ISO SQL committee launched a project tasked
with adding SQL language extensions to support temporal data. The project completed a
proposed new part of the SQL standard known as SQL/Temporal in 1995. However, there
were competing proposals from the American National Standards Institute (ANSI) and the
UK, which led to considerable controversy and a failure to obtain adequate support for
SQL/Temporal among the ISO SQL committee membership. The work on SQL/Temporal
was canceled in 2001, not only because no new proposals had been submitted, but also
because there was no indication that the DBMS vendors of the time were planning to
implement any new extensions.

A second attempt at adding temporal features to the SQL standard was made in 2008,
largely inspired by the earlier proposals, but with substantially different syntax. Rather
than resurrecting SQL/Temporal, this proposal added “system-versioned tables” to
SQL/Foundation (Part 2 of the SQL Standard). (I discuss system-versioned tables in the
“Create and Use System-Versioned Tables” section of this chapter.) In 2010, another
temporal feature known as “application-time period tables” was added to
SQL/Foundation. (I discuss application-time period tables in the “Create and Use
Application-Time Period Tables” section in this chapter.) Both system-versioned and
application-time period were approved and published as part of the SQL:2011 standard.
Although this may seem surprising at first, system-versioned tables and application-time
period tables are not mutually exclusive, and thus it is possible to construct a systemversioned
application-time period table. (I discuss these tables in the “Create and Use
System-Versioned Application-Time Period Tables” section of this chapter.)
Create and Use System-Versioned Tables

As you learned in Chapter 3, system-versioned tables are one of five types of base tables
supported by the SQL standard. A system-versioned table is a table that contains current
rows as well as row history that is automatically maintained by the DBMS from a system
(database) time perspective. System-versioned tables are useful in situations where an
accurate history of data changes must be maintained for business and/or legal reasons. For
example, a financial institution must keep previous versions of customer account
information so that customers, auditors, and regulatory agencies can be provided a
detailed history of account information. With system-versioned tables, previous versions
of table rows are automatically preserved whenever an update or delete of a row is
successfully processed by the DBMS. Furthermore, in order to maintain the integrity of
the row history, only the DBMS is allowed to maintain the data values in the period begin
and period end columns for each version of a row.

Here is the syntax for the CREATE TABLE statement, including the options required
for defining a system-versioned table:
Compared to the CREATE TABLE syntax you first saw in Chapter 3, you will likely
notice these differences:
The options for GLOBAL TEMPORARY and LOCAL TEMPORARY tables have
been removed because system-versioned tables cannot be defined as temporary tables.
The ON COMMIT clause has been removed because it only applies to temporary

The third line of the syntax contains the table period definition, which must be
included in the table elements list for system-versioned tables. The table period
definition contains the keywords PERIOD FOR SYSTEM_TIME followed by a
comma-separated list containing the period begin column name and the period end
column name, enclosed in a pair of parentheses. The period begin and period end
column names must reference columns that are explicitly defined in the table. These
columns must be defined with a datetime data type, such as DATE or TIMESTAMP,

0 >1>2>3>4>5>6>7>8>9>10>11>12>13>14>15>16>17>18>19>20>


No Comments

Sorry, the comment form is closed at this time.