Computed columns, computed column indexes, and function-based indexes provide easier data manipulation and faster data access.
Computed columns are defined by an expression, whether from regular columns in the same row, or functions, arithmetic operators, XML path queries, and so forth.
The expression can be either deterministic or nondeterministic. The deterministic expression always returns the same results from the same set of inputs.
You can create indexes on materialized computed columns as if they were regular columns.
Computed columns and function-based indexes similarly allow you to create indexes on expressions.
Computed columns and function-based indexes differ in some respects:
A computed column provides both shorthand for an expression and indexability, while a function-based index provides no shorthand.
Function-based indexes allow you to create indexes directly on expressions, while to create an index on a computed column, you must create the computed column first.
A computed column can be either deterministic or nondeterministic, but a function-based index must be deterministic. “Deterministic” means that if the input values in an expression are the same, the return values must also be the same. See “Deterministic property”.
You can create a clustered index on a computed column, but not a clustered function-based index.
For more information on function-based indexes, see “Indexing with function-based indexes”.
These are the differences between materialized and not materialized computed columns:
Computed columns can be materialized or not materialized. Columns that are materialized are preevaluated and stored in the table when base columns are inserted or updated. The values associated with them are stored in both the data row and the index row. Any subsequent access to a materialized column does not require reevaluation; its preevaluated result is accessed. Once a column is materialized, each access to it returns the same value.
Columns that are not materialized are sometimes called virtual columns; virtual columns become materialized when they are accessed. If a column is virtual, or not materialized, its result value must be evaluated each time the column is accessed. This means that if the virtual computed column expression is based on a nondeterministic expression, or calls one, it may return different values each time you access it.You may also encounter runtime exceptions, such as domain errors, when you access virtual computed columns.
