Table Types - Teradata Database

Database Introduction

Product

Teradata Database

Release Number

15.00

Language

English (United States)

Last Update

2018-09-25

dita:id

B035-1091

lifecycle

Product Category

Teradata® Database

Table Type	Description
ANSI Temporal	ANSI-compliant support for temporal tables. Using temporal tables, Teradata Database can process statements and queries that include time-based reasoning. Temporal tables record both system time (the time period when the information was recorded in the database) and valid time (the time period when the information is in effect or true in a real-world application).
Derived	A derived table: Is a type of temporary table obtained from one or more other tables as the result of a subquery. Is specified in an SQL SELECT statement. Avoids the need to use the CREATE and DROP TABLE statements for storing retrieved information. Is useful when you are coding more sophisticated, complex queries.
Error Logging	Error logging tables: Store information about errors on an associated permanent table. Log information about insert and update errors.
Global Temporary	Global temporary tables: Are private to the session. Are dropped automatically at the end of a session. Have a persistent table definition stored in the Data Dictionary. The saved definition may be shared by multiple users and sessions with each session getting its own instance of the table.
Global Temporary Trace	Global temporary trace tables: Store trace output for the length of the session. Have a persistent table definition stored in the Data Dictionary. Are useful for debugging SQL stored procedures (via a call to an external stored procedure written to the trace output) and external routines (UDFs, UDMs, and external stored procedures).
NoPI	NoPI tables are permanent tables that do not have primary indexes defined on them. They provide a performance advantage when used as staging tables to load data from FastLoad or TPump Array INSERT. They can have secondary indexes defined on them to avoid full-table scans during row access.
Permanent	Permanent tables allow different sessions and users to share table content.
Queue	Queue tables: Are permanent tables with a timestamp column. The timestamp indicates when each row was inserted into the table. Establish first-in first-out (FIFO) ordering of table contents, which is needed for customer applications requiring event processing.
Volatile	Volatile tables are used when: Only one session needs the table. Only the creator needs to access the table. You want better performance than a global temporary table. You do not need the table definition after the session ends. Note: The definition of a volatile table can survive across a system restart if it is contained in a macro.

For more information about table types, see SQL Data Definition Language and Database Design.