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.
- 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.
- 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.