Table Restrictions On Using UDT Columns | CREATE TABLE | Teradata Vantage - Table Restrictions On Using UDT Columns - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™
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.