次の方法で共有

  • [アーティクル]

CREATE INDEX (Transact-SQL)

指定のテーブルまたは指定のテーブルのビューに、リレーショナル インデックスを作成します。インデックスはテーブル内にデータがなくても作成できます。データベース名を修飾して指定することにより、他のデータベース内のテーブルまたはビューにリレーショナル インデックスを作成することもできます。

注意注

XML インデックスの作成方法の詳細については、「CREATE XML INDEX (Transact-SQL)」を参照してください。空間インデックスの作成方法の詳細については、「CREATE SPATIAL INDEX (Transact-SQL)」を参照してください。

トピック リンク アイコンTransact-SQL 構文表記規則

構文

Create Relational Index 
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
    table_or_view_name
}

<relational_index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE} 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}

<filter_predicate> ::= 
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

<range> ::= 
<partition_number_expression> TO <partition_number_expression>


Backward Compatible Relational Index
Important   The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] ) 
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ] 
    table_or_view_name
}

<backward_compatible_index_option> ::=
{ 
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE 
  | DROP_EXISTING 
}

引数

  • UNIQUE
    テーブルまたはビューに一意のインデックスを作成します。一意のインデックスとは、どの 2 つの行にも同じインデックス キー値が設定されていないインデックスです。ビューのクラスター化インデックスは一意である必要があります。

    データベース エンジンでは、IGNORE_DUP_KEY が ON に設定されているかどうかに関係なく、重複する値が既に含まれている列に対して一意のインデックスを作成できません。作成しようとすると、データベース エンジンではエラー メッセージが表示されます。1 行または複数行に対して一意のインデックスを作成するには、先に重複する値を削除しておく必要があります。一意のインデックスに使用する列は NOT NULL に設定してください。一意のインデックスを作成するとき、複数の NULL 値は重複した値と見なされます。

  • CLUSTERED
    キー値の論理的順序がテーブル内にある対応する行の物理的な順序を決めるインデックスを作成します。クラスター化インデックスの最下位レベル (リーフ レベル) には、テーブルの実際のデータ行が含まれます。1 つのテーブルまたはビューに、同時に複数のクラスター化インデックスを定義することはできません。詳細については、「クラスタ化インデックスの構造」を参照してください。

    一意のクラスター化インデックスが定義されているビューは、インデックス付きビューと呼ばれます。ビューに一意のクラスター化インデックスを作成すると、ビューを物理的に具体化することになります。ビューにその他のインデックスを定義するには、まずそのビューに一意のクラスター化インデックスを作成する必要があります。詳細については、「インデックス付きビューのデザイン」を参照してください。

    非クラスター化インデックスを作成する前に、クラスター化インデックスを作成します。これは、クラスター化インデックスを作成すると、テーブルの既存の非クラスター化インデックスが再構築されるためです。

    CLUSTERED を指定しない場合、非クラスター化インデックスが作成されます。

    注意注

    クラスター化インデックスのリーフ レベルとデータ ページは定義上同一であるため、クラスター化インデックスの作成で、ON partition_scheme_name または ON filegroup_name 句を使用すると、テーブルを、そのテーブルが作成された元のファイル グループから新しいパーティション構成またはファイル グループに効率的に移動できます。特定のファイル グループ上にテーブルまたはインデックスを作成する前に、使用可能なファイル グループとインデックス用の十分な空領域を確認しておいてください。詳細については、「インデックスに必要なディスク領域の決定」を参照してください。

  • NONCLUSTERED
    テーブルの論理順序を示すインデックスを作成します。非クラスター化インデックスの場合、データ行の物理的な順序は、そのインデックスが作成された順序とは関係ありません。詳細については、「非クラスタ化インデックスの構造」を参照してください。

    インデックスの作成方法に関係なく、PRIMARY KEY および UNIQUE 制約で暗黙的に作成する場合も、CREATE INDEX で明示的に作成する場合も、各テーブルには 999 個までの非クラスター化インデックスを作成できます。

    インデックス付きビューの場合は、既に一意のクラスター化インデックスが作成されているビューにのみ、非クラスター化インデックスを作成できます。

    既定値は NONCLUSTERED です。

  • index_name
    インデックスの名前を指定します。インデックス名は、テーブルまたはビュー内では一意である必要がありますが、データベース内で一意である必要はありません。インデックス名は、識別子の規則に従っている必要があります。

  • column
    インデックスの基準となる 1 列または複数列を指定します。指定した列を組み合わせた値で複合インデックスを作成するには、2 つ以上の列名を指定します。複合インデックスに含まれる列は、table_or_view_name の後のかっこ内に、並べ替えの優先順序に従って指定します。

    16 列までを、1 つの複合インデックス キーとして結合できます。複合インデックス キーに含まれる列はすべて、同じテーブルまたはビュー内に存在する必要があります。複合インデックスの最大許容サイズは、900 バイトです。

    ラージ オブジェクト (LOB) データ型 (ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、xml、または image) の列は、インデックスのキー列として指定できません。また、ntext、text、または image 型の列は、CREATE INDEX ステートメントで参照されていなくても、ビュー定義に含めることはできません。

    バイナリ順序がサポートされる CLR ユーザー定義型列に対してインデックスを作成できます。ユーザー定義型列からメソッドを呼び出すように定義されている計算列にも、そのメソッドが決定的とマークされていて、データ アクセス操作が実行されない限り、インデックスを作成できます。CLR ユーザー定義型列でのインデックス作成の詳細については、「CLR ユーザー定義型の使用」を参照してください。

  • [ ASC | DESC ]
    特定のインデックス列に対して、昇順または降順の並べ替えの方向を指定します。既定値は ASC です。

  • INCLUDE **(**column [ ,...n ] )
    非クラスター化インデックスのリーフ レベルに、非キー列を追加します。非クラスター化インデックスは、一意であっても一意でなくてもかまいません。

    列名は INCLUDE リスト内で繰り返すことはできず、キー列と非キー列両方で同時に使用することはできません。テーブルにクラスター化インデックスが定義されている場合、非クラスター化インデックスには常にクラスター化インデックスの列が含まれます。詳細については、「付加列インデックス」を参照してください。

    text、ntext、および image 型を除く、すべてのデータ型が許可されます。指定する非キー列のいずれか 1 つが varchar(max)、nvarchar(max)、または varbinary(max) 型の場合、インデックスはオフライン (ONLINE = OFF) で作成または再構築する必要があります。

    決定的な計算列は、正確かどうかに関係なく、付加列にすることができます。image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、および xml 型から派生した計算列は、その計算列のデータ型が付加列として許可されている限り、非キーの付加列にできます。詳細については、「計算列に対するインデックスの作成」を参照してください。

    XML インデックスの作成の詳細については、「CREATE XML INDEX (Transact-SQL)」を参照してください。

  • WHERE <filter_predicate>
    含める行を指定して、フィルター選択されたインデックスを作成します。フィルター選択されたインデックスは、テーブル上の非クラスター化インデックスである必要があります。フィルター選択されたインデックスのデータ行のフィルター選択された統計情報を作成します。

    フィルター述語では単純な比較ロジックを使用するため、計算列、UDT 列、空間データ型列、または hierarchyID データ型列を参照することはできません。比較演算子では、NULL リテラルを使用する比較を実行できません。代わりに、IS NULL 演算子と IS NOT NULL 演算子を使用します。

    次に、Production.BillOfMaterials テーブルのフィルター述語の例をいくつか示します。

    WHERE StartDate > '20040101' AND EndDate <= '20040630'

    WHERE ComponentID IN (533, 324, 753)

    WHERE StartDate IN ('20040404', '20040905') AND EndDate IS NOT NULL

    フィルター選択されたインデックスは、XML インデックスおよびフルテキスト インデックスには適用されません。UNIQUE インデックスの場合、一意のインデックス値を持つ必要があるのは選択した行のみです。フィルター選択されたインデックスでは IGNORE_DUP_KEY オプションを使用できません。

  • ON partition_scheme_name**(column_name)**
    ファイル グループが定義されているパーティション構成を指定します。このファイル グループは、パーティション インデックスのパーティションのマップ先となります。CREATE PARTITION SCHEME または ALTER PARTITION SCHEME を実行して、パーティション構成がデータベース内に存在するようにする必要があります。column_name には、パーティション インデックスがパーティション分割される対象の列を指定します。この列は、partition_scheme_name で使用されているパーティション関数の引数のデータ型、長さ、および有効桁数に一致する必要があります。column_name は、インデックス定義で指定されている列に限定されません。UNIQUE インデックスをパーティション分割する場合、column_name は一意のキーとして使用されている列から選択する必要がありますが、それ以外の場合はベース テーブルの任意の列を指定できます。この制限により、データベース エンジンでは、単一のパーティション内だけでキー値の一意性を確認できます。

    注意注

    一意でないクラスター化インデックスをパーティション分割するとき、データベース エンジンでは既定により、まだ指定されていない場合、パーティション分割列がクラスター化インデックス キーのリストに追加されます。一意でない非クラスター化インデックスをパーティション分割するとき、データベース エンジンでは、まだ指定されていない場合、パーティション分割列がインデックスの非キー列 (付加列) として追加されます。

    partition_scheme_name または filegroup が指定されないまま、テーブルがパーティション分割されると、インデックスは基になるテーブルと同じパーティション分割列を使用して、同じパーティション構造に配置されます。

    注意注

    XML インデックスにはパーティション構成を指定できません。ベース テーブルがパーティション分割される場合、XML インデックスではテーブルと同じパーティション構造が使用されます。XML インデックスの作成の詳細については、「CREATE XML INDEX (Transact-SQL)」を参照してください。

    パーティション インデックスの詳細については、「パーティション インデックスの専用ガイドライン」を参照してください。

  • ON filegroup_name
    指定したファイル グループに、指定したインデックスを作成します。位置の指定がなく、テーブルまたはビューがパーティション分割されていない場合、インデックスには、基になるテーブルまたはビューと同じファイル グループが使用されます。ファイル グループは既に存在している必要があります。

  • ON "default"
    既定のファイル グループに、指定したインデックスを作成します。

    この文脈での default という語はキーワードではありません。default は、既定ファイル グループの識別子のため、ON "default" または ON [default] のように区切る必要があります。"default" を指定する場合は、現在のセッションに対して QUOTED_IDENTIFIER オプションが ON である必要があります。これが既定の設定です。詳細については、「SET QUOTED_IDENTIFIER (Transact-SQL)」を参照してください。

  • [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
    クラスター化インデックスの作成時に、テーブルの FILESTREAM データの配置を指定します。FILESTREAM_ON 句を使用すると、異なる FILESTREAM ファイル グループやパーティション構成に FILESTREAM データを移動できます。

    filestream_filegroup_name は、FILESTREAM ファイル グループの名前です。ファイル グループには、CREATE DATABASE ステートメントまたは ALTER DATABASE ステートメントを使用してファイルが 1 つ定義されている必要があります。それ以外の場合は、エラーが発生します。

    テーブルがパーティション分割されている場合、FILESTREAM_ON 句を使用して、テーブルのパーティション構成と同じパーティション関数とパーティション列を使用するように、FILESTREAM ファイル グループのパーティション構成を指定する必要があります。それ以外の場合は、エラーが発生します。

    テーブルがパーティション分割されていない場合、FILESTREAM 列も分割できません。テーブルの FILESTREAM データは、FILESTREAM_ON 句で指定した単一のファイル グループに格納する必要があります。

    クラスター化インデックスの作成で、テーブルに FILESTREAM 列が含まれていないときは、CREATE INDEX ステートメントに FILESTREAM_ON NULL を指定できます。

    FILESTREAM に関するトピックの一覧については、「FILESTREAM ストレージの設計と実装」を参照してください。

<object>::=

インデックスを作成するオブジェクトを、完全修飾または完全修飾ではない形式で指定します。

  • database_name
    データベースの名前を指定します。

  • schema_name
    テーブルまたはビューが属するスキーマの名前を指定します。

  • table_or_view_name
    インデックスを作成するテーブルまたはビューの名前を指定します。

    ビューにインデックスを作成するには、SCHEMABINDING を指定してそのビューを定義する必要があります。ビューに非クラスター化インデックスを作成する前に、そのビューに一意のクラスター化インデックスを作成する必要があります。インデックス付きビューの詳細については、「解説」を参照してください。

<relational_index_option>::=

インデックスを作成するときに使用するオプションを指定します。

  • PAD_INDEX = { ON | OFF }
    インデックスの埋め込みを指定します。既定値は OFF です。

    • ON
      fillfactor で指定される空き領域のパーセンテージが、インデックスの中間レベルのページに適用されます。

    • OFF または fillfactor の指定なし
      中間レベルのページはほぼ全容量が使用されます。ただし、中間ページにあるキーのセットを考慮して、インデックスに割り当てることのできる、少なくとも 1 行の最大サイズが収まる分の領域は残されます。

    PAD_INDEX では FILLFACTOR で指定されるパーセンテージが使用されるので、PAD_INDEX オプションは、FILLFACTOR が指定されている場合にのみ有効です。FILLFACTOR で指定されるパーセンテージで 1 行分のデータを格納できない場合、データベース エンジンでは内部的に、最小サイズを格納できるパーセンテージが使用されます。中間インデックス ページの行数は、fillfactor の値がどれだけ小さくなっても 2 未満にはなりません。

    旧バージョンと互換性のある構文では、WITH PAD_INDEX は WITH PAD_INDEX = ON と同じです。

  • FILLFACTOR **=**fillfactor
    インデックスの作成時や再構築時に、データベース エンジンが各インデックス ページのリーフ レベルをどのくらい使用するかを示すパーセンテージを指定します。fillfactor には 1 ～ 100 の整数値を指定する必要があります。fillfactor が 100 の場合、データベース エンジンでは全容量を使用するリーフ ページでインデックスが作成されます。

    FILLFACTOR 設定は、インデックスが作成または再構築されるときのみ適用されます。データベース エンジンでは、ページ内で指定されたパーセント分の空領域は動的に保持されません。FILLFACTOR 設定を表示するには、sys.indexes カタログ ビューを使用します。

    重要な注意事項重要

    データベース エンジンでは、クラスター化インデックスの作成時にデータが再分配されるため、100 未満の FILLFACTOR 値を使ってクラスター化インデックスを作成すると、データ用のストレージ領域のサイズに影響が生じます。

    詳細については、「FILL FACTOR」を参照してください。

  • SORT_IN_TEMPDB = { ON | OFF }
    tempdb に一時的な並べ替え結果を格納するかどうかを指定します。既定値は OFF です。

    • ON
      インデックス構築に使用される中間の並べ替え結果が tempdb に格納されます。この場合、tempdb がユーザー データベースとは別のディスク セット上に存在すると、インデックスの作成にかかる時間を短縮できますが、インデックスの構築中に使用されるディスク領域のサイズは増加します。

    • OFF
      中間の並べ替え結果はインデックスと同じデータベースに格納されます。

    インデックスを作成するためにユーザー データベース内に必要となる領域の他に、tempdb には、並べ替えの中間結果を格納するためにほぼ同じ大きさの追加領域が必要になります。詳細については、「tempdb とインデックスの作成」を参照してください。

    旧バージョンと互換性のある構文では、WITH SORT_IN_TEMPDB は WITH SORT_IN_TEMPDB = ON と同じです。

  • IGNORE_DUP_KEY = { ON | OFF }
    挿入操作で、一意のインデックスに重複するキー値を挿入しようとした場合のエラー応答を指定します。IGNORE_DUP_KEY オプションは、インデックスが作成または再構築された後の挿入操作のみに適用されます。CREATE INDEX、ALTER INDEX、または UPDATE を実行した場合、このオプションは無効です。既定値は OFF です。

    • ON
      重複したキー値が一意のインデックスに挿入されると、警告メッセージが表示されます。一意性制約に違反する行のみが失敗します。

    • OFF
      重複したキー値が一意のインデックスに挿入されると、エラー メッセージが表示されます。INSERT 操作全体がロールバックされます。

    ビューで作成されるインデックス、一意ではないインデックス、XML インデックス、空間インデックス、およびフィルター選択されたインデックスに対して、IGNORE_DUP_KEY は ON に設定できません。

    IGNORE_DUP_KEY を表示するには、sys.indexes を使用します。

    旧バージョンと互換性のある構文では、WITH IGNORE_DUP_KEY は WITH IGNORE_DUP_KEY = ON と同じです。

  • STATISTICS_NORECOMPUTE = { ON | OFF}
    分布統計を再計算するかどうかを指定します。既定値は OFF です。

    • ON
      古い統計情報は、自動的には再計算されません。

    • OFF
      自動統計更新が有効です。

    自動統計更新を復元するには、STATISTICS_NORECOMPUTE を OFF に設定するか、NORECOMPUTE 句を指定せずに UPDATE STATISTICS を実行します。

    重要な注意事項重要

    分布統計の自動再計算を無効にすると、クエリ オプティマイザーで、テーブルが関与するクエリの最適実行プランが選択されなくなる場合があります。

    旧バージョンと互換性のある構文では、WITH STATISTICS_NORECOMPUTE は WITH STATISTICS_NORECOMPUTE = ON と同じです。

  • DROP_EXISTING = { ON | OFF }
    名前付きの、既存のクラスター化または非クラスター化インデックスを削除して再構築を実行します。既定値は OFF です。

    • ON
      既存のインデックスは削除され、再構築されます。指定するインデックス名は、現在存在するインデックスと同じにする必要がありますが、インデックス定義は変更できます。たとえば、異なる列、並べ替え順、パーティション構成、またはインデックス オプションを指定できます。

    • OFF
      指定するインデックス名が既に存在する場合、エラーが表示されます。

    DROP_EXISTING を使用してインデックスの種類を変更することはできません。

    旧バージョンと互換性のある構文では、WITH DROP_EXISTING は WITH DROP_EXISTING = ON と同じです。

  • ONLINE = { ON | OFF }
    インデックス操作時に、基になるテーブルや関連するインデックスをクエリやデータ変更で使用できるかどうかを指定します。既定値は OFF です。

    注意注

    オンラインでのインデックス操作は、SQL Server Enterprise Edition、Developer Edition、および Evaluation Edition でのみ使用できます。

    • ON
      長期のテーブル ロックは、インデックス操作の間は保持されません。インデックス操作の主要フェーズの期間、ソース テーブルではインテント共有 (IS) ロックのみが保持されます。これにより、基になるテーブルやインデックスに対するクエリや更新を続行できます。操作の開始時、非常に短い時間ですが、ソース オブジェクトの共有 (S) ロックが保持されます。操作の終了時、短い時間ですが、非クラクタ化インデックスが作成される場合は、ソース オブジェクト上で共有 (S) ロックの取得が行われます。また、クラスター化インデックスがオンラインで作成または削除され、クラスター化または非クラスター化インデックスが再構築される場合は、SCH-M (スキーマ修正) ロックが取得されます。インデックスがローカルの一時テーブルに作成される場合は、ONLINE は ON にできません。

    • OFF
      テーブル ロックは、インデックス操作の間適用されます。クラスター化インデックスを作成、再構築、または削除するオフライン インデックス操作や、非クラスター化インデックスを再構築または削除するオフライン インデックス操作では、テーブルのスキーマ修正 (Sch-M) ロックが取得されます。このため、操作中は、すべてのユーザーは基になるテーブルにアクセスできません。非クラスター化インデックスを作成するオフライン インデックス操作では、テーブルの共有 (S) ロックが取得されます。この場合は、基になるテーブルに対して更新は許可されませんが、SELECT ステートメントなどの読み取り操作は許可されます。

    詳細については、「オンライン インデックス操作の動作原理」を参照してください。ロックの詳細については、「ロック モード」を参照してください。

    インデックスは、グローバル一時テーブル上のインデックスを含めてオンラインで作成できます。ただし次のインデックスは例外です。

    • XML インデックス。

    • ローカル一時テーブル上のインデックス。

    • ビュー上の最初の一意のクラスター化インデックス。

    • 無効なクラスター化インデックス。

    • 基になるテーブルに LOB データ型 (image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、および xml 型) が含まれる場合のクラスター化インデックス。

    • LOB データ型列で定義される非クラスター化インデックス。

      注意注

      テーブルに LOB データ型が含まれていても、これらの列がキー列または非キー列 (付加列) としてインデックス定義で使用されていなければ、一意ではない非クラスター化インデックスをオンラインで作成できます。

    詳細については、「オンラインでのインデックス操作の実行」を参照してください。

  • ALLOW_ROW_LOCKS = { ON | OFF }
    行ロックを許可するかどうかを指定します。既定値は ON です。

    • ON
      インデックスにアクセスするとき、行ロックが許可されます。いつ行ロックを使用するかは、データベース エンジンによって決定されます。

    • OFF
      行ロックは使用されません。

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    ページ ロックを許可するかどうかを指定します。既定値は ON です。

    • ON
      ページにアクセスするとき、行ロックが許可されます。いつページ ロックを使用するかは、データベース エンジンによって決定されます。

    • OFF
      ページ ロックは使用されません。

  • MAXDOP = max_degree_of_parallelism
    インデックス操作では、max degree of parallelism 構成オプションを無効にします。並列プランの実行で使用されるプロセッサ数を制限するには、MAXDOP を使用します。最大数は 64 プロセッサです。

    max_degree_of_parallelism には次のデータを指定できます。

    • 1
      並列プランの生成を抑制します。

    • >1
      現在のシステム ワークロードに基づいて、並列インデックス操作で使用される最大プロセッサ数を指定の数以下に制限します。

    • 0 (既定値)
      現在のシステム ワークロードに基づいて、実際の数以下のプロセッサを使用します。

    詳細については、「並列インデックス操作の構成」を参照してください。

    注意注

    並列インデックス操作は、SQL Server Enterprise Edition、Developer Edition、および Evaluation Edition でのみ使用できます。

  • DATA_COMPRESSION
    指定したインデックス、パーティション番号、またはパーティション範囲に、データ圧縮オプションを指定します。オプションは次のとおりです。

    • NONE
      インデックスまたは指定したパーティションが圧縮されません。

    • ROW
      行の圧縮を使用して、インデックスまたは指定したパーティションが圧縮されます。

    • PAGE
      ページの圧縮を使用して、インデックスまたは指定したパーティションが圧縮されます。

    圧縮の詳細については、「圧縮されたテーブルおよびインデックスの作成」を参照してください。

  • ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n] )
    DATA_COMPRESSION 設定を適用するパーティションを指定します。インデックスがパーティション分割されていない場合に ON PARTITIONS 引数を使用すると、エラーが発生します。ON PARTITIONS 句を指定しないと、パーティション インデックスのすべてのパーティションに対して DATA_COMPRESSION オプションが適用されます。

    <partition_number_expression> は以下の方法で指定できます。

    • ON PARTITIONS (2) などのように、1 つのパーティションの番号を指定します。

    • ON PARTITIONS (1, 5) などのように、複数のパーティションのパーティション番号をコンマで区切って指定します。

    • ON PARTITIONS (2, 4, 6 TO 8) などのように、範囲と個別のパーティションの両方を指定します。

    <range> は、ON PARTITIONS (6 TO 8) などのように、パーティション番号を TO で区切って指定できます。

    さまざまなパーティションにさまざまな種類のデータ圧縮を設定するには、次のように DATA_COMPRESSION オプションを複数回指定します。

    REBUILD WITH 
