Rules and Guidelines | ARRAY/VARRAY Data Type | Teradata Vantage - 17.10 - Rules and Guidelines - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Data Types and Literals

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)
  • The 1-D ARRAY type only supports a lower bound of 1. This means that every 1-D ARRAY type that is created has a first array element indexed by the number 1.
  • The n-D ARRAY type supports an optional user-specified lower bound that can be a negative or positive integer number.
  • The maximum size of an ARRAY type and its autogenerated transform string must not exceed 64 KB because SQL Engine stores its data within the row and because the maximum size of its autogenerated transform string is limited to the maximum size of a VARCHAR type, which is 64,000 Teradata Latin bytes.

    Note that the size of the array is influenced not only by the number of elements, but also by the element type of the array.

  • For an n-D ARRAY type, you must first consider the row size limit of 64 KB. Then there is a limit to the maximum number of dimensions that can be declared within the scope of the array. The minimum number of dimensions that you can create is 2, and the maximum number of dimensions that you can create is 5.
  • You can use the optional DEFAULT NULL clause when creating an ARRAY type to initialize all elements of the ARRAY to null at the time a new instance of the ARRAY type is constructed.

    This clause is particularly useful for ARRAY types that are expected to be fully populated.

    When you use this clause, the action prevents all subset operations such as AVERAGE, UPDATE, or COUNT for arrays from aborting and returning an error because they refer to an element that is not initialized.