16.20 - Type Conversion and Performance - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

As it does with other functions and operators, Teradata Database performs implicit conversion of types that are compatible with LOBs according to the rules of precedence. If you define a UDF that declares a LOB type parameter, you can call the UDF and pass a compatible argument.

IF the UDF declares this parameter type … THEN you can call the UDF and pass this argument type …
CLOB
  • VARCHAR
  • CHAR
BLOB
  • VARBYTE
  • BYTE

In some cases, implicit conversions of types that are compatible with LOBs have performance consequences.

For example, suppose a UDF declares a BLOB parameter. If you call the UDF and pass in a VARBYTE column, Teradata Database converts the VARBYTE value into a temporary BLOB and passes it to the UDF. Because Teradata Database stores the temporary BLOB on disk, the performance cost of the conversion is significant.

A solution is to create an overloaded function that explicitly declares a VARBYTE argument.