(
DATA_COMPRESSION = NONE ON PARTITIONS (1), 
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), 
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
)

説明

CREATE INDEX ステートメントは、他のクエリと同じように最適化されます。クエリ プロセッサでは I/O 操作を減らすため、テーブル スキャンの代わりに別のインデックスがスキャンされる場合があります。状況によっては、並べ替え操作が行われない場合もあります。SQL Server 2005 Enterprise Edition または SQL Server 2008 を実行しているマルチプロセッサ コンピューターの場合、CREATE INDEX では他のクエリと同様に、インデックス作成に関連するスキャンおよび並べ替え操作を実行するために、より多くのプロセッサを使用することができます。詳細については、「並列インデックス操作の構成」を参照してください。

データベース復旧モデルが一括ログ復旧モデルまたは単純復旧モデルのいずれかに設定されている場合、インデックス作成操作のログへの記録を最小限にできます。詳細については、「インデックス操作の復旧モデルの選択」を参照してください。

一時テーブルにインデックスを作成することもできます。テーブルが削除されるか、セッションが終了すると、インデックスは削除されます。

インデックスでは拡張プロパティがサポートされます。詳細については、「データベース オブジェクトでの拡張プロパティの使用」を参照してください。

クラスター化インデックス

テーブル (ヒープ) にクラスター化インデックスを作成したり、既存のクラスター化インデックスを削除して再作成する場合は、データの並べ替えや、基のテーブルまたは既存のクラスター化インデックス データの一時的コピーを実行するために、データベース内で追加の作業領域が使用可能になっている必要があります。詳細については、「インデックスに必要なディスク領域の決定」を参照してください。クラスター化インデックスの詳細については、「クラスタ化インデックスの作成」を参照してください。

