Using Computed Columns

When you add a new computed column without specifying nullability and the materialization property, the default option is nullable and not materialized.

When you add a new materialized computed column, the computed_column_expression is evaluated for each existing row in the table, and the result is stored in the table.

You cannot add new computed columns and add or modify their base columns at the same time.

You can modify the entire definition of an existing computed column. This is a quick way to drop the computed column and add a new one with the same name. Such a column behaves like a new computed column: its defaults are not materialized and nullable, if you do not specify these options.

You can modify the materialization property of an existing computed column without changing its other properties, such as the expression that defines it or its nullability.

When you modify a not-null, materialized computed column into a virtual column, you must specify “null” in the modify clause.

When you modify a computed column that is not materialized, to materialize it, the computed_column_expression is evaluated for each existing row in the table, and the result is stored in the table.

If you modify existing columns that are index keys, the index is rebuilt.

You cannot modify a materialized computed column into a virtual column if it has been used as an index key; you must first drop the index.

You cannot modify a regular column to become a computed column, or a computed column to become a regular column.

You cannot modify or drop the base column referenced by a computed column.

You cannot drop a computed column if it is used as an index key.