16.10 - Temporary Tables - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Published
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)
Last Update
2018-04-25

Temporary tables are useful for temporary storage of data. Teradata Database supports these types of temporary tables.

Type Usage
Global temporary A global temporary table has a persistent table definition that is stored in the Data Dictionary. Any number of sessions can materialize and populate their own local copies that are retained until session logoff.

Global temporary tables are useful for storing temporary, intermediate results from multiple queries into working tables that are frequently used by applications.

Global temporary tables are identical to ANSI global temporary tables.

Volatile Like global temporary tables, the contents of volatile tables are only retained for the duration of a session. However, volatile tables do not have persistent definitions. To populate a volatile table, a session must first create the definition.
Global temporary trace Global temporary trace tables are useful for debugging external routines (UDFs, UDMs, and external stored procedures). During execution, external routines can write trace output to columns in a global temporary trace table.

Like global temporary tables, global temporary trace tables have persistent definitions, but do not retain rows across sessions.

Materialized instances of a global temporary table share the following characteristics with volatile tables:

  • Private to the session that created them.
  • Contents cannot be shared by other sessions.
  • Optionally emptied at the end of each transaction using the ON COMMIT PRESERVE/DELETE rows option in the CREATE TABLE statement.
  • Activity optionally logged in the transient journal using the LOG/NO LOG option in the CREATE TABLE statement.
  • Dropped automatically when a session ends.