一意のインデックス

一意のインデックスが存在する場合、データベース エンジンは、挿入操作によってデータが追加されるたびに、重複した値がないかをチェックします。重複キー値を生成する挿入操作はロールバックされ、データベース エンジンはエラー メッセージを表示します。挿入操作で多くの行が変更された場合でも、重複が 1 つでもあれば、ロールバックが行われます。IGNORE_DUP_KEY 句が ON に設定されている一意のインデックスにデータを入力しようとすると一意のインデックスに違反する行だけが失敗します。一意のインデックスの詳細については、「一意インデックスの作成」を参照してください。

パーティション インデックス

パーティション インデックスは、パーティション分割されたテーブルと同様の方法で作成および維持されますが、通常のインデックスのように、個別のデータベース オブジェクトとして扱われます。パーティション分割されていないテーブルにパーティション インデックスを作成したり、パーティション分割されているテーブルに非パーティション インデックスを作成することもできます。

パーティション分割されているテーブルにインデックスを作成し、インデックスを配置するファイル グループを指定しない場合、インデックスは基になるテーブルと同じ方法でパーティション分割されます。これは、既定では、インデックスは基になるテーブルと同じファイル グループに配置され、パーティション分割されたテーブルの場合、同じパーティション分割列を使用する同じパーティション構成に配置されるためです。

