16.20 - Function of Distinct UDTs - 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)
Last Update

The concept of distinct UDTs is analogous to the typedef construct in the C and C++ programming languages. Unlike predefined SQL data types, however, which are only moderately strongly typed, distinct UDTs are strongly typed, which makes them ideal for enforcing domains. Note that you cannot declare CHECK constraints on UDT columns, so the UDT-as-domain concept cannot be fully implemented.

For example, you might want to define euro and USDollar as two different distinct UDTs even though both are internally represented as DECIMAL numbers. If euro and USDollar are defined as separate distinct data types, they cannot be compared directly or used interchangeably. In this example, a euro value cannot be compared with or stored in a USDollar type column.

A distinct UDT shares its internal representation with a predefined data type (referred to as its source type), but it is considered to be a separate and incompatible type for most operations. You cannot define a distinct UDT based on any of the Period data types. Distinct UDTs encapsulate their underlying predefined data type. This means that operations and behaviors associated with the underlying data type are not valid, and users of a distinct UDT are only able to interact with its encapsulated value by means of user- or system-defined transforms, orderings, and castings, and manipulations by means of instance methods (see CREATE TRANSFORM and REPLACE TRANSFORM, CREATE ORDERING and REPLACE ORDERING, CREATE CAST and REPLACE CAST, and CREATE METHOD, respectively).

You can drop any of the system-generated semantics for a UDT (see System-Generated Default Functionalities For Distinct UDTs) and replace them with your own definitions using the following SQL statements:
  • DROP CAST, see “DROP CAST” in Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.

Teradata Database supports the definition of instance methods for distinct UDTs, which is an extension to the ANSI SQL standard. For example, you can define an instance method named round() to use with a new distinct UDT.