Creating Indexes on Computed Columns

Indexes can be defined on computed columns, provided these requirements are met:

• The computed_column_expression must be deterministic. Expressions are deterministic if they always return the same result for a given set of inputs. computed_column_expression is deterministic if:
  • All functions referenced by the expression are deterministic and precise. This includes both user-defined and built-in functions. For more information, see Deterministic and Nondeterministic Functions.
    
    
  • All columns referenced in the expression come from the table containing 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.

  The IsDeterministic property of the COLUMNPROPERTY function reports whether a computed_column_expression is deterministic.

  A computed column expression is precise if:

  • It is not an expression of the float data type
    
    
  • It does not use in its definition a float data type. For example, in the following statement, column y is int and deterministic, but not precise:

    CREATE TABLE t2 (a int, b int, c int, x float, 
        y AS CASE x 
                WHEN 0 THEN a 
                WHEN 1 THEN b 
                ELSE c 
            END)

  The IsPrecise property of the COLUMNPROPERTY function reports whether a computed_column_expression is precise.

  Note  Any float expression is considered nonprecise and cannot be a key of an index; a float expression can be used in an indexed view but not as a key. This is true also for computed columns. Any function, expression, user-defined function, or view definition is considered non-deterministic if it contains any float expressions, including logical ones (comparisons).

• The ANSI_NULL connection-level option must be set to ON when the CREATE TABLE statement is executed. The OBJECTPROPERTY function reports whether the option is on through the IsAnsiNullsOn property.
  
  
• The computed_column_expression defined for the computed column cannot evaluate to the text, ntext, or image data types.
  
  
• The connection on which the index is created, and all connections attempting INSERT, UPDATE, or DELETE statements that will change values in the index, must have six SET options set to ON and one option set to OFF. The optimizer ignores an index on a computed column for any SELECT statement executed by a connection that does not have these same option settings.

  These options must be set to ON:

  • ANSI_NULLS
    
    
  • ANSI_PADDING
    
    
  • ANSI_WARNINGS
    
    
  • ARITHABORT
    
    
  • CONCAT_NULL_YIELDS_NULL
    
    
  • QUOTED_IDENTIFIER

  In addition to these ON settings, the NUMERIC_ROUNDABORT option must be set to OFF. For more information, see SET Options That Affect Results.