Function-based indexes contain one or more expressions as index keys.You can create indexes directly on functions and expressions.
Like computed columns, function-based indexes are helpful for user-defined ordering and decision-support system (DSS) applications, which frequently require intensive data manipulation. Function-based indexes simplify the tasks in these applications and improve performance.
For more information on computed columns, see “Computed columns”.
Function-based indexes are similar to computed columns in that they both allow you to create indexes on expressions.
However, there are significant differences:
A function-based index allows you to index the expression directly. It does not first create the column.
A function-based index must be deterministic and cannot reference global variables, unlike a computed column.
You can create a clustered computed column index, but not a clustered function-based index.
Before you can execute create index, you must enable the database option select into:
sp_dboption <dbname>, 'select into', true
See the Reference Manual: Commands and the Reference Manual: Procedures.