一意でないクラスター化インデックスをパーティション分割するとき、データベース エンジンは既定では、まだ指定されていないパーティション分割列をクラスター化インデックス キーのリストに追加します。

インデックス付きビューは、テーブルのインデックスと同じ方法でパーティション分割されたテーブルに作成できます。パーティション インデックスの詳細については、「パーティション テーブルとパーティション インデックス」を参照してください。

インデックス付きビュー

ビューに一意のクラスター化インデックスを作成すると、そのビューは、クラスター化インデックスが定義されているテーブルと同じ方法でデータベースに格納されるので、クエリのパフォーマンスが向上します。クエリ オプティマイザーではインデックス付きビューを使って、クエリの実行速度を高めることができます。オプティマイザーでビューを代用するかどうかを判別するために、ビューがクエリで参照されている必要はありません。

次の手順は、インデックス付きビューの作成に必要な手順であり、ビューの正常な実装に不可欠です。

  1. SET オプションが、ビューで参照されるすべての既存のテーブルに対して正しいことを確認します。

  2. 新しいテーブルやビューを作成する前に、そのセッション用の SET オプションが正しく設定されていることを確認します。

  3. ビュー定義が決定的であることを確認します。

  4. WITH SCHEMABINDING オプションを使ってビューを作成します。

  5. ビューに一意のクラスター化インデックスを作成します。

