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

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

The kind of table determines duplicate row control. See Teradata Vantage™ - 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. Vantage 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 Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.