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

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

只要符合下列要求,您就可以在計算資料行上定義索引:You can define indexes on computed columns as long as the following requirements are met:

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

擁有權需求Ownership Requirements

計算資料行中的所有函數參考都必須與資料表具有相同的擁有者。All function references in the computed column must have the same owner as the table.

決定性需求Determinism Requirements

如果運算式一定會針對指定的輸入集傳回相同的結果,這些運算式就具有決定性。Expressions are deterministic if they always return the same result for a specified set of inputs. COLUMNPROPERTY 函數的 IsDeterministic 屬性會報告 computed_column_expression 是否具決定性。The IsDeterministic property of the COLUMNPROPERTY function reports whether a computed_column_expression is deterministic.
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.

任何包含 Common Language Runtime (CLR) 運算式的計算資料行都必須具有決定性,而且必須在製作索引前標示為 PERSISTED。Any computed column that contains a common language runtime (CLR) expression must be deterministic and marked PERSISTED before the column can be indexed. 在計算的資料行定義中允許 CLR 使用者自訂類型的運算式。CLR user-defined type expressions are allowed in computed column definitions. 具有 CLR 使用者自訂類型的計算資料行,只要類型是可比較的就可製作索引。Computed columns whose type is a CLR user-defined type can be indexed as long as the type is comparable. 如需詳細資訊,請參閱 CLR 使用者定義型別For more information, see CLR User-Defined Types.

CAST 和 CONVERTCAST and CONVERT

當您在 SQL ServerSQL Server中參考索引計算資料行內的日期資料類型字串常值時,我們建議您使用具有決定性的日期格式樣式,將常值明確轉換成您想要的日期類型。When you refer to string literals of the date data type in indexed computed columns in SQL ServerSQL Server, we recommend that you explicitly convert the literal to the date type that you want by using a deterministic date format style. 如需具決定性之日期格式樣式的清單,請參閱 CAST 和 CONVERTFor a list of the date format styles that are deterministic, see CAST and CONVERT.

如需詳細資訊,請參閱將常值日期字串轉換成 DATE 值的非決定性轉換For more information, see Nondeterministic conversion of literal date strings into DATE values.

相容性層級Compatibility level

除非相容性層級設定為 80 或以下;否則,定序之間非 Unicode 字元資料的隱含轉換會被視為不具決定性。Implicit conversion of non-Unicode character data between collations is considered nondeterministic, unless the compatibility level is set to 80 or earlier.

當資料庫相容性層級設定為 90 時,您就不能在包含這些運算式的計算資料行上建立索引。When the database compatibility level setting is 90, you cannot create indexes on computed columns that contain these expressions. 但是,包含這些來自升級資料庫之運算式的現有計算資料行是可以維護的。However, existing computed columns that contain these expressions from an upgraded database are maintainable. 如果您使用包含字串到日期之隱含轉換的索引計算資料行,請確定資料庫和應用程式中 LANGUAGE 和 DATEFORMAT 設定是一致的,以避免索引損毀。If you use indexed computed columns that contain implicit string to date conversions, to avoid possible index corruption, make sure that the LANGUAGE and DATEFORMAT settings are consistent in your databases and applications.

相容性層級 90 對應至 SQL Server 2005。Compatibility level 90 corresponds to SQL Server 2005.

有效位數需求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);  
    

注意

任何 floatreal 運算式都會視為不精確,並且不能作為索引的索引鍵; floatreal 運算式可用於索引檢視中,但不能作為索引鍵。Any float or real expression is considered imprecise and cannot be a key of an index; a float or real expression can be used in an indexed view but not as a key. 對於計算資料行也是如此。This is true also for computed columns. 若任何函數、運算式、使用者定義函數包含任何 floatreal 運算式,均會被視為不精確。Any function, expression, or user-defined function is considered imprecise if it contains any float or real expressions. 這包含邏輯運算式 (比較)。This includes logical ones (comparisons).

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_PADDINGANSI_PADDING
    • ANSI_WARNINGSANSI_WARNINGS
    • ARITHABORTARITHABORT
    • CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIERQUOTED_IDENTIFIER

注意

當資料庫的相容性層級設定為 90 或以上時,將 ANSI_WARNINGS 設定為 ON 也會將 ARITHABORT 隱含設定為 ON。Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher.

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

有時候您可以建立使用具決定性但不精確運算式定義的計算資料行。Sometimes you can create a computed column that is defined with an expression that is deterministic yet imprecise. 您可以在資料行於 CREATE TABLE 或 ALTER TABLE 陳述式中標記為 PERSISTED 時執行此動作。You can do this when the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement.

這表示 Database EngineDatabase Engine 會將計算值儲存在資料表中,並在更新計算資料行所根據的任何其他資料行時更新這些計算值。This means that the Database EngineDatabase Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. Database EngineDatabase Engine 在資料行上建立某索引,且查詢中參考該索引時,它會使用這些保存值。The Database EngineDatabase Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query.

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.

COLUMNPROPERTY (Transact-SQL) COLUMNPROPERTY (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL)ALTER TABLE (Transact-SQL)