インデックス付きビューに必要な SET オプション

クエリの実行時、異なる SET オプションがアクティブになっている場合、データベース エンジンは同じ式を評価しても異なる結果を生成することがあります。たとえば、SET オプションの CONCAT_NULL_YIELDS_NULL を ON に設定した後、式 'abc' + NULL を実行すると NULL 値が返されますが、CONCAT_NULL_YIELDS_NULL を OFF に設定した後、同じ式を実行すると値 'abc' が返されます。

ビューが正しく維持され、一貫性のある結果が返されるようにするには、インデックス付きビューで、いくつかの SET オプションに固定値が必要となります。固定値の設定が必要な SET オプションと、その値 (必要な値の列を参照) を下の表に示します。この設定は次の条件に該当する場合に常に必要となります。

  • インデックス付きビューが作成されている。

  • インデックス付きビューに関与するテーブルで実行される挿入、更新、または削除操作がある。これには一括コピー、レプリケーション、分散クエリなどの操作も含まれます。

  • クエリ オプティマイザーで、クエリ プランの生成にインデックス付きビューが使用される。

    SET オプション

    必要な値

    既定のサーバー値

    既定の

    OLE DB および ODBC 値

    既定の

    DB-Library 値

    ANSI_NULLS

    ON

    ON

    ON

    OFF

    ANSI_PADDING

    ON

    ON

    ON

    OFF

    ANSI_WARNINGS*

    ON

    ON

    ON

    OFF

    ARITHABORT

    ON

    ON

    OFF

    OFF

    CONCAT_NULL_YIELDS_NULL

    ON

    ON

    ON

    OFF

    NUMERIC_ROUNDABORT

    OFF

    OFF

    OFF

    OFF

    QUOTED_IDENTIFIER

    ON

    ON

    ON

    OFF

    *ANSI_WARNINGS を ON に設定すると、データベース互換性レベルが 90 以上に設定されている場合、暗黙的に ARITHABORT が ON に設定されます。データベース互換性レベルが 80 以下に設定されている場合は、ARITHABORT オプションを明示的に ON に設定する必要があります。

OLE DB または ODBC サーバー接続を使用している場合、変更する必要があるのは ARITHABORT 設定の値だけです。すべての DB-Library 値は、サーバー レベルで sp_configure を使用するか、アプリケーションから SET コマンドを使用して、正しく設定する必要があります。SET オプションの詳細については、「SQL Server でのオプションの使用」を参照してください。

重要な注意事項重要

ARITHABORT ユーザー オプションは、そのサーバーのデータベースで初めてインデックス付きビューまたは計算列のインデックスが作成されたときすぐに、サーバー全体で ON に設定することを強くお勧めします。

決定的な関数

インデックス付きビューの定義は決定的である必要があります。選択リストのすべての式と、WHERE 句および GROUP BY 句が決定的である場合、ビューは決定的であるといえます。決定的な式では、特定の入力値セットで評価するとき常に同じ結果が返されます。決定的な式には、決定的な関数のみを含めることができます。たとえば、DATEADD 関数は、3 つのパラメーターの任意の引数値セットに対して常に同じ結果を返すため、決定的であるといえます。GETDATE は、常に同じ引数で起動されるにもかかわらず、返す値は実行のたびに変化するため、非決定的であるといえます。詳細については、「決定的関数と非決定的関数」を参照してください。

式が決定的でも、浮動小数点式が含まれる場合は、正確な結果はプロセッサのアーキテクチャまたはマイクロコードのバージョンによって異なる可能性があります。データの整合性を確保するため、このような式は、インデックス付きビューの非キー列としてのみ含めることができます。浮動小数点式を含まない決定的な式は、正確な式です。インデックス ビューのキー列と WHERE または GROUP BY 句には、正確で決定的な式だけを含めることができます。

ビュー列が決定的かどうかを判断するには、COLUMNPROPERTY 関数の IsDeterministic プロパティを使用します。スキーマ バインドを含むビューの決定的な列が正確であるかどうかを判断するには、COLUMNPROPERTY 関数の IsPrecise プロパティを使用します。COLUMNPROPERTY では、TRUE の場合は 1、FALSE の場合は 0、有効でない入力に対しては NULL が返されます。これは、列が決定的でないか、正確でないことを表します。

その他の要件

