Temporary Tables - Advanced SQL Engine - Teradata Database

SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
zwv1557098532464.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1141
lifecycle
previous
Product Category
Teradata Vantageâ„¢

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.