Function-based indexes can provide an inexpensive option for enhancing the performance of certain legacy applications.
Function-based indexes allow you to create indexes based directly on one or more expressions (see the Transact-SQL Users Guide). When the index is built, the result of evaluating the expressions for each row is stored as an index key value, and is not reevaluated at query execution time. This means lookups on the result of an expression within a SQL query can be very fast. Without function-based indexes, table scans are typically be required to evaluate the expression for each row in the table for comparison. Adaptive Server creates a hidden computed column containing the evaluated key expressions and indexes this column.
You can effectively use function-based indexes for queries that need to apply a function or operation to a column value and compare the result to another column in the same row or to a constant or variable.
You can also obtain the performance benefits of function-based indexes by adding a materialized computed column with index to a table and rewriting the query to use the indexed computed column. This can be a good approach for new application development. The advantage of function-based indexes is that you can simply add to an existing table an index that matches expressions used in existing queries. In this way, you can enhance the performance of legacy applications with a minimal schema addition and no change to SQL query code.
