CREATE INDEX (Transact-SQL)

適用対象:SQL ServerAzure SQL データベースAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

テーブルまたはビューにリレーショナル インデックスを作成します。 クラスター化または非クラスター化 B ツリー インデックスであるため、行ストア インデックスとも呼ばれます。 行ストア インデックスは、テーブルにデータが設定される前に作成することができます。 クエリが特定の列から選択するか、特定の順序での値の並べ替えを要求する場合は特に、クエリのパフォーマンスを向上させるために行ストア インデックスを使用します。

Note

ドキュメントでは、一般的にインデックスに関して B ツリーという用語が使用されます。 行ストア インデックスで、データベース エンジンによって B+ ツリーが実装されます。 これは、列ストア インデックスやメモリ最適化テーブルのインデックスには適用されません。 詳細については、「SQL Server と Azure SQL のインデックスのアーキテクチャとデザイン ガイド」を参照してください。

Azure Synapse Analytics および Analytics Platform System (PDW) では、現在 UNIQUE 制約はサポートされません。 一意の制約を参照する例は、SQL Server、Azure SQL Database、および Azure SQL Managed Instance にのみ適用できます。

インデックスの設計ガイドラインについては、 SQL Server インデックス設計ガイドを参照してください。

例:

1. テーブルまたはビューに非クラスター化インデックスを作成する

   CREATE INDEX index1 ON schema1.table1 (column1);
   

2. テーブルにクラスター化インデックスを作成し、テーブルに 3 部構成の名前を使用する

   CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
   

3. UNIQUE 制約を持つ非クラスター化インデックスを作成し、並べ替え順序を指定する

   CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);
   

重要なシナリオ:

SQL Server 2016 (13.x) 以降、Azure SQL Database および Azure SQL Managed Instance では、列ストア インデックスに非クラスター化インデックスを使用して、データ ウェアハウス クエリのパフォーマンスを向上させることができます。 詳細については、「 列ストア インデックス - データ ウェアハウス」を参照してください。

その他の種類のインデックスについては、以下を参照してください。

• CREATE XML INDEX
• CREATE SPATIAL INDEX
• CREATE COLUMNSTORE INDEX

Transact-SQL 構文表記規則

構文

SQL Server、Azure SQL Database、Azure SQL Managed Instance の構文

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.table_or_view_name | schema_name.table_or_view_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 }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [MINUTES]
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
}

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

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

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

<comparison> ::=
        column_name <comparison_op> constant

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

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

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

下位互換性のあるリレーショナル インデックス

重要

下位互換性のあるリレーショナル インデックスの構文構造は、今後のバージョンの SQL Server では削除される予定です。 新しい開発作業ではこの構文構造の使用を避け、現在この構造を使用しているアプリケーションの変更を検討してください。 代わりに <relational_index_option> に指定されている構文構造を使用します。

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
}

Azure Synapse Analytics と Parallel Data Warehouse の構文

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
    [ORDER (column[,...n])]
    [WITH ( DROP_EXISTING = { ON | OFF } )]
[;]


CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]

引数

UNIQUE

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

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

UNIQUE制約は、NULLを値として扱います。 列が null 許容で、列に UNIQUE 制約が存在する場合は、 NULL を持つ最大 1 つの行が許可されます。

CLUSTERED

インデックス キー列に指定された並べ替え順序によって、ディスク上のインデックス構造のページ順序が決まるインデックスを作成します。 クラスター化インデックスの下部 (リーフ) レベルのページの行には、常にテーブルのすべての列が含まれます。 インデックスの上位レベルのページの行には、キー列のみが含まれます。

テーブルに含めることができるのは、1 つのクラスター化インデックスのみです。 クラスター化インデックスがテーブルに存在する場合は、テーブル内のすべてのデータが含まれます。 クラスター化インデックスのないテーブルは、ヒープと呼ばれます。

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

非クラスター化インデックスを作成する前に、クラスター化インデックスを作成します。 テーブル上の既存の非クラスター化インデックスは、クラスター化インデックスの作成時に再構築されます。これは、テーブルが大きい場合にリソースを大量に消費する操作です。

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

Note

クラスター化インデックスにはテーブル内のすべてのデータが含まれているため、クラスター化インデックスを作成し、 ON partition_scheme_name 句または ON filegroup_name 句を使用すると、テーブルが作成されたファイル グループから新しいパーティション構成またはファイル グループに効率的に移動されます。 特定のファイル グループ上にテーブルまたはインデックスを作成する前に、使用可能なファイル グループとインデックス用の十分な空領域を確認しておいてください。

場合によっては、クラスター化インデックスを作成することで、以前に無効にしたインデックスを有効にできます。 詳細については、「インデックスと制約を有効にする」および「インデックスと制約を無効にする」を参照してください。

NONCLUSTERED

インデックス キー列に指定された並べ替え順序によって、ディスク上のインデックス構造のページ順序が決まるインデックスを作成します。 クラスター化インデックスとは異なり、非クラスター化インデックスのリーフ レベルのページの行には、インデックス キー列のみが含まれます。 必要に応じて、 INCLUDE 句を使用して、キー以外の列のサブセットを含めることができます。

各テーブルは、インデックスの作成方法に関係なく、最大 999 個の非クラスター化インデックスを持つことができます。 PRIMARY KEY 制約と UNIQUE 制約を使用して暗黙的に行うか、 CREATE INDEXで明示的に指定します。

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

特に指定しない場合、既定のインデックスの種類は NONCLUSTERED になります。

index_name

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

column

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

1 つの複合インデックス キーには、最大 32 の列を結合できます。 複合インデックス キーに含まれる列はすべて、同じテーブルまたはビュー内に存在する必要があります。 複合インデックスの値の最大許容サイズは、クラスター化インデックスの場合は、900 バイトまたは非クラスター化インデックスの 1,700 です。 SQL Database および SQL Server 2016 (13.x) 以前のバージョンの場合、制限は列数が 16、サイズが 900 バイトになります。

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

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

