table_kind | CREATE TABLE SQL statement | Teradata Vantage - 17.10 - table_kind - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

The kind of table determines duplicate row control. See Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184 for details. The table can be created as a global temporary table or a volatile table. If you do not specify global temporary or volatile, then the table is defined as a persistent user data table, also referred to as base tables. Hash and join index tables are also considered base tables.

If you do not explicitly specify SET or MULTISET, the table kind assignment depends on the session mode:

Session Mode Default
ANSI MULTISET
Teradata SET
The session mode default is in effect, except when you:
  • Copy a table definition using the non-subquery form of the CREATE TABLE .. AS syntax. The default table kind is the table kind of the source table, regardless of the current session mode.
  • Create a column-partitioned table. The default table kind is always MULTISET, regardless of the session mode or the setting of the DBS Control parameter PrimaryIndexDefault.
MULTISET
Duplicate rows are permitted, in compliance with the ANSI SQL:2011 standard. If there are uniqueness constraints on any column or set of columns in the table definition, then the table cannot have duplicate rows even if it is declared as MULTISET. Vantage creates NoPI and column-partitioned tables as MULTISET tables by default.
Some client utilities have restrictions regarding MULTISET tables. See the appropriate documentation:
  • Teradata® FastLoad Reference, B035-2411
  • Teradata® Parallel Data Pump Reference, B035-3021
SET
Duplicate rows are not permitted. You cannot create the following kinds of tables as SET tables:
  • Temporal
  • Column-partitioned
  • NoPI
GLOBAL TEMPORARY
A temporary table definition is created and stored in the data dictionary for future materialization. You can create global temporary tables by copying a table WITH NO DATA, but not by copying a table WITH DATA.
You cannot create a column-partitioned global temporary table.
You cannot create a global temporary table with row-level security constraint columns.
VOLATILE
Create a volatile table. The definition is of a volatile table is retained in memory only for the duration of the session in which it is defined. Space usage is charged to the login user spool space. Because volatile tables are private to the session that creates them, the system does not check the creation, access, modification, and drop privileges. A single session can materialize up to 1,000 volatile tables.
The contents and the definition of a volatile table are dropped when a system reset occurs.
If you frequently reuse particular volatile table definitions, consider writing a macro that contains the CREATE TABLE text for those volatile tables.
You cannot create a column-partitioned volatile table or normalized volatile table.
You cannot create secondary, hash, or join indexes on a volatile table.
You cannot create a volatile table with row-level security constraint columns.
For more information about volatile tables, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.