generation-clause

Syntax

GENERATED ALWAYS AS ( value-expression )

value-expression

An Expression that resolves to a single value, with some limitations:

  • The generation-clause may reference other non-generated columns in the table, but it must not reference any generated column. The generation-clause must not reference a column in another table.
  • The generation-clause must not include subqueries.
  • The generation-clause may invoke user-coded functions, if the functions meet the requirements in the User Function Restrictions section below.

User Function Restrictions

The generation-clause may invoke user-coded functions, if the functions meet the following requirements:

  • The functions must not read or write SQL data.
  • The functions must have been declared DETERMINISTIC.
  • The functions must not invoke any of the following possibly non-deterministic system functions:

Example

CREATE TABLE employee(
  employeeID           int,
  name                 varchar( 50 ),
  caseInsensitiveName  GENERATED ALWAYS AS( UPPER( name ) )
  );

CREATE INDEX caseInsensitiveEmployeeName
  ON employee( caseInsensitiveName );