Chapter 3 Working With ANSI System-Time Tables - Teradata Database

SQL External Routine Programming

Teradata Database
Release Number
English (United States)
Last Update
Product Category
Teradata® Database

System time is the time period during which the information in a row is or was known to the database. It reflects the database reality, automatically recording when rows have been added, modified, and deleted in tables that include system time.

Use system-versioned system-time tables when historical changes need to be automatically tracked and maintained in the database. For example, system-time tables can be used for some types of regulatory compliance.

In order for a system-time table to be considered a temporal table, and to include the automatic timestamping and tracking behaviors characteristic of system-time temporal tables, the table must be designated to have system versioning in the table definition. It is the system versioning that bestows on the table the temporal capabilities. For purposes of discussion in this book assume that, unless otherwise noted, references to “system-time” tables implicitly refer to “system-versioned system-time” tables.

Note: The material in this chapter covers only the syntax, rules, and other details that apply to ANSI temporal tables. The syntax diagrams presented are extracts of the full diagrams that focus on the temporal syntax.

Most of the existing rules and options that apply to conventional, nontemporal versions of the SQL statements discussed here also apply to the temporal statements. The nontemporal rules and options are not repeated here. For more information on conventional, nontemporal SQL DDL and DML statements, see SQL Data Definition Language and SQL Data Manipulation Language.