Global Temporary and Volatile Tables | CREATE TABLE | Teradata Vantage - Global Temporary Tables - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
Product Category
Teradata Vantage™

Global temporary tables have a persistent definition but do not have persistent contents across sessions.

The following list describes characteristics of global temporary tables:
  • Space usage is charged to the temporary space of the user.

    A minimum of 4KB times the number of AMPs on the system of permanent space is also required to contain the table header for each global temporary table.

  • A single session can materialize up to 2,000 global temporary tables at one time.
  • You materialize a global temporary table locally by referencing it in a data manipulation request. To materialize a global temporary table, you must have the appropriate privilege on the base global temporary table or on the containing database or user as required by the request that materializes the table.
  • Any number of different sessions can materialize the same table definition, but the contents are different depending on the DML requests made against the individual materialized tables during the course of a session.
  • You can log global temporary table updates by specifying the LOG option for the CREATE TABLE statement. LOG is the default.
  • You can save the contents of a materialized global temporary table across transactions by specifying ON COMMIT PRESERVE ROWS as the last keywords in the CREATE TABLE statement. The default is not to preserve table contents after a transaction completes (DELETE).
  • The primary index for a global temporary table can be nonpartitioned or row-partitioned. The table can be defined without a primary index. See Partitioned and Nonpartitioned Primary Indexes and Nonpartitioned NoPI Tables.
You cannot specify the following for global temporary tables:
  • Referential integrity constraints
  • Permanent journaling
  • Column partitioning
  • Primary AMP index
  • Hash or join indexes

The database does not check privileges for the materialized instances of global temporary tables because those tables exist only for the duration of the session in which they are materialized.

See also CREATE TABLE Global and Temporary.

For information about how block compression works with global temporary tables, see Block-Level Compression and Tables. Also see Teradata Vantage™ - Database Design, B035-1094 and the DBS Control utility in Teradata Vantage™ - Database Utilities, B035-1102.