計算資料行的索引

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

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

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

注意

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

擁有權需求

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

決定性需求

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

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

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

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

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

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

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

CAST 和 CONVERT

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

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

相容性層級

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

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

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

有效位數需求

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

  • 它並非 float 或 real 資料類型的運算式。

  • 它的定義中並沒有使用 float 或 real 資料類型。 例如，在下列陳述式中， 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);

注意

任何 float 或 real 運算式都會視為不精確，並且不能作為索引的索引鍵； float 或 real 運算式可用於索引檢視中，但不能作為索引鍵。 對於計算資料行也是如此。 若任何函數、運算式、使用者定義函數包含任何 float 或 real 運算式，均會被視為不精確。 這包含邏輯運算式 (比較)。

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

資料類型需求

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

SET 選項需求

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

  • 建立索引的連線，以及嘗試執行會變更索引值之 INSERT、UPDATE 或 DELETE 陳述式的所有連線，都必須有六個 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 TABLE 或 ALTER TABLE 陳述式中標示為 PERSISTED 時，也可以建立索引。

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

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

注意

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

下一步