SET オプションと決定的な関数の要件に加えて、次の要件を満たす必要があります。

  • CREATE INDEX を実行するユーザーが、ビューの所有者であること。

  • ビュー定義に GROUP BY 句を指定した場合、一意のクラスター化インデックスのキーでは、GROUP BY 句で指定した列のみを参照できること。

  • テーブルの作成時にベース テーブルに正しい SET オプションが設定されていること。設定されていない場合、スキーマ バインドを含むビューでテーブルを参照できません。

  • ビュー定義では、schema**.**tablename という 2 つの部分から構成される名前でテーブルが参照されていること。

  • ユーザー定義関数は、WITH SCHEMABINDING オプションを使って作成されていること。

  • ユーザー定義関数が、schema**.**function という 2 つの部分から構成される名前で参照されていること。

  • ビューが、WITH SCHEMABINDING オプションを使って作成されていること。

  • ビューが、他のビューを参照せず、同じデータベース内のベース テーブルのみを参照していること。

  • ビュー定義に次のものが含まれないこと。

    COUNT(*)

    ROWSET 関数

    派生テーブル

    自己結合

    DISTINCT

    STDEV、VARIANCE、AVG

    float*、text、ntext、または image 列

    サブクエリ

    フルテキスト述語 (CONTAIN、FREETEXT)

    NULL 値を許容する式での SUM

    CLR ユーザー定義集計関数

    TOP

    MIN、MAX

    UNION

    *インデックス付きビューには float 列を含めることができますが、このような列はクラスター化インデックス キーには含めることができません。

GROUP BY が存在する場合、VIEW 定義には COUNT_BIG(*) を含める必要があります。HAVING を含めることはできません。このような GROUP BY 制限は、インデックス付きビュー定義にのみ適用されます。クエリがこの GROUP BY 制限を満たしていない場合でも、実行プランでインデックス付きビューを使用することはできます。

インデックス付きビューはパーティション分割されたテーブルに作成でき、インデックス付きビュー自体をパーティション分割できます。パーティション分割の詳細については、前の「パーティション インデックス」を参照してください。

データベース エンジンでインデックス付きビューが使用されないようにするには、クエリに OPTION (EXPAND VIEWS) ヒントを含めます。これによって、オプションの 1 つが正しく設定されていない場合、オプティマイザーもビューのインデックスを使用できません。OPTION (EXPAND VIEWS) ヒントの詳細については、「SELECT (Transact-SQL)」を参照してください。

データベースの互換性レベルは 80 未満にできません。インデックス付きビューを含むデータベースは、80 より低い互換性レベルには変更できません。

フィルター選択されたインデックス

フィルター選択されたインデックスは、最適化された非クラスター化インデックスであり、テーブルから選択する行の少ないクエリに適しています。フィルター選択されたインデックスは、フィルター述語を使用してテーブル内の一部のデータにインデックスを作成します。フィルター選択されたインデックスを適切に設計すると、クエリのパフォーマンスを向上させ、ストレージ コストとメンテナンス コストを削減することができます。

フィルター選択されたインデックスに必要な SET オプション

次の条件のいずれかに該当する場合、Required Value 列の SET オプションが必要となります。

  • フィルター選択されたインデックスを作成するとき。

  • INSERT、UPDATE、DELETE、MERGE のいずれかの操作で、フィルター選択されたインデックスのデータを変更するとき。

  • クエリ オプティマイザーが、クエリ実行プラン内のフィルター選択されたインデックスを使用するとき。

    SET オプション

    必要な値

    ANSI_NULLS

    ON

    ANSI_PADDING

    ON

    ANSI_WARNINGS*

    ON

    ARITHABORT

    ON

    CONCAT_NULL_YIELDS_NULL

    ON

    NUMERIC_ROUNDABORT

    OFF

    QUOTED_IDENTIFIER

    ON

    *ANSI_WARNINGS を ON に設定すると、データベース互換性レベルが 90 以上に設定されている場合、暗黙的に ARITHABORT が ON に設定されます。データベース互換性レベルが 80 以下に設定されている場合は、ARITHABORT オプションを明示的に ON に設定する必要があります。

SET オプションが正しくないと、次の状態が発生する場合があります。

  • フィルター選択されたインデックスが作成されません。

  • データベース エンジンによりエラーが生成され、インデックスのデータを変更していた INSERT ステートメント、UPDATE ステートメント、DELETE ステートメント、または MERGE ステートメントがロールバックされます。

  • Transact-SQL ステートメントの実行プランで、クエリ オプティマイザーがインデックスを無視します。

フィルター選択されたインデックスの詳細については、「フィルター選択されたインデックスのデザイン ガイドライン」を参照してください。

空間インデックス

空間インデックスの詳細については、「CREATE SPATIAL INDEX (Transact-SQL)」および「空間インデックスの使用 (データベース エンジン)」を参照してください。

XML インデックス

XML インデックスの詳細については、「CREATE XML INDEX (Transact-SQL)」および「XML データ型の列のインデックス」を参照してください。

インデックス キーのサイズ

インデックス キーの最大サイズは 900 バイトです。900 バイトを超える varchar 列へのインデックスは、インデックス作成時にその列の既存のデータが 900 バイトを超えていなければ作成できます。ただし、後続の挿入や更新操作によってその列の合計サイズが 900 バイトを超えると、その操作は失敗します。詳細については、「インデックス キーの最大サイズ」を参照してください。クラスター化インデックスのインデックス キーには、ROW_OVERFLOW_DATA アロケーション ユニットにデータを持つ varchar 列を含めることはできません。クラスター化インデックスが varchar 列に作成され、その列の既存データが IN_ROW_DATA アロケーション ユニットにある場合、それ以後、既存データを行外に押し出すことになるような挿入や更新操作は失敗します。アロケーション ユニットの詳細については、「テーブルとインデックスの編成」を参照してください。

非クラスター化インデックスのリーフ レベルに非キー列を含めることができます。インデックス キー サイズを計算するとき、データベース エンジンではこれらの列は考慮されません。詳細については、「付加列インデックス」を参照してください。

注意注

テーブルがパーティション分割される際に、一意でないクラスター化インデックスにパーティション分割キー列がまだ存在しない場合は、それらの列がデータベース エンジンによってインデックスに追加されます。一意でないクラスター化インデックスでは、インデックス付き列の合計サイズ (付加列はカウントしません) と、追加されたパーティション分割列のサイズとを足した合計が 1,800 バイトを超えないようにしてください。

計算列

インデックスを計算列に作成できます。また、計算列にプロパティ PERSISTED を設定することができます。その場合、データベース エンジンによってテーブルに計算値が格納され、計算列が依存している他の列が更新されるとその計算値も更新されます。データベース エンジンは、列にインデックスを作成するとき、およびインデックスがクエリで参照されるときに、これらの保存値を使用します。

計算列のインデックスを作成するには、計算列が決定的で正確である必要があります。ただし、PERSISTED プロパティを使用した場合、インデックス作成が可能となる計算列の種類は、次のようになります。

  • Transact-SQL、CLR 関数、およびユーザーによって決定的とマークされた CLR ユーザー定義型メソッドに基づく計算列

  • データベース エンジンの定義によると決定的であるが、正確でない式に基づく計算列

