Routines: Scalar functions

A scalar function is a function that, for each set of one or more scalar parameters, returns a single scalar value. Examples of scalar functions include the LENGTH function, and the SUBSTR function.

Scalar functions can also be created that do complex mathematical calculations on function input parameters. Scalar functions can be referenced anywhere that an expression is valid within an SQL statement, such as in a select-list, or in a FROM clause.

  • Built-in scalar functions perform well.
  • Built-in scalar functions are strongly typed.
  • Can be referenced with SQL statements wherever expressions are supported.
  • Logic is executed on the server as part of the SQL statement that references it.
  • Output of a scalar UDF can be used directly by the statement that references the function.
  • When used in predicates, scalar UDF usage can improve overall query performance. When a scalar functions are applied to a set of candidate rows at the server, it can act as a filter, thus limiting the number of rows that must be returned to the client.
  • For external scalar user-defined functions, state can be maintained between the iterative invocations of the function by using a scratchpad.
  • PARAMETER STYLE HIVE scalar functions that are used in predicates can be pushed down to the reader for better performance.
  • By design, they only return a single scalar value.
  • Transaction management is not supported within scalar functions. Commits and rollbacks cannot be executed within scalar function bodies.
  • Result sets cannot be returned from scalar functions.
  • In a single partition database user-defined external scalar UDFs can contain SQL statements. These statements can read data from tables, but cannot modify data in tables.
  • In a multi-partition database environment, user-defined scalar UDFs cannot contain SQL statements.
Common uses
  • To manipulate strings within SQL statements.
  • To perform basic mathematical operations within SQL statements.
  • User-defined scalar functions can be created to extend the existing set of built-in scalar functions. For example, you can create a complex mathematical function, by re-using the existing built-in scalar functions along with other logic.
Supported implementations
  • Sourced implementation
  • External implementation