共用方式為


計算資料行的索引

適用於:SQL Server、Azure SQL 資料庫 和 Azure SQL 受控執行個體。

只要符合下列要求,您就可以在計算資料行上定義索引:

  • 擁有權需求
  • 決定性需求
  • 有效位數需求
  • 資料類型需求
  • SET 選項需求

注意

當要建立或變更計算資料行上的索引或索引檢視表時,SET QUOTED_IDENTIFIER 必須為 ON。 如需詳細資訊,請參閱 SET QUOTED_IDENTIFIER (Transact-SQL)

擁有權需求

計算資料行中的所有函數參考都必須與資料表具有相同的擁有者。

決定性需求

如果運算式一定會針對指定的輸入集傳回相同的結果,這些運算式就具有決定性。 IsDeterministic 函數的 IsDeterministic 屬性會報告 computed_column_expression 是否具決定性。

computed_column_expression 必須具決定性。 若下列全部情況成立,computed_column_expression 就會具決定性:

  • 運算式所參考的所有函數都具有決定性而且是精確的。 這些函數包括使用者自訂函數與內建函數。 如需詳細資訊,請參閱 決定性與非決定性函數。 如果計算資料行是 PERSISTED,則函式可能就不精確。 如需詳細資訊,請參閱本文稍後的在保存的計算資料行上建立索引

  • 運算式中參考的所有資料行都來自包含計算資料行的資料表。

  • 沒有資料行參考從多個資料列中提取資料。 例如,SUMAVG 這類彙總函式將取決於來自多個資料列的資料,並使得 computed_column_expression 不具決定性。

  • computed_column_expression 沒有系統資料存取或使用者資料存取。

任何包含 Common Language Runtime (CLR) 運算式的計算資料行都必須具有決定性,而且必須在製作索引前標示為 PERSISTED。 在計算的資料行定義中允許 CLR 使用者自訂類型的運算式。 具有 CLR 使用者自訂類型的計算資料行,只要類型是可比較的就可製作索引。 如需詳細資訊,請參閱 CLR 使用者定義型別

CAST 和 CONVERT

當您在 SQL Server 中參考索引計算資料行內的日期資料類型字串常值時,建議使用具有決定性的日期格式樣式,將常值明確轉換成想要的日期類型。 如需具決定性之日期格式樣式的清單,請參閱 CAST 和 CONVERT

如需詳細資訊,請參閱將常值日期字串轉換成 DATE 值的非決定性轉換

相容性層級

除非相容性層級設定為 80 或以下;否則,定序之間非 Unicode 字元資料的隱含轉換會被視為不具決定性。

當資料庫相容性層級設定為 90 時,您就不能在包含這些運算式的計算資料行上建立索引。 但是,包含這些來自升級資料庫之運算式的現有計算資料行是可以維護的。 如果您使用包含字串到日期之隱含轉換的索引計算資料行,請確定資料庫和應用程式中 LANGUAGEDATEFORMAT 設定為一致,以避免索引損毀。

相容性層級 90 對應至 SQL Server 2005 (9.x)。

有效位數需求

computed_column_expression 必須精確。 若下列一或多種情況成立, computed_column_expression 就會精確:

  • 它並非 floatreal 資料類型的運算式。

  • 它的定義中並沒有使用 floatreal 資料類型。 例如,在下列陳述式中, y 資料行是 int 且具決定性,但並不精確。

    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 運算式可用於索引檢視中,但不能作為索引鍵。 對於計算資料行也是如此。 若任何函數、運算式、使用者定義函數包含任何 floatreal 運算式,均會被視為不精確。 這包含邏輯運算式 (比較)。

COLUMNPROPERTY 函式的 IsPrecise 屬性會報告 computed_column_expression 是否精確。

資料類型需求

  • 針對計算資料行所定義的 computed_column_expression 並不能評估為 textntextimage 資料類型。
  • imagentexttextvarchar(max)nvarchar(max)varbinary(max)以及 xml 資料類型所衍生的計算資料行,只要其資料類型可作為索引鍵資料行,就可以製作成索引。
  • imagentext以及 text 資料類型所衍生的計算資料行,只要其資料類型可作為非索引鍵之索引資料行,就可作為非叢集索引中無索引鍵 (內含) 的資料行。

SET 選項需求

  • 執行定義計算資料行的 CREATE TABLEALTER TABLE 陳述式時,ANSI_NULLS 連接層級選項必須設定為 ONOBJECTPROPERTY 函數將透過 IsAnsiNullsOn 屬性,報告選項是否為開啟狀態。

  • 建立索引的連線,以及嘗試執行會變更索引值之 INSERTUPDATEDELETE 陳述式的所有連線,都必須有六個 SET 選項設成 ON,以及一個選項設成 OFF。 若有任何 SELECT 陳述式是由不具相同選項設定的連線所執行,最佳化工具將略過計算資料行上的索引。

    NUMERIC_ROUNDABORT 選項必須設為 OFF,而且下列選項必須設為 ON

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

注意

當資料庫相容性層級設定為 90 或更高數值時,將 ANSI_WARNINGS 設定為 ON,會將 ARITHABORT 隱含設定為 ON

在保存的計算資料行上建立索引

有時候您可以建立使用具決定性但不精確運算式定義的計算資料行。 當資料行在 CREATE TABLEALTER TABLE 陳述式中標示為 PERSISTED 時,也可以建立索引。

這表示資料庫引擎會將計算值儲存在資料表中,並在更新計算資料行所根據的任何其他資料行時更新這些計算值。 當資料庫引擎在資料行上建立索引,且當查詢中參考該索引時,便會使用這些保存的值。

當資料庫引擎無法證明傳回計算資料行運算式的函式 (特別是在 .NET Framework 中建立的 CLR 函數) 是否具有決定性和是否精確時,此選項可讓您在計算的資料行上建立索引。

注意

您無法在計算資料行上建立篩選的索引

下一步