15.00 - SET and MULTISET - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

SET and MULTISET

SET tables obey set theory semantics and the relational data model by not permitting duplicate rows in a table.

MULTISET tables obey bag, or multiset, semantics. They violate set theory semantics and the relational data model by permitting duplicate rows in a table.

 

IF you specify this keyword …

THEN duplicate rows are …

SET

not permitted for the table.

MULTISET

permitted for the table in compliance with the ANSI SQL:2011 standard.

If neither SET nor MULTISET is specified explicitly, then the table kind assignment is made according to the rules stated in the following table.

 

IF the session creating the table is in this mode …

THEN this keyword is implied …

ANSI

MULTISET

Teradata

SET

There are 2 exceptions to this rule.

  • The case where you copy a table definition using the non‑subquery form of the CREATE TABLE … AS syntax.
  • In this case, the default table kind is the table kind of the source table irrespective of the current session mode.

  • The case where you create a column‑partitioned table.
  • In this case, the default table kind is always MULTISET, regardless of the session mode or the setting of the DBS Control parameter PrimaryIndexDefault.

    Teradata Database creates unpartitioned NoPI (see “Unpartitioned NoPI Tables” on page 575) and column‑partitioned tables (see “Column‑Partitioned Tables” on page 577) as MULTISET tables by default. Neither type can be created as, or altered to become, SET tables.

    Some client utilities have restrictions with respect to MULTISET tables. For specific information on these restrictions, see the appropriate client utility manual.

  • Teradata FastLoad Reference
  • Teradata Archive/Recovery Utility Reference
  • Teradata Parallel Data Pump Reference