Using Distinct UDTs | CREATE TYPE | Teradata Vantage - Function of Distinct UDTs - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • DROP ORDERING (see DROP ORDERING)
  • DROP TRANSFORM (see DROP TRANSFORM)

Vantage 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.