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.
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 it therefore assumes all the properties of a materialized computed column.
Adaptive Server assumes all function- or expression-based index keys to be deterministic. When they are referenced in a query, the preevaluated results that are already stored in the index page are used; the index keys are not reevaluated.
This preevaluated result is 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.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
