計算列のインデックスIndexes on Computed Columns

適用対象: ○SQL Server ○Azure SQL Database×Azure SQL Data Warehouse ×Parallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse 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 RequirementsOwnership Requirements

計算列のすべての関数参照の所有者がテーブルの所有者と同じである必要があります。All function references in the computed column must have the same owner as the table.

Determinism RequirementsDeterminism 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. 次の 1 つ以上の条件に該当する場合、 computed_column_expression は決定的です。A computed_column_expression is deterministic when one or more of the following is 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.

共通言語ランタイム (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.

重要

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 および CONVERT」を参照してください。For a list of the date format styles that are deterministic, see CAST and CONVERT.

注意

日付データ型への文字列の暗黙的な変換が必要な式は、データベース互換性レベルが 80 以下に設定されている場合を除いて、非決定的であると見なされます。Expressions that involve implicit conversion of character strings to date data types are considered nondeterministic, unless the database compatibility level is set to 80 or earlier. これは、サーバー セッションの LANGUAGE および DATEFORMAT の設定によって結果が異なるためです。This is because the results depend on the LANGUAGE and DATEFORMAT settings of the server session.

たとえば、式 CONVERT (datetime, '30 listopad 1996', 113) では、言語が異なると文字列 '30 listopad 1996' が異なる月を意味するので、結果が LANGUAGE の設定によって異なります。For example, the results of the expression CONVERT (datetime, '30 listopad 1996', 113) depend on the LANGUAGE setting because the string '30 listopad 1996' means different months in different languages. 同様に、式 DATEADD(mm,3,'2000-12-01')では、 データベース エンジンDatabase Engine により、文字列 '2000-12-01' が DATEFORMAT の設定に基づいて解釈されます。Similarly, in the expression DATEADD(mm,3,'2000-12-01'), the データベース エンジンDatabase Engine interprets the string '2000-12-01' based on the DATEFORMAT setting.

照合順序間で行われる Unicode 以外の文字データの暗黙的な変換も、互換性レベルが 80 以下の場合を除いて、非決定的であると見なされます。Implicit conversion of non-Unicode character data between collations is also 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.

Precision RequirementsPrecision Requirements

computed_column_expression は正確である必要があります。The computed_column_expression must be precise. computed_column_expression は、次の 1 つ以上の条件に該当する場合は正確です。A computed_column_expression is precise when one or more of the following is true:

  • float データ型または real データ型の式ではない。It is not an expression of the float or real data types.
  • 式の定義に float データ型や real データ型を使用していない。It does not use a float or real data type in its definition. たとえば、次のステートメントでは、列 yint 型で決定的ですが、正確ではありません。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);  
    

注意

float 型や real 型の式はすべて不正確であると見なされ、インデックスのキーにできません。つまり、 float 型または real 型の式はインデックス付きビューで使用できますが、キーとしては使用できません。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. float 型や real 型の任意の式が含まれている関数、式、またはユーザー定義関数はすべて不正確であると見なされます。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 RequirementsData Type Requirements

  • 計算列で定義された computed_column_expression は、 textデータ型、 ntextデータ型、または image データ型として評価できません。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 Option RequirementsSET 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 プロパティは、このオプションが ON に設定されているかどうかを示します。The OBJECTPROPERTY function reports whether the option is on through the IsAnsiNullsOn property.
  • インデックスを作成する接続、およびインデックス内の値を変更する INSERT、UPDATE、または DELETE ステートメントを実行するすべての接続では、SET オプションのうち 6 つを ON に設定し、1 つを 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

注意

ANSI_WARNINGS を ON に設定すると、データベース互換性レベルが 90 以上に設定されている場合、暗黙的に 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

決定的でも不正確である式を使用して定義されている計算列が CREATE TABLE ステートメントまたは ALTER TABLE ステートメントで PERSISTED に設定されている場合、計算列にインデックスを作成できます。You can create an index on a computed column that is defined with a deterministic, but imprecise, expression if the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement. その場合、 データベース エンジンDatabase Engine によってテーブルに計算値が格納され、計算列が依存している他の列が更新されるとその計算値も更新されます。This means that the データベース エンジンDatabase Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. データベース エンジンDatabase Engine は、列にインデックスを作成するとき、およびインデックスがクエリで参照されるときに、これらの保存値を使用します。The データベース エンジンDatabase Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query. このオプションにより、特に データベース エンジンDatabase Engine で作成されている CLR 関数など、計算列の式を返す関数が決定的かつ正確であるかどうかを .NET Framework.NET Frameworkで正確に確認できないときに、計算列にインデックスを作成できます。This option enables you to create an index on a computed column when データベース エンジンDatabase 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)