16.20 - Function of UDT Ordering Maps - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)

A UDT ordering specifies how two values of the same UDT are to be compared using relational operators (see Teradata Vantage™ SQL Data Manipulation Language , B035-1146 ). The system also uses the ordering definition for other comparison-related operations such as groupings, orderings, sorts, and uniqueness determinations.

An ordering is associated with a UDT by means of the CREATE ORDERING statement. You must define an ordering for all UDTs that you develop, and you must complete the definition before you can use it to define the data type of any table. If you attempt to use a UDT in any way without first creating an ordering for it, the system returns an error to the requestor.

There are a number of SQL operations that depend on an ordering definition to complete. For instance, a UDT must have an ordering to be used in any of the following operations.
  • WHERE clause with comparison operators
  • [NOT] IN
  • EXCEPT and MINUS set operators
  • INTERSECT set operator
  • UNION set operator
  • CREATE TABLE when the table contains a column typed with the UDT
FOR this type of UDT … THE ordering functionality …
distinct is generated automatically by the system.

This is not true if the source data type is a LOB. In this case, you must explicitly create an ordering for the LOB using the CREATE ORDERING statement in conjunction with an appropriate external routine.

If the system-generated ordering semantics is adequate, you need not define explicit ordering functionality using the CREATE ORDERING statement.

If your applications require different or richer ordering semantics, then you can specify explicit ordering functionality using CREATE ORDERING.

structured must be defined explicitly using the CREATE ORDERING statement.