Unlike computed columns, function-based index keys must be deterministic. A computed column is still conceptually a column, which, once evaluated and stored, does not require reevaluation. A function or expression, however, must be reevaluated upon each appearance in a query. You cannot use preevaluated data, such as index data, unless the function always evaluates to the same results with the same input set (for more information on function-based indexes, see “Indexing with function-based indexes”).
Adaptive Server internally represents function-based index keys as hidden materialized computed columns. The value of a function-based index key is stored on both a data row and an index page, and therefore assumes all the properties of a materialized computed column.
Adaptive Server assumes all function- or expression-based index keys to be deterministic. When these index keys are referenced in a query, the preevaluated results that are already stored in the index page are used; the index keys are not reevaluated.
Preevaluated results are updated only when the base columns of the function-based index key are updated.
Do not use a nondeterministic function as an index, as in Example 2. The results can be unexpected.