[ ASC | DESC ]

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

INCLUDE (column [ ,... n ] )

非クラスター化インデックスのリーフ レベルに追加するキー以外の列を指定します。 非クラスター化インデックスは、一意であっても一意でなくてもかまいません。

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

text、 ntext、および imageを除く、すべてのデータ型を使用できます。 SQL Server 2012 (11.x) 以降、Azure SQL Database、および Azure SQL Managed Instance で、指定されたキー以外の列のいずれかが varchar(max)、 nvarchar(max)、または varbinary(max) データ型の場合は、 ONLINE オプションを使用してインデックスを構築または再構築できます。

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

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

WHERE <filter_predicate>

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

フィルター述語は単純な比較ロジックを使用し、計算列、ユーザー定義データ型 (UDT) 列、空間データ型列、または hierarchyid データ型列を参照することはできません。 比較演算子を使用した NULL リテラルとの比較は許可されません。 代わりに、IS NULL および IS NOT NULL 演算子を使用します。

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

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') 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 は、インデックス定義で指定されている列に限定されません。 一意のインデックスをパーティション分割する場合を除き、ベース テーブル内の任意の列を指定 column_name、一 意キーとして使用されるものの中から選択する必要があります。 この制限により、データベース エンジンでは、単一のパーティション内だけでキー値の一意性を確認できます。

Note

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

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

Note

XML インデックスにはパーティション構成を指定できません。 ベース テーブルがパーティション分割される場合、XML インデックスではテーブルと同じパーティション構造が使用されます。

パーティション インデックス、パーティション テーブル、およびインデックスのパーティション分割について詳しくは、以下 をご覧ください。

ON filegroup_name

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

ON [既定値]

テーブルまたはビューと同じファイルグループまたはパーティション スキームに対して、指定されたインデックスを作成します。

このコンテキストでは、 defaultという用語はキーワードではありません。 これはテーブルまたはビューのファイル グループまたはパーティション分割されたスキームの識別子であり、 ON "default" や ON [default]のように区切る必要があります。 "default"を指定する場合は、現在のセッションに対して QUOTED_IDENTIFIER オプションをONする必要があります。 これが既定の設定です。 詳しくは、「SET QUOTED_IDENTIFIER」をご覧ください。

Note

CREATE INDEXのコンテキストでは、"default"と[default]はデータベースの既定のファイル グループを示していません。 ベース テーブルまたはビューで使用されるファイル グループまたはパーティション構成を示します。 これは、データベースの既定のファイル グループにテーブルを配置"default"[default]CREATE TABLEとは異なります。

