table_kind | CREATE TABLE SQL statement | Teradata Database - table_kind

The kind of table determines duplicate row control. See SQL Data Definition Language Detailed Topics, B035-1184. You can create the table 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 do the following:

Copy a table definition using the nonsubquery form of CREATE TABLE .. AS. 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 a column or set of columns in the table definition, the table cannot have duplicate rows even if it is declared as MULTISET. Teradata Database creates NoPI and column-partitioned tables as MULTISET tables by default.

Client utilities can 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 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 logon 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 SQL Data Definition Language Detailed Topics, B035-1184.

table_kind | CREATE TABLE SQL statement | Teradata Database - table_kind - Teradata Database

SQL Data Definition Language Syntax and Examples