• Actually, if you read carefully, an index on a computed column CAN include multiple rows and columns from other tables. As long as one of the following four conditions are true, then you can use the computed column in an index. Therefore, only the first answer is necessary and the other three are incorrect answers.

    The computed_column_expression must be deterministic. A computed_column_expression is deterministic when one or more of the following is true:

    All functions that are referenced by the expression are deterministic and precise. These functions include both user-defined and built-in functions. For more information, see Deterministic and Nondeterministic Functions. Functions might be imprecise if the computed column is PERSISTED. For more information, see Creating Indexes on Persisted Computed Columns later in this topic.

    All columns that are referenced in the expression come from the table that contains the computed column.

    No column reference pulls data from multiple rows. For example, aggregate functions such as SUM or AVG depend on data from multiple rows and would make a computed_column_expression nondeterministic.

    Has no system data access or user data access.


    Steve Eckhart