15.00 - Volatile Tables - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Volatile Tables

Volatile tables do not have a persistent definition; they must be newly created each time you need to use them. The table definition is cached only for the duration of the session in which it is created.

If you frequently reuse particular volatile table definitions, consider writing a macro that contains the CREATE TABLE text for those volatile tables.

Because volatile tables are private to the session that creates them, the system does not check their creation, access, modification, and drop privileges.

The following list details the general characteristics of volatile tables.

  • Both the contents and the definition of a volatile table are destroyed when a system reset occurs.
  • Space usage is charged to the login user spool space.
  • A single session can materialize up to 1,000 volatile tables at one time.
  • The primary index for a volatile table can be unpartitioned or partitioned (see “Partitioned and Unpartitioned Primary Indexes” on page 603), or the table can be defined with no primary index (see “Unpartitioned NoPI Tables” on page 575).
  • You cannot create secondary, hash, or join indexes on a volatile table.
  • The following options are not permitted for volatile tables.
  • Referential integrity constraints
  • CHECK constraints
  • Permanent journaling
  • DEFAULT clause
  • TITLE clause
  • Named indexes
  • Column partitioning
  • Otherwise, the options for volatile tables are identical to those for global temporary tables.