16.20 - Table Restrictions On Using UDT Columns - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)
The general restrictions on the use of UDT columns in tables are the following.
  • A table containing ARRAY, VARRAY, Period or Geospatial columns must have at least one non-Period or Geospatial column because an ARRAY, VARRAY, Period or Geospatial column cannot be a component of an index.
  • The first column of the table cannot have a Geospatial, ARRAY, VARRAY, or Period data type if the PRIMARY KEY or UNIQUE attribute is not specified for any of the non-UDT columns.

    An ARRAY, VARRAY, Geospatial, or Period column cannot be the primary index for a table, whether by default or by design.

  • A UDT must have an ordering and transform group defined prior to its usage as a column type in any table. See CREATE ORDERING and REPLACE ORDERING and CREATE TRANSFORM and REPLACE TRANSFORM.

    Because ARRAY, VARRAY, Geospatial, and Period data types are implemented internally as UDTs, you do not have to define ordering and transform definitions for them.

    See Prerequisites for Specifying a UDT, ARRAY, VARRAY, Period, or Geospatial Column in a Table Definition.

  • Table header space is reduced for each UDT, ARRAY, VARRAY, Geospatial, or Period column specified in a table definition.

    Each UDT column declared consumes approximately 80 bytes of table header space. Depending on other options you declare for a table, including multivalue and algorithmic compression, partitioned primary indexes, secondary indexes, and so on, the table header size limit can eventually be exceeded, returning an error message to the requestor.

    In the absence of other feature specifications, the limit on the number of UDT, ARRAY, VARRAY, Geospatial, and Period columns per table is approximately 1,600 columns. This assumes that your system permits fat table headers. For details about table headers, see Teradata Vantage™ - Database Design, B035-1094.