Creating Function-Based Indexes

The expression must be deterministic.

Since the SAP ASE server does not verify the deterministic property of the expression index key, the user must manually maintain the property. A change in this property can cause unexpected results.

As a function-based index key must be deterministic, its result is preevaluated, and reused without reevaluation. The SAP ASE server assumes all function-based index keys to be deterministic and uses their pre-evaluated values when they are referenced in a query; they are reevaluated only when the values of their base columns are changed.

An index can have multiple function-based index keys or a combination of function-based index keys and regular columns.

Expressions used as index keys must be deterministic. An expression key is different from a computed column index key, which needs to be evaluated only once, and does not require the deterministic property. An expression, however, must be reevaluated upon each occurrence of the expression in a specified query, and must always return the same result.

If a user-defined function that is referenced by a function-based index is dropped or becomes invalid, any operations that call that function fail.

The SAP ASE server does not support clustered function-based indexes.

You cannot create a function-based index with the sorted_data option.

Once you create an index key on an expression, subsequent queries recognize the expression as an index key only if the expression is exactly the same as the expression used to create the index key.

All insert, delete, and update operations on base columns cause the SAP ASE server to automatically update the value of function-based index keys.