# 計算資料行的索引Indexes on Computed Columns

• 擁有權需求Ownership requirements
• 決定性需求Determinism requirements
• 有效位數需求Precision requirements
• 資料類型需求Data type requirements
• SET 選項需求SET option requirements

## 決定性需求Determinism Requirements

computed_column_expression 必須具決定性。The computed_column_expression must be deterministic. 若下列全部情況成立，computed_column_expression 就會具決定性：A computed_column_expression is deterministic when all of the following are 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. 如果計算的資料行是 PERSISTED，函數可能就不精確。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. 例如，SUM 或 AVG 這類彙總函數將取決於來自多個資料列的資料，並使得 computed_column_expression 不具決定性。For example, aggregate functions such as SUM or AVG depend on data from multiple rows and would make a computed_column_expression nondeterministic.

• computed_column_expression 沒有系統資料存取或使用者資料存取。The computed_column_expression has no system data access or user data access.

## 有效位數需求Precision Requirements

computed_column_expression 必須精確。The computed_column_expression must be precise. 若下列一或多種情況成立， computed_column_expression 就會精確：A computed_column_expression is precise when one or more of the following is true:

• 它並非 floatreal 資料類型的運算式。It is not an expression of the float or real data types.

• 它的定義中並沒有使用 floatreal 資料類型。It does not use a float or real data type in its definition. 例如，在下列陳述式中， `y` 資料行是 int 且具決定性，但並不精確。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);
``````

COLUMNPROPERTY 函數的 IsPrecise 屬性會報告 computed_column_expression 是否精確。The IsPrecise property of the COLUMNPROPERTY function reports whether a computed_column_expression is precise.

## 資料類型需求Data Type Requirements

• 針對計算資料行所定義的 computed_column_expression 並不能評估為 textntextimage 資料類型。The computed_column_expression defined for the computed column cannot evaluate to the text, ntext, or image data types.
• imagentexttextvarchar(max)nvarchar(max)varbinary(max) 以及 xml 資料類型所衍生的計算資料行，只要其資料類型可作為索引鍵資料行，就可以製作成索引。Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be indexed as long as the computed column data type is allowable as an index key column.
• imagentext以及 text 資料類型所衍生的計算資料行，只要其資料類型可作為非索引鍵之索引資料行，就可作為非叢集索引中無索引鍵 (內含) 的資料行。Computed columns derived from image, ntext, and text data types can be nonkey (included) columns in a nonclustered index as long as the computed column data type is allowable as a nonkey index column.

## SET 選項需求SET Option Requirements

• 執行定義計算資料行的 CREATE TABLE 或 ALTER TABLE 陳述式時，ANSI_NULLS 連接層級選項必須設定為 ON。The ANSI_NULLS connection-level option must be set to ON when the CREATE TABLE or ALTER TABLE statement that defines the computed column is executed. OBJECTPROPERTY 函數將透過 IsAnsiNullsOn 屬性，報告選項是否為開啟狀態。The OBJECTPROPERTY function reports whether the option is on through the IsAnsiNullsOn property.

• 建立索引的連接，以及嘗試執行會變更索引值之 INSERT、UPDATE 或 DELETE 陳述式的所有連線，都必須有六個 SET 選項設成 ON，以及一個選項設成 OFF。The connection on which the index is created, and all connections trying 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. 若有任何 SELECT 陳述式是由不具相同選項設定的連接所執行，最佳化工具將略過計算資料行上的索引。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.

• NUMERIC_ROUNDABORT 選項必須設為 OFF，而且下列選項必須設為 ON：The NUMERIC_ROUNDABORT option must be set to OFF, and the following options must be set to ON:
• ANSI_NULLSANSI_NULLS
• ANSI_WARNINGSANSI_WARNINGS
• ARITHABORTARITHABORT
• CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIERQUOTED_IDENTIFIER

## 在保存的計算資料行上建立索引Creating Indexes on Persisted Computed Columns

Database EngineDatabase Engine 無法證明傳回計算資料行運算式的函數 (特別是在 .NET Framework.NET Framework中建立的 CLR 函數) 是否具有決定性和是否精確時，此選項可讓您在計算的資料行上建立索引。This option enables you to create an index on a computed column when Database EngineDatabase Engine cannot prove with accuracy whether a function that returns computed column expressions, particularly a CLR function that is created in the .NET Framework.NET Framework, is both deterministic and precise.