保存される計算列に対しては、前の「インデックス付きビューに必要な SET オプション」で示すように、次の SET オプションを設定する必要があります。

インデックス作成の条件をすべて満たしている限り、UNIQUE または PRIMARY KEY 制約があっても計算列を含めることができます。この計算列は、決定的かつ正確であるか、決定的かつ持続可能である必要があります。決定性の詳細については、「決定的関数と非決定的関数」を参照してください。

image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、および xml 型から派生した計算列は、計算列のデータ型をインデックス キー列または非キー列として使用できる限り、キー列または非キー列としてインデックスを設定できます。たとえば、xml 計算列にはプライマリ XML インデックスを作成できません。インデックス サイズが 900 バイトを超える場合、警告メッセージが表示されます。

計算列にインデックスを作成すると、以前は機能していた挿入または更新の操作が失敗することがあります。このような失敗は、計算列の結果が算術エラーになる場合に発生する可能性があります。たとえば、次のテーブルでは、計算列 c は計算エラーになりますが、INSERT ステートメントは正常に実行されます。

CREATE TABLE t1 (a int, b int, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

これに対し、テーブルの作成後に計算列 c にインデックスを作成すると、同じ INSERT ステートメントは失敗します。

CREATE TABLE t1 (a int, b int, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

詳細については、「計算列に対するインデックスの作成」を参照してください。

インデックスの付加列

付加列と呼ばれる非キー列は、非クラスター化インデックスのリーフ レベルに追加でき、クエリに対応することによりクエリ パフォーマンスを向上できます。この場合、クエリで参照されるすべての列は、キー列または非キー列としてインデックスに含まれます。これにより、クエリ オプティマイザーではテーブルまたはクラスター化インデックス データにアクセスすることなく、インデックス スキャンによって必要な情報をすべて特定できます。詳細については、「付加列インデックス」を参照してください。

インデックス オプションの指定

SQL Server 2005 では新しいインデックス オプションが導入され、オプションの指定方法も変更になりました。旧バージョンと互換性のある構文では、WITH option_name は WITH ( <option_name> = ON ) と同じです。インデックス オプションを設定する場合は、次の規則が適用されます。

  • 新しいインデックス オプションは、WITH (option_name= ON | OFF**)** を使用してのみ指定できる。

  • 同じステートメントで、旧バージョンとの互換性がある構文と新しい構文の両方を使ってオプションを指定することはできない。たとえば、WITH (DROP_EXISTING, ONLINE = ON**)** を指定すると、ステートメントは失敗します。

  • XML インデックスを作成するとき、オプションは WITH (option_name= ON | OFF**)** を使用して指定する必要がある。

DROP_EXISTING 句

DROP_EXISTING 句を使用して、インデックスの再構築、列の追加または削除、オプションの変更、列の並べ替え順の変更、パーティション構成またはファイル グループの変更を行うことができます。

インデックスに PRIMARY KEY または UNIQUE 制約が設定されていて、インデックス定義が変更されることがない場合は、既存の制約を保持したままインデックスが削除され再作成されます。ただし、インデックス定義が変更されると、ステートメントは失敗します。PRIMARY KEY または UNIQUE 制約の定義を変更するには、制約を削除し、新しい定義で制約を追加します。

DROP_EXISTING を使用すると、非クラスター化インデックスが定義されているテーブル上で、同じまたは異なるキー セットのクラスター化インデックスを再作成するときのパフォーマンスを向上できます。DROP_EXISTING では、古いクラスター化インデックスに DROP INDEX ステートメントを実行した後、新しいクラスター化インデックスに CREATE INDEX ステートメントを実行するという操作を一度に実行できます。非クラスター化インデックスは一度だけ再構築され、その後はインデックス定義が変更された場合のみ再構築されます。インデックス定義に元のインデックスと同じインデックス名、キーおよびパーティション列、一意性属性、および並べ替え順がある場合、DROP_EXISTING 句で非クラスター化インデックスを再構築できません。

非クラスター化インデックスが再構築されるかどうかに関係なく、非クラスター化インデックスは元のファイル グループまたはパーティション構成に常に属したままになり、元のパーティション関数を使用します。クラスター化インデックスが他のファイル グループまたはパーティション構成に再構築される場合、非クラスター化インデックスはクラスター化インデックスの新しい位置に移動されません。したがって、以前に非クラスター化インデックスがクラスター化インデックスに対応した位置にあっても、再構築後は別の位置になる可能性があります。パーティション インデックスの位置合わせの詳細については、「パーティション インデックスの専用ガイドライン」を参照してください。

インデックス ステートメントで非クラスター化インデックスが指定され、かつ ONLINE オプションが OFF に設定されている場合を除き、同じインデックス キー列が同じ順序 (昇順または降順も同じ) で使用される場合、DROP_EXISTING 句では再度データの並べ替えは行われません。クラスター化インデックスが無効になっている場合、CREATE INDEX WITH DROP_EXISTING 操作は ONLINE が OFF に設定された状態で実行する必要があります。非クラスター化インデックスが無効で、無効なクラスター化インデックスと関連がない場合、CREATE INDEX WITH DROP_EXISTING 操作は、ONLINE が OFF または ON に設定された状態で実行できます。

128 以上のエクステントがあるインデックスを削除または再構築するとき、データベース エンジンは、トランザクションがコミットされるまで実際のページの割り当て解除とそれに関連するロックを延期します。詳細については、「ラージ オブジェクトの削除と再構築」を参照してください。

ONLINE オプション

インデックス操作をオンラインで実行する場合は、次のガイドラインが適用されます。

  • オンライン インデックス操作の実行中、基になるテーブルは変更、切り捨て、削除できない。

  • インデックス操作中は、追加の一時ディスク領域が必要。詳細については、「インデックスに必要なディスク領域の決定」を参照してください。

  • オンライン操作は、パーティション インデックスや、保存される計算列を含むインデックス、または付加列で実行できる。

詳細については、「オンラインでのインデックス操作の実行」を参照してください。

行およびページ ロック オプション

ALLOW_ROW_LOCKS = ON かつ ALLOW_PAGE_LOCK = ON の場合は、インデックスにアクセスするときに、行、ページ、およびテーブル レベルのロックが許可されます。データベース エンジンは適切なロックを選択し、行ロックまたはページ ロックをテーブル ロックにエスカレートすることができます。詳細については、「ロックのエスカレーション (データベース エンジン)」を参照してください。

ALLOW_ROW_LOCKS = OFF かつ ALLOW_PAGE_LOCK = OFF の場合は、インデックスにアクセスするときに、テーブル レベルのロックのみが許可されます。

インデックスのロックの粒度の構成方法の詳細については、「インデックスのロックのカスタマイズ」を参照してください。

インデックス情報の表示

インデックスに関する情報を返すには、カタログ ビュー、システム関数、およびシステム ストアド プロシージャを使用できます。詳細については、「インデックス情報の表示」を参照してください。

データの圧縮

データの圧縮については、「圧縮されたテーブルおよびインデックスの作成」に記載されています。特に次の点に注意してください。

  • 圧縮を使用すると、ページに格納できる行数が増えますが、最大行サイズは変更されません。

  • インデックスの非リーフ ページでは、ページの圧縮は行われませんが、行の圧縮は可能です。

  • 非クラスター化インデックスにはそれぞれ個別の圧縮設定があり、基になるテーブルの圧縮設定は継承されません。

  • ヒープにクラスター化インデックスを作成する場合、圧縮状態を特に指定しない限り、ヒープの圧縮状態がクラスター化インデックスに継承されます。

パーティション インデックスには次の制限が適用されます。

  • 固定されていないインデックスがテーブルにある場合、そのパーティションの圧縮設定を変更できません。

  • ALTER INDEX <index> ...REBUILD PARTITION ... 構文は、そのインデックスの指定のパーティションを再構築します。

  • ALTER INDEX <index> ...REBUILD WITH ... 構文は、そのインデックスのすべてのパーティションを再構築します。

圧縮状態の変更による、テーブル、インデックス、またはパーティションへの影響を評価するには、sp_estimate_data_compression_savings ストアド プロシージャを使用します。

権限

テーブルまたはビューに対する ALTER 権限が必要です。実行するには、固定サーバー ロール sysadmin または、固定データベース ロール db_ddladmin および db_owner のメンバーである必要があります。

例

A. 単純な非クラスター化インデックスを作成する

次の例では、Purchasing.ProductVendor テーブルの BusinessEntityID 列に非クラスター化インデックスを作成します。

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor (BusinessEntityID); 
GO

B. 単純な非クラスター化複合インデックスを作成する

次の例では、Sales.SalesPerson テーブルの SalesQuota 列および SalesYTD 列に非クラスター化複合インデックスを作成します。

USE AdventureWorks2008R2
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
    DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO

C. 一意の非クラスター化インデックスを作成する

次の例では、Production.UnitMeasure テーブルの Name 列に一意の非クラスター化インデックスを作成します。このインデックスでは、Name 列に挿入されるデータが一意である必要があります。

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'AK_UnitMeasure_Name')
    DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name 
    ON Production.UnitMeasure(Name);
GO

次のクエリでは、既存の行と同じ値の行を挿入することによって、一意性の制約をテストします。

--Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
    VALUES ('OC', 'Ounces', GetDate());

結果のエラー メッセージは次のようになります。

Server: Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

D. IGNORE_DUP_KEY オプションを使用する

次の例では、最初に IGNORE_DUP_KEY オプションを ON に設定し、次にこのオプションを OFF に設定して、複数の行を一時テーブルに挿入したときのこのオプションの影響を検証します。2 番目の複数行の INSERT ステートメントを実行するときには、#Test テーブルに、重複する値となる 1 行を意図的に挿入します。テーブル内の行数としては、挿入された行数が返されます。

USE AdventureWorks2008R2;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

次は 2 番目の INSERT ステートメントの結果です。

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows

--------------

38

一意性の制約に違反していない Production.UnitMeasure テーブルからの行は、正常に挿入されています。ここでは警告が発行され、重複する行が無視されましたが、トランザクション全体はロールバックされていません。

次に、IGNORE_DUP_KEY を OFF に設定して同じステートメントを実行します。

USE AdventureWorks2008R2;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = OFF);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