[ 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 列が含まれていないときは、FILESTREAM_ON NULL ステートメントに CREATE INDEX を指定できます。

詳細については、「 FILESTREAM (SQL Server)」を参照してください。

<object>::=

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

database_name

データベースの名前。

schema_name

テーブルまたはビューが属するスキーマの名前です。

table_or_view_name

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

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

SQL Server 2016 (13.x) 以降では、オブジェクトをクラスター化列ストア インデックスに格納されたテーブルに指定できます。

Azure SQL Database では、<database_name>が現在のデータベース名である場合、または<database_name>がtempdbされ、#または##で始まる<object_name>、3 部構成の名前形式<database_name>.<schema_name>.<object_name>がサポートされます。 スキーマ名が dboの場合は、 <schema_name> を省略できます。

<relational_index_option>::=

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

PAD_INDEX = { ON | OFF }

インデックスの埋め込みを指定します。 既定値は OFFです。

• ON

  塗りつぶし係数で指定された空き領域の割合は、インデックスの中間レベルのページに適用されます。 FILLFACTORがONに設定PAD_INDEX同時に指定されていない場合は、sys.indexes の fill factor 値が使用されます。

• OFF

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

PAD_INDEX オプションは、FILLFACTORが指定されている場合にのみ役立ちます。PAD_INDEXは、FILLFACTORで指定された割合を使用するためです。 FILLFACTORに指定されたパーセンテージが 1 行に対して十分な大きさでない場合、データベース エンジンは内部的にパーセンテージをオーバーライドして最小値を許可します。 中間インデックス ページの行数は、 FILLFACTORの値がどれだけ小さいかに関係なく、2 行を超えることはありません。

下位互換性のある構文では、WITH PAD_INDEX は WITH PAD_INDEX = ON と等価です。

FILLFACTOR = fillfactor

インデックスの作成時または再構築時に、データベース エンジン が各インデックス ページのリーフ レベルをどの程度まで埋めるかを、パーセント値で指定します。 fillfactor 値は、1 から 100 までの整数値である必要があります。 FILL FACTOR 値 0 と 100 の機能は、まったく同じです。 fillfactor が 100 の場合、データベース エンジン では全容量を使用するリーフ ページでインデックスが作成されます。

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

塗りつぶし係数の設定を表示するには、sys.indexes カタログ ビューのfill_factor列を使用します。

重要

FILLFACTORが 100 未満のインデックスを作成すると、データベース エンジンがインデックスの作成時または再構築時にフィル ファクターに従ってデータを再配布するため、データが占有するストレージ領域の量が増えます。

詳細については、「インデックスの 塗りつぶし係数を指定する」を参照してください。

SORT_IN_TEMPDB = { ON | OFF }

一時的な並べ替え結果を tempdbに格納するかどうかを指定します。 既定値は、Azure SQL Database Hyperscale を除く OFF です。 Hyperscale のすべてのインデックス作成操作では、再開可能なインデックス ビルドが使用されない限り、 SORT_IN_TEMPDB は常に ON されます。 再開可能なインデックス ビルドの場合、 SORT_IN_TEMPDB は常に OFF。

• ON

  インデックスの作成に使用される中間の並べ替え結果は、 tempdbに格納されます。 これにより、インデックスの作成に必要な時間が短縮される可能性があります。 インデックスの構築中に使用されるディスク領域のサイズは増加します。

• OFF

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

ユーザー データベースでインデックスを作成するために必要な領域に加えて、 tempdb は、中間の並べ替え結果を保持するために、約同じ量の領域を追加する必要があります。 詳細については、「 インデックスのSORT_IN_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ステートメント全体がロールバックされます。

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

インデックスのIGNORE_DUP_KEY設定を表示するには、sys.indexes カタログ ビューの ignore_dup_key 列を使用します。

下位互換性のある構文では、WITH IGNORE_DUP_KEY は WITH IGNORE_DUP_KEY = ON と等価です。

STATISTICS_NORECOMPUTE = { ON | OFF}

統計を再計算するかどうかを指定します。 既定値は OFFです。

• ON

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

• OFF

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

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

警告

STATISTICS_NORECOMPUTE = ON設定して統計の自動再計算を無効にすると、クエリ オプティマイザーがテーブルに関連するクエリの最適な実行プランを選択できなくなる可能性があります。

STATISTICS_NORECOMPUTEを ON に設定しても、インデックスの再構築操作中に発生するインデックス統計の更新は防止されません。

下位互換性のある構文では、WITH STATISTICS_NORECOMPUTE は WITH STATISTICS_NORECOMPUTE = ON と等価です。

STATISTICS_INCREMENTAL = { ON | OFF }

適用対象: SQL Server 2014 (12.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance

ONすると、作成される統計はパーティションごとの統計になります。 OFFすると、統計ツリーが削除され、SQL Server によって統計が再計算されます。 既定値は OFFです。

パーティションごとの統計がサポートされていない場合、このオプションは無視され、警告が生成されます。 次の場合、増分統計はサポートされません。

• ベース テーブルにパーティションで固定されていないインデックスを使用して作成された統計。
• Always On の読み取り可能なセカンダリ データベースに対して作成された統計。
• 読み取り専用のデータベースに対して作成された統計。
• フィルター選択されたインデックスに対して作成された統計。
• ビューに対して作成された統計。
• 内部テーブルに対して作成された統計。
• 空間インデックスまたは XML インデックスを使用して作成された統計。

DROP_EXISTING = { ON | OFF }

列の仕様が変更された既存のクラスター化または非クラスター化インデックスを削除して再構築し、インデックスを同じ名前のままにするオプションです。 既定値は OFFです。

• ON

  既存のインデックスを削除して再構築することを指定します。これはパラメーター index_name と同じ名前である必要があります。

• OFF

  既存のインデックスを削除および再構築しないことを指定します。 指定するインデックス名が既に存在する場合、SQL Server はエラーを表示します。

DROP_EXISTING では、以下を変更することができます。

• 非クラスター化行ストア インデックスからクラスター化行ストア インデックスへの変更。

DROP_EXISTING を使用すると、次の変更を行うことはできません。

• クラスター化行ストア インデックスから非クラスター化行ストア インデックスへの変更。
• クラスター化列ストア インデックスから任意の種類の行ストア インデックスへの変更。

下位互換性のある構文では、WITH DROP_EXISTING は WITH DROP_EXISTING = ON と等価です。

ONLINE = { ON | OFF }

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

重要

オンラインでのインデックス操作は、Microsoft SQL Server のすべてのエディションで使用できるわけではありません。 SQL Server の各エディションでサポートされる機能の一覧については、「SQL Server 2022 の各エディションとサポートされている機能」を参照してください。

• ON

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

  Note

  WAIT_AT_LOW_PRIORITY オプションを使用すると、オンライン インデックス操作中のブロックを減らしたり回避したりできます。 詳細については、「 オンライン インデックス操作を使用したWAIT_AT_LOW_PRIORITY」を参照してください。

• OFF

  テーブル ロックは、インデックス操作の間適用されます。 クラスター化インデックス、空間インデックス、XML インデックスを作成、再構築、または削除したり、非クラスター化インデックスを再構築または削除したりするオフライン インデックス操作では、テーブルに対するスキーマ変更 (Sch-M) ロックが取得されます。 このため、操作中は、すべてのユーザーは基になるテーブルにアクセスできません。 非クラスター化インデックスを作成するオフライン インデックス操作では、最初にテーブルの共有 (S) ロックが取得されます。 これにより、基になるテーブル定義を変更できなくなりますが、インデックスのビルドの進行中にテーブル内のデータを読み取って変更できます。

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

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

• XML インデックス
• ローカル一時テーブルのインデックス
• ビューの最初の一意クラスター化インデックス
• 無効なクラスター化インデックス
• SQL Server 2017 (14.x) 以前のバージョンのクラスター化列ストア インデックス
• SQL Server 2016 (13.x) 以前のバージョンの非クラスター化列ストア インデックス
• 基になるテーブルに LOB データ型 (image、ntext、text) および空間データ型が含まれる場合のクラスター化インデックス。
• varchar(max) および varbinary(max) 列を、インデックス キーの一部にすることはできません。 SQL Server (SQL Server 2012 (11.x) 以降)、Azure SQL Database、および Azure SQL Managed Instance では、テーブルに varchar(max) 列または varbinary(max) 列が含まれている場合、 ONLINE オプションを使用して、他の列を含むクラスター化インデックスを構築または再構築できます。
• クラスター化列ストア インデックスを持つテーブルの非クラスター化インデックス

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

RESUMABLE = { ON | OFF }

に適用: SQL Server 2019 (15.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance

オンラインでのインデックス操作が再開可能かどうかを指定します。 詳細については、「 再開可能なインデックス操作 」および「 再開可能なインデックスに関する考慮事項」を参照してください。

• ON

  インデックス操作は再開可能です。

• OFF

  インデックス操作は再開可能ではありません。

MAX_DURATION = time [MINUTES] は RESUMABLE = ON (ONLINE = ON が必須) と共に使用

に適用: SQL Server 2019 (15.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance

再開可能なインデックス操作が一時停止されるまでの実行時間を分単位で指定します。

ALLOW_ROW_LOCKS = { ON | OFF }

行ロックを許可するかどうかを指定します。 既定値は ONです。

• ON

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

• OFF

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

ALLOW_PAGE_LOCKS = { ON | OFF }

ページ ロックを許可するかどうかを指定します。 既定値は ONです。

• ON

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

• OFF

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

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

に適用: SQL Server 2019 (15.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance

最後のページ挿入の競合を回避するために最適化するかどうかを指定します。 既定値は OFFです。 詳細については、「 シーケンシャル キー 」セクションを参照してください。

MAXDOP = max_degree_of_parallelism

インデックス操作の 並列処理の最大次数 構成オプションをオーバーライドします。 詳細については、「 max degree of parallelism サーバー構成オプションの構成」を参照してください。 MAXDOPを使用して、並列処理の程度と、インデックス作成操作の結果のリソース消費量を制限します。

max_degree_of_parallelism は次のように指定できます。

• 1

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

• >1

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

• 0 (既定値)

  現在のシステム ワークロードに基づいて減らされない限り、サーバー、データベース、またはワークロード グループ レベルで指定された並列処理の次数を使用します。

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

Note

並列インデックス操作は、Microsoft SQL Server の一部のエディションで使用できません。 SQL Server の各エディションでサポートされる機能の一覧については、「SQL Server 2022 の各エディションとサポートされている機能」を参照してください。

DATA_COMPRESSION

指定したインデックス、パーティション番号、またはパーティション範囲に、データ圧縮オプションを指定します。 次のようなオプションがあります。

• NONE

  インデックスまたは指定されたパーティションは圧縮されません。 これは列ストア インデックスには適用されません。

• ROW

  行の圧縮を使用して、インデックスまたは指定したパーティションが圧縮されます。 これは列ストア インデックスには適用されません。

• PAGE

  ページの圧縮を使用して、インデックスまたは指定したパーティションが圧縮されます。 これは列ストア インデックスには適用されません。

• COLUMNSTORE

  適用対象: SQL Server 2014 (12.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance

  非クラスター化列ストアとクラスター化列ストア インデックスの両方を含む列ストア インデックスにのみ適用されます。

• COLUMNSTORE_ARCHIVE

  適用対象: SQL Server 2014 (12.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance

  非クラスター化列ストアとクラスター化列ストア インデックスの両方を含む列ストア インデックスにのみ適用されます。 COLUMNSTORE_ARCHIVE 指定したパーティションをさらに小さいサイズに圧縮します。 これは、アーカイブ用や、ストレージのサイズを減らす必要があり、かつ保存と取得に時間をかける余裕があるその他の状況で使用できます。

圧縮の詳細については、「データ圧縮」を参照してください。

XML_COMPRESSION

適用対象: SQL Server 2022 (16.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance

1 つ以上の xml データ型列を含む、指定したインデックスの XML 圧縮オプションを指定します。 次のようなオプションがあります。

• ON

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

• OFF

  インデックスまたは指定されたパーティションは、XML 圧縮を使用して圧縮されません。

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

DATA_COMPRESSION または XML_COMPRESSION の設定が適用されるパーティションを指定します。 インデックスがパーティション分割されていない場合、 ON PARTITIONS 引数はエラーを生成します。 ON PARTITIONS 句を指定しないと、パーティション インデックスのすべてのパーティションに、DATA_COMPRESSION または XML_COMPRESSION オプションが適用されます。

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

• パーティション番号を指定します (例: ON PARTITIONS (2))。
• コンマで区切った複数の個別のパーティションのパーティション番号を指定します (例: ON PARTITIONS (1, 5))。
• 範囲と個別のパーティションの両方を提供します。たとえば次のとおりです: ON PARTITIONS (2, 4, 6 TO 8)。

<range> は、キーワード TOで区切られたパーティション番号として指定できます (例: ON PARTITIONS (6 TO 8))。

さまざまなパーティションにさまざまな種類のデータ圧縮を設定するには、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)
);

また、XML_COMPRESSION オプションを 1 回以上指定できます。次に例を示します。

REBUILD WITH
(
XML_COMPRESSION = OFF ON PARTITIONS (1),
XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

解説

CREATE INDEX ステートメントのクエリ プランを作成するときに、クエリ オプティマイザーはテーブル スキャンを実行する代わりに別のインデックスをスキャンすることを選択する場合があります。 状況によっては、並べ替え操作が削除される場合があります。 マルチプロセッサ コンピューターでは、 CREATE INDEX は、他のクエリと同じように、インデックスの作成に関連するスキャン操作と並べ替え操作に並列処理を使用できます。 詳細については、「 並列インデックス操作の構成」を参照してください。

データベース復旧モデルが一括ログまたは単純のいずれかに設定されている場合、 CREATE INDEX 操作は最小限に抑えられます。

一時テーブルにインデックスを作成することもできます。 テーブルが削除されるか、スコープ外になると、インデックスが削除されます。

主キー制約が追加されると、クラスター化インデックスがテーブル変数に基づいて構築されます。 同様に、一意制約が追加されると、非クラスター化インデックスがテーブル変数に基づいて構築されます。 テーブル変数がスコープ外になると、インデックスが削除されます。

インデックスでは拡張プロパティがサポートされます。

CREATE INDEX は、Microsoft Fabric ではサポートされていません。

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

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

[非クラスター化インデックス]

SQL Server 2016 (13.x) 以降、Azure SQL Database および Azure SQL Managed Instance では、クラスター化列ストア インデックスとして格納されているテーブルに非クラスター化インデックスを作成できます。 ヒープまたはクラスター化インデックスとして格納されているテーブルに非クラスター化インデックスを最初に作成した場合、後でテーブルをクラスター化列ストア インデックスに変換した場合、インデックスは保持されます。 クラスター化列ストア インデックスを再構築するときに、非クラスター化インデックスを削除する必要がではありません。

クラスター化列ストア インデックスとして格納されたテーブルに非クラスター化インデックスを作成する場合、FILESTREAM_ON オプションは無効です。

一意なインデックス

一意のインデックスが存在する場合、データベース エンジンは、データが追加または変更されるたびに重複する値をチェックします。 重複するキー値を生成する操作はロールバックされ、データベース エンジンはエラー メッセージを返します。 これは、データの追加または変更操作で多数の行が変更されても、重複が 1 つだけ発生する場合でも当てはまります。 IGNORE_DUP_KEY オプションが ON に設定されている一意のインデックスがある場合、行の挿入が試行された場合、一意のインデックスに違反する行は無視されます。

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

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

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

警告

固定されていないインデックスをパーティションが 1, 000 個以上あるテーブルに作成または再構築することは可能ですが、サポートされていません。 このような操作を行うと、操作中にパフォーマンスが低下したりメモリが過度に消費される可能性があります。 パーティションの数が 1,000 を超える場合にのみ、アラインインデックスを使用することをお勧めします。

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

インデックス付きビューは、テーブルのインデックスと同じ方法でパーティション分割されたテーブルに作成できます。 パーティション インデックスの詳細については、 パーティション テーブルとパーティション インデックス、およびSQL Server インデックスのアーキテクチャと設計ガイドを参照してください。

インデックスが作成または再構築されると、クエリによってインデックスの更新統計が最適化されます。 パーティション インデックスの場合、クエリ オプティマイザーは、パーティション分割されていないインデックスについてテーブル内のすべての行をスキャンする代わりに、既定のサンプリング アルゴリズムを使用します。 テーブル内のすべての行をスキャンしてパーティション インデックスの統計を作成するには、CREATE STATISTICS 句で UPDATE STATISTICS または FULLSCAN を使用します。

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

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

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

次のいずれかの条件が発生するたびに、[必須値] 列のSETオプションが必要です。

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

• INSERT、UPDATE、DELETE、またはMERGEステートメントは、フィルター選択されたインデックス内のデータを変更します。

• クエリ オプティマイザーで、クエリ プランの生成にフィルター選択されたインデックスが使用されるとき。

  SET オプション	必須値	既定のサーバー値	既定の OLE DB および ODBC 値	既定の DB-Library 値
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS 1	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 に設定する必要があります。

SETオプションが正しくない場合は、次の条件が発生する可能性があります。

• フィルター選択されたインデックスの作成が失敗します。
• データベース エンジンはエラーを生成し、インデックス内のデータを変更する INSERT、 UPDATE、 DELETE、または MERGE ステートメントをロールバックします。
• Transact-SQL ステートメントの実行プランで、クエリ オプティマイザーがインデックスを無視します。

フィルター選択されたインデックスの詳細については、「 フィルター選択されたインデックスの作成 」および 「SQL Server インデックスのアーキテクチャと設計ガイド」を参照してください。

空間インデックス

空間インデックスの詳細については、 CREATE SPATIAL INDEX と 空間インデックスの概要に関するページを参照してください。

XML インデックス数

XML インデックスについては、「CREATE XML INDEX」および「XML インデックス (SQL Server)」をご覧ください。

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

インデックス キーの最大サイズは 900 バイトをクラスター化インデックスと非クラスター化インデックスの 1,700 バイトです。 (SQL Database および SQL Server 2016 (13.x) 以前では、制限は常に 900 バイトでした。) バイト制限を超える varchar 列のインデックスは、列内の既存のデータがインデックス作成時の制限を超えない場合に作成できます。ただし、合計サイズが制限を超える列に対する後続の挿入操作または更新操作は失敗します。 クラスター化インデックスのインデックス キーには、ROW_OVERFLOW_DATAアロケーション ユニットに既存のデータを含む varchar 列を含めることはできません。 varchar 列にクラスター化インデックスが作成され、既存のデータがIN_ROW_DATA割り当て単位にある場合、その列に対する後続の挿入操作または更新操作によって、データが行外にプッシュされる場合は失敗します。

非クラスター化インデックスには、インデックスのリーフ レベルに非キー (含まれる) 列を含めることができます。 これらの列は、インデックス キー のサイズを計算するときにデータベース エンジンによって考慮されません。 詳細については、「 含まれる列を含むインデックスを作成する 」および 「SQL Server インデックスのアーキテクチャと設計ガイド」を参照してください。

Note

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

計算列

インデックスを計算列に作成できます。 さらに、計算列にはプロパティ 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 インデックスのアーキテクチャと設計ガイド」を参照してください。

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

SQL Server 2005 (9.x) では、新しいインデックス オプションが導入され、オプションの指定方法も変更されました。 下位互換性のある構文では、 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 句で非クラスター化インデックスを再構築できません。

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

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

Note

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

ONLINE オプション

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

• オンライン インデックス操作の実行中、基になるテーブルは変更、切り捨て、削除できない。
• インデックス操作中は、追加の一時ディスク領域が必要。
• オンライン操作は、パーティション インデックスや、保存される計算列を含むインデックス、または付加列で実行できる。
• WAIT_AT_LOW_PRIORITY引数オプションを使用すると、Sch-Mロックを待機するときにインデックス操作を続行する方法を決定できます。 詳細については、WAIT_AT_LOW_PRIORITYを参照してください。

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

再開可能なインデックス操作

に適用: SQL Server 2019 (15.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance

オンライン インデックス作成操作を再開可能にすることができます。 つまり、インデックスのビルドを停止してから、停止した時点から後で再起動できます。 再開可能としてインデックス ビルドを実行するには、 RESUMABLE = ON オプションを指定します。

再開可能なインデックス操作には、次のガイドラインが適用されます。

• RESUMABLE オプションを使用するには、ONLINE オプションも使用する必要があります。
• RESUMABLE オプションは、特定のインデックスのメタデータには保持されず、現在の DDL ステートメントの期間にのみ適用されます。 したがって、再開機能を有効にするには、RESUMABLE = ON 句を明示的に指定する必要があります。
• MAX_DURATION オプションは、次の 2 つのコンテキストで指定できます。
  • MAX_DURATION RESUMABLE オプションの場合は、再構築するインデックスの時間間隔を指定します。 この時間が経過しても、インデックスの再構築がまだ実行されている場合は、一時停止されます。 一時停止したインデックスの再構築を再開できるタイミングを決定します。 MAX_DURATIONの時間は、0 分以上 1 週間以下である必要があります (7 * 24 * 60 = 10080 分)。 インデックス操作の長時間の一時停止は、元のインデックスと新しく作成されたインデックスの両方にディスク領域が必要であり、DML 操作によって更新する必要があるため、特定のテーブルの DML パフォーマンスとデータベース ディスク容量に大きな影響を与える可能性があります。 MAX_DURATIONオプションを省略した場合、インデックス操作は完了するまで、またはエラーが発生するまで続行されます。
  • MAX_DURATION WAIT_AT_LOW_PRIORITY オプションでは、インデックス操作がブロックされている場合に、アクションを実行する前に、優先順位の低いロックを使用して待機する時間を指定します。 詳細については、「 オンライン インデックス操作を使用したWAIT_AT_LOW_PRIORITY」を参照してください。
• インデックス操作をすぐに一時停止するには、 ALTER INDEX PAUSE コマンドを実行するか、 KILL <session_id> コマンドを実行します。
• 元の CREATE INDEX ステートメントを同じパラメーターで再実行すると、一時停止中のインデックス作成操作が再開されます。 ALTER INDEX RESUME ステートメントを実行して、一時停止したインデックスのビルド操作を再開することもできます。
• ABORT コマンドは、インデックス ビルドを実行しているセッションを強制終了し、インデックス操作を取り消します。 中止されたインデックス操作を再開することはできません。

再開可能なインデックス操作は、完了、一時停止、または失敗するまで実行されます。 操作が一時停止した場合、操作が一時停止され、インデックスの作成が完了しなかったことを示すエラーが発行されます。 操作が失敗した場合は、エラーも発行されます。

インデックス操作が再開可能な操作として実行されているかどうかを確認し、現在の実行状態を確認するには、 sys.index_resumable_operations カタログ ビューを使用します。

リソース

再開可能なインデックス操作には、次のリソースが必要です。

• ビルドが一時停止されている時間など、インデックスの作成を維持するために必要な追加の領域。
• 並べ替えフェーズ中の追加ログ スループット。 再開可能なインデックスの全体的なログ領域使用量は、通常のオンライン インデックス作成と比較して少なく、この操作中にログを切り捨てることができます。
• インデックス操作の一時停止中に作成されるインデックスに関連付けられているテーブルを変更しようとする DDL ステートメントは許可されません。
• 一時停止と操作実行の両方の操作期間中、ゴースト クリーンアップはビルド内のインデックスでブロックされます。
• テーブルに LOB 列が含まれている場合、再開可能なクラスター化インデックスのビルドでは、操作の開始時にスキーマの変更 (Sch-M) ロックが必要です。

現在の機能上の制限

再開可能なインデックス作成操作には、次の制限があります。

• 再開可能なオンライン インデックス作成操作を一時停止した後、 MAXDOP の初期値を変更することはできません。
• 再開可能なインデックス操作では、 SORT_IN_TEMPDB = ON オプションはサポートされていません。
• RESUMABLE = ONを含む DDL コマンドは、明示的なトランザクション内では実行できません。
• 次を含む再開可能なインデックスを作成することはできません。
  • 計算列または timestamp (rowversion) 列をキー列として指定します。
  • 含まれる列としての LOB 列。
• 再開可能なインデックス操作は、次の場合にサポートされていません。
  • ALTER INDEX REBUILD ALL コマンド
  • ALTER TABLE REBUILD コマンド
  • 列ストア インデックス
  • フィルター選択されたインデックス
  • 無効なインデックス

オンライン インデックス操作での WAIT_AT_LOW_PRIORITY

適用対象: SQL Server 2022 (16.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance

WAIT_AT_LOW_PRIORITY オプションを使用しない場合、インデックス作成操作を開始して完了するには、テーブルまたはインデックスのロックを保持しているすべてのアクティブなブロック トランザクションが完了する必要があります。 オンライン インデックス操作が開始され、完了する前に、テーブルの共有 (S) ロックまたはスキーマ変更 (Sch-M) ロックを取得し、短時間保持する必要があります。 ロックは短時間しか保持されませんが、ワークロードのスループットに大きな影響を与えたり、クエリの待機時間を長くしたり、実行タイムアウトを引き起こしたりする可能性があります。

このような問題を回避するために、 WAIT_AT_LOW_PRIORITY オプションを使用すると、オンライン インデックス操作の開始と完了に必要な S ロックまたは Sch-M ロックの動作を、3 つのオプションから選択して管理できます。 いずれの場合も、 MAX_DURATION = n [minutes] によって指定された待機時間中に、インデックス操作を伴うブロックがない場合、インデックス操作はすぐに続行されます。

WAIT_AT_LOW_PRIORITY では、オンライン インデックス操作は優先順位の低いロックを使用して待機し、通常の優先度ロックを使用する他の操作をその間続行できるようにします。 WAIT_AT_LOW_PRIORITY オプションを省略すると、WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE) と同等になります。

MAX_DURATION = time [MINUTES]

オンライン インデックス操作が優先度の低いロックを使用して待機する待機時間 (分単位で指定された整数値)。 操作が MAX_DURATION 時間ブロックされている場合は、指定した ABORT_AFTER_WAIT アクションが実行されます。 MAX_DURATION time は常に分単位で、 MINUTES という単語は省略できます。

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS ]

• NONE: 通常の優先度でロックを待機し続けます。
• SELF: アクションを実行せずに、現在実行中のオンライン インデックス操作を終了します。 MAX_DURATIONが 0 の場合、SELFオプションは使用できません。
• BLOCKERS: オンライン インデックス操作をブロックするすべてのユーザー トランザクションを強制終了して、操作を続行できるようにします。 BLOCKERS オプションでは、CREATE INDEXまたは ALTER INDEX ステートメントを実行するプリンシパルにALTER ANY CONNECTIONアクセス許可が必要です。

次の拡張イベントを使用して、優先度の低いロックを待機するインデックス操作を監視できます。

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

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

ALLOW_ROW_LOCKS = ON と ALLOW_PAGE_LOCK = ON の場合、インデックスにアクセスするとき、行レベル、ページ レベル、テーブル レベルのロックが許可されます。 データベース エンジンは適切なロックを選択し、行ロックまたはページ ロックをテーブル ロックにエスカレートすることができます。

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

警告

インデックスの行ロックまたはページ ロックを無効にすることはお勧めしません。 コンカレンシー関連の問題が発生し、特定の機能が使用できない可能性があります。 たとえば、 ALLOW_PAGE_LOCKS が OFF に設定されている場合、インデックスを再構成することはできません。

シーケンシャル キー

適用対象: SQL Server 2019 (15.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance。

最終ページ挿入競合は、多数の同時実行スレッドがシーケンシャル キーを使用するインデックスに行を挿入しようと発生する、一般的なパフォーマンスの問題です。 先頭のキー列に、ID 列や現在の日付/時刻が既定値である日付など、常に増加 (または減少) する値が含まれている場合、インデックスはシーケンシャルと見なされます。 挿入されるキーはシーケンシャルであるため、インデックス構造の末尾 (つまり、同じページ) にすべての新しい行が挿入されます。 これにより、メモリ内のページの競合が発生します。これは、問題のページのラッチの取得を待機している複数のスレッドとして観察できます。 対応する待機の種類が PAGELATCH_EX。

OPTIMIZE_FOR_SEQUENTIAL_KEY インデックス オプションを有効にすると、インデックスへの高コンカレンシーの挿入のスループット向上に役立つ、データベース エンジン内での最適化が有効になります。 それは、シーケンシャル キーを使用していて最終ページ挿入競合が発生しやすいインデックスを対象とするものですが、B ツリー インデックス構造の他の領域でホット スポットが発生するインデックスでも役に立つ場合があります。

Note

ドキュメントでは、一般的にインデックスに関して B ツリーという用語が使用されます。 行ストア インデックスで、データベース エンジンによって B+ ツリーが実装されます。 これは、列ストア インデックスやメモリ最適化テーブルのインデックスには適用されません。 詳細については、「SQL Server と Azure SQL のインデックスのアーキテクチャとデザイン ガイド」を参照してください。

データ圧縮

データ圧縮の詳細については、「データ 圧縮」を参照してください。

データ圧縮を使用する場合のインデックス作成操作のコンテキストで考慮すべき重要なポイントを次に示します。

• 圧縮を使用すると、ページに格納できる行数が増えますが、最大行サイズは変更されません。
• インデックスの非リーフ ページでは、ページの圧縮は行われませんが、行の圧縮は可能です。
• 非クラスター化インデックスにはそれぞれ個別の圧縮設定があり、基になるテーブルの圧縮設定は継承されません。
• ヒープにクラスター化インデックスを作成する場合、圧縮状態を特に指定しない限り、ヒープの圧縮状態がクラスター化インデックスに継承されます。

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

XML 圧縮

適用対象: SQL Server 2022 (16.x) 以降のバージョン、Azure SQL データベース、および Azure SQL Managed Instance。

データ圧縮に関する考慮事項の多くは、XML 圧縮に適用されます。 また、次の考慮事項にも注意してください。

• パーティションの一覧を指定する場合は、個別のパーティションで XML 圧縮を有効にすることができます。 パーティションの一覧を指定しない場合は、すべてのパーティションで XML 圧縮を使用するように設定されます。 特に指定しない限り、テーブルまたはインデックスが作成されるとき、XML データ圧縮は無効になっています。 特に指定しない限り、既存の圧縮はテーブルの変更時にも保持されます。
• 範囲外の一連のパーティションまたは単独のパーティションを指定すると、エラーが生成されます。
• ヒープにクラスター化インデックスを作成する場合、代替の圧縮オプションを特に指定しない限り、ヒープの XML 圧縮状態がクラスター化インデックスに継承されます。
• ヒープの XML 圧縮設定を変更するには、テーブルのすべての非クラスター化インデックスを再構築して、ヒープ内の新しい行位置へのポインターを持つようにする必要があります。
• XML 圧縮は、オンラインまたはオフラインで有効または無効にすることができます。 オンライン操作の場合、ヒープに対する圧縮の有効化はシングル スレッドです。
• パーティション テーブル内のパーティションの XML 圧縮状態を確認するには、sys.partitions カタログ ビューのxml_compression列を使用します。

インデックス統計

行ストア インデックスが作成されると、データベース エンジンはインデックスのキー列に関する 統計 も作成します。 sys.stats カタログ ビューの統計オブジェクトの名前は、インデックスの名前と一致します。 パーティション分割されていないインデックスの場合、統計はデータのフル スキャンを使用して作成されます。 パーティション インデックスの場合、統計は既定のサンプリング アルゴリズムを使用して構築されます。

列ストア インデックスが作成されると、データベース エンジンも sys.stats に統計オブジェクトを作成します。 この統計オブジェクトには、ヒストグラムや密度ベクトルなどの統計データは含まれません。 これは、データベースをスクリプト化してデータベース複製を作成するときに使用されます。 その時点で、 DBCC SHOW_STATISTICS コマンドと UPDATE STATISTICS ... WITH STATS_STREAM コマンドを使用して、セグメント、ディクショナリ、デルタ ストア サイズなどの列ストア メタデータを取得し、列ストア インデックスの統計に追加します。 このメタデータは、通常のデータベースのクエリ コンパイル時に動的に取得されますが、データベース クローンの統計オブジェクトによって提供されます。 UPDATE STATISTICS コマンドは、他のシナリオでは列ストア インデックスの統計オブジェクトではサポートされていません。

アクセス許可

db_ddladmin固定データベース ロールのテーブルまたはビューまたはメンバーシップに対するALTER権限が必要です。

制限事項と制約事項

Azure Synapse Analytics および Analytics Platform System (PDW) では、以下を作成することはできません。

• 列ストア インデックスが既に存在する場合に、データ ウェアハウス テーブル上のクラスター化または非クラスター化行ストア インデックス。 この動作は、行ストアと列ストアの両方のインデックスが同じテーブル上に共存できる SMP SQL Server とは異なります。
• インデックスをビューに作成することはできません。

Metadata

既存のインデックスに関する情報を表示するには、sys.indexes カタログ ビューに対してクエリを実行します。

バージョンに関するメモ

• Azure SQL Database では、 PRIMARY以外のファイル グループはサポートされていません。
• Azure SQL Database と Azure SQL Managed Instance では、 FILESTREAM オプションはサポートされていません。
• 列ストア インデックスは、SQL Server 2012 (11.x) より前には使用できません。
• 再開可能なインデックス操作は、SQL Server 2017 (14.x) 以降、Azure SQL Database、および Azure SQL Managed Instance で使用できます。

例 :すべてのバージョン。 AdventureWorks データベースを使用します。

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

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

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

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

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

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

C. 他のデータベースのテーブルにインデックスを作成する

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

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D. インデックスに列を追加する

次の例では、dbo.FactFinance テーブルの 2 つの列でインデックス IX_FF を作成します。 次のステートメントでは、1 つ以上の列でインデックスを再構築し、既存の名前を保持します。

CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC);

-- Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC)
  WITH (DROP_EXISTING = ON);

例 :SQL Server、Azure SQL Database

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

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

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

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

-- 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.

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

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

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 に設定して同じステートメントを実行します。

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 行だけでしたが、このテーブルから行は挿入されませんでした。

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

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

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

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

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

-- 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

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

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

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

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

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

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

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

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

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

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

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

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

M. XML 圧縮を使用してインデックスを作成する

適用対象: SQL Server 2022 (16.x) 以降のバージョン、Azure SQL データベース、および Azure SQL Managed Instance。

次の例では、XML 圧縮を使用して、非パーティション テーブルのインデックスを作成します。 インデックス内の 1 列以上が、xml データ型である必要があります。

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

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

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

北 再開可能なインデックス操作を作成、再開、一時停止、中止する

に適用: SQL Server 2019 (15.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 ON test_table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 ON test_table PAUSE;
ALTER INDEX test_idx2 ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx1 ON test_table RESUME;
ALTER INDEX test_idx2 ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 ON test_table ABORT;
ALTER INDEX test_idx2 ON test_table ABORT;

O. 低優先度ロック オプションが異なる CREATE INDEX

次の例では、WAIT_AT_LOW_PRIORITY オプションを使用して、ブロックの対処にさまざまな戦略を指定します。

--Kill this session after waiting 5 minutes
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));
GO

--Kill blocker sessions
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));
GO

次の例では、RESUMABLE オプションの使用と、2 つの MAX_DURATION 値の指定の両方を行っています。1 つ目は ABORT_AFTER_WAIT オプションに適用され、2 つ目は RESUMABLE オプションに適用されます。

--With resumable option; default locking behavior 
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), RESUMABLE = ON, MAX_DURATION = 240 MINUTES);

例: Azure Synapse Analytics、Analytics Platform System (PDW)

P. 基本構文

再開可能なインデックス操作を作成、再開、一時停止、中止する

に適用: SQL Server 2019 (15.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx ON test_table ABORT;

Q. 現在のデータベース内のテーブルに非クラスター化インデックスを作成する

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

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

R. 他のデータベースのテーブルにクラスター化インデックスを作成する

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

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

S. 順序を付けてクラスター化したインデックスをテーブルで作成する

次の例では、順序を付けてクラスター化したインデックスが c1 データベースの c2 テーブルの T1 列と MyDB 列で作成されます。

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 
ORDER (c1, c2);

T. 順序を付けてクラスター化したインデックスに CCI をテーブルで変換する

次の例では、クラスター化された既存の列ストア インデックスが、MyOrderedCCI データベースの c1 テーブルの c2 列と T2 列で、順序を付けてクラスター化した、MyDB という名称の列ストア インデックスに変換されます。

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2
ORDER (c1, c2)
WITH (DROP_EXISTING = ON);

関連コンテンツ

• SQL Server のインデックスのアーキテクチャとデザイン ガイド
• オンラインでのインデックス操作の実行
• インデックスと ALTER TABLE
• ALTER INDEX
• CREATE PARTITION FUNCTION
• CREATE PARTITION SCHEME
• CREATE SPATIAL INDEX
• CREATE STATISTICS
• CREATE TABLE
• CREATE XML INDEX
• データの種類
• DBCC SHOW_STATISTICS
• DROP INDEX
• XML インデックス (SQL Server)
• sys.indexes
• sys.index_columns
• sys.xml_indexes
• EVENTDATA