The kind of table determines duplicate row control. See 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:
- 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.
- 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. Teradata Database 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
- Teradata Archive/Recovery Utility Reference
- Teradata Parallel Data Pump Reference
- Duplicate rows are not permitted. You cannot create the following kinds of tables as SET tables:
- 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.
- 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 further information about volatile tables, see SQL Data Definition Language - Detailed Topics, B035-1184.