次は 2 番目の INSERT ステートメントの結果です。

Server: Msg 2601, Level 14, State 1, Line 5

Cannot insert duplicate key row in object '#Test' with unique index

'AK_Index'. The statement has been terminated.

Number of rows

--------------

1

ここでは、Production.UnitMeasure テーブルで UNIQUE インデックス制約に違反した行は 1 行だけでしたが、このテーブルから行は挿入されませんでした。

E. DROP_EXISTING を使ってインデックスを削除し再作成する

次の例では、DROP_EXISTING オプションを使って、Production.WorkOrder テーブルの ProductID 列にある既存のインデックスを削除して再作成します。ここではオプション FILLFACTOR および PAD_INDEX も設定されています。

USE AdventureWorks2008R2;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

F. ビューにインデックスを作成する

次の例では、ビューとそのビューのインデックスを作成します。ここでは、インデックス付きビューを使用する 2 つのクエリを実行します。

USE AdventureWorks2008R2;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
    WHERE od.SalesOrderID = o.SalesOrderID
    GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is 
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
    OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

G. 非キー列 (付加列) を使用してインデックスを作成する

次の例では、1 つのキー列 (PostalCode) と 4 つの非キー列 (AddressLine1、AddressLine2、City、StateProvinceID) を使って非クラクタ化インデックスを作成します。次に、そのインデックスが対応するクエリを実行します。クエリ オプティマイザーによって選択されるインデックスを SQL Server Management Studio の [クエリ] メニューに表示するには、クエリを実行する前に [実際の実行プランを含める] を選択します。

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_Address_PostalCode')
    DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
    ON Person.Address (PostalCode)
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

H. パーティション インデックスを作成する

次の例では、既存のパーティション構成 TransactionsPS1 に非クラスター化パーティション インデックスを作成します。この例では、パーティション インデックスのサンプルがインストールされていることを前提としています。

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'IX_TransactionHistory_ReferenceOrderID'
    AND object_id = OBJECT_ID(N'Production.TransactionHistory'))
DROP INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory (ReferenceOrderID)
    ON TransactionsPS1 (TransactionDate);
GO

I. フィルター選択されたインデックスを作成する

次の例では、フィルター選択されたインデックスを Production.BillOfMaterials テーブルに作成します。フィルター述語では、フィルター選択されたインデックスに非キー列を含めることができます。この例の述語では、EndDate が NULL 以外の行だけを選択します。

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO

J. 圧縮されたインデックスを作成する

次の例では、行の圧縮を使用して、非パーティション テーブルのインデックスを作成します。

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

次の例では、インデックスのすべてのパーティションに行の圧縮を使用して、パーティション テーブルのインデックスを作成します。

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

次の例では、インデックスのパーティション 1 にページの圧縮を、パーティション 2 から 4 までに行の圧縮を使用して、パーティション テーブルのインデックスを作成します。

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4 ) ) ;
GO