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

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

As it does with other functions and operators, Vantage 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, Vantage converts the VARBYTE value into a temporary BLOB and passes it to the UDF. Because Vantage 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.