CREATE INDEX (Transact-SQL)

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

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

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

インデックスの設計のガイドラインについては、「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) および SQL Database 以降では、列ストア インデックスに非クラスター化インデックスを使用して、データ ウェアハウスのクエリ パフォーマンスを向上させることができます。 詳しくは、「列ストア インデックス - データ ウェアハウス」をご覧ください。

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

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

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

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 行または複数行に対して一意のインデックスを作成するには、先に重複する値を削除しておく必要があります。 一意のインデックスに使用する列は NOT NULL に設定してください。一意のインデックスを作成するとき、複数の NULL 値は重複した値と見なされます。

CLUSTERED

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

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

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

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

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

NONCLUSTERED

テーブルの論理順序を示すインデックスを作成します。 非クラスター化インデックスの場合、データ行の物理的な順序は、そのインデックスが作成された順序とは関係ありません。

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

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

特に指定しない場合、既定のインデックスの種類は 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 は、インデックスのキー列として指定することはできません。 また、 ステートメントで参照されていない場合であっても、ビュー定義に ntext、text、または CREATE INDEX 列を含めることはできません。

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

[ ASC | DESC ]

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

INCLUDE (column [ ,... n ] )

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

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

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

決定的な計算列、および正確または不正確な計算列を、付加列にできます。 派生した計算列 image、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 は、インデックス定義で指定されている列に限定されません。 UNIQUE インデックスをパーティション分割する場合、column_name は一意のキーとして使用されている列から選択する必要がありますが、それ以外の場合はベース テーブルの任意の列を指定できます。 この制限により、データベース エンジンでは、単一のパーティション内だけでキー値の一意性を確認できます。

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

パーティション分割の詳細については、「パーティション テーブルとパーティション インデックス」を参照してください。

ON filegroup_name

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

ON "default"

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

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

[ 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_name が現在のデータベースの場合、または database_name が で、object_name が # で始まる場合に、3 つの要素で構成された名前形式 database_name.[tempdb].object_name をサポートします。

<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 の整数値にする必要があります。 FILL FACTOR 値 0 と 100 の機能は、まったく同じです。 fillfactor が 100 の場合、データベース エンジン では全容量を使用するリーフ ページでインデックスが作成されます。

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

FILL FACTOR の設定を表示するには、fill_factor で sys.indexes を使用します。

詳細については、「 インデックスの FILL FACTOR の指定」を参照してください。

SORT_IN_TEMPDB = { ON | OFF }

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

ON
インデックス構築に使用される中間の並べ替え結果が tempdb に格納されます。 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 操作全体がロールバックされます。

ビューに作成されたインデックス、一意でないインデックス、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 に設定するか、UPDATE STATISTICS 句を指定せずに NORECOMPUTE を実行します。

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

STATISTICS_INCREMENTAL = { ON | OFF }

適用対象:SQL Server (開始値 SQL Server 2014 (12.x)) および Azure SQL データベース

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 です。

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

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

詳しくは、「 Perform Index Operations Online」をご覧ください。

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

• 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 データベース では、テーブルに varchar(max) 列または varbinary(max) 列が含まれている場合、他の列を含むクラスター化インデックスは、ONLINE オプションを使用して作成または再作成できます。
• クラスター化列ストア インデックスを持つテーブルの非クラスター化インデックス

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

RESUMABLE = { ON | OFF }

適用対象:SQL Server (開始値 SQL Server 2019 (15.x)) および Azure SQL データベース

オンラインでのインデックス操作が再開可能かどうかを指定します。

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

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

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

適用対象:SQL Server (開始値 SQL Server 2019 (15.x)) および Azure SQL データベース

再開可能なオンラインでのインデックス操作が、一時停止までに実行される時間 (分単位で指定する整数値) を示します。

ALLOW_ROW_LOCKS = { ON | OFF }

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

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

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

ALLOW_PAGE_LOCKS = { ON | OFF }

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

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

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

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

適用対象:SQL Server (開始値 SQL Server 2019 (15.x)) および Azure SQL データベース

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

MAXDOP = max_degree_of_parallelism

インデックス操作の間、max degree of parallelism 構成オプションをオーバーライドします。 詳細については、「 max degree of parallelism サーバー構成オプションの構成」を参照してください。 並列プランの実行で使用されるプロセッサ数を制限するには、MAXDOP を使用します。 最大数は 64 プロセッサです。

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

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

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

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

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

DATA_COMPRESSION

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

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

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

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

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

XML_COMPRESSION

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

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

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

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

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

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

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

主キーが作成されたときに、テーブル変数にクラスター化インデックスを構築できます。 クエリが完了するかセッションが終了すると、インデックスが削除されます。

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

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

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

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

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

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

制限事項と制約事項:

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

一意なインデックス

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

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

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

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

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

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

SQL Server では、パーティション インデックスが作成または再構築された場合、テーブル内のすべての行をスキャンして統計を作成することはできません。 代わりに、クエリ オプティマイザーが既定のサンプリング アルゴリズムを使用して統計を生成します。 テーブル内のすべての行をスキャンしてパーティション インデックスの統計を作成するには、CREATE STATISTICS 句で UPDATE STATISTICS または FULLSCAN を使用します。

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

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

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

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

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

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

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

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

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 のインデックスのアーキテクチャとデザイン ガイド」を参照してください。

計算列

インデックスを計算列に作成できます。 また、計算列にプロパティ 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 句で非クラスター化インデックスを再構築できません。

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

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

ONLINE オプション

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

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

詳しくは、「 Perform Index Operations Online」をご覧ください。

リソース

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

• インデックスが一時停止されている期間など、構築されているインデックスを保持するために追加の領域が必要である。
• 並べ替えフェーズ中の追加ログ スループット。 再開可能なインデックスの全体的なログ領域使用量は、通常のオンライン インデックス作成と比較して少なく、この操作中にログを切り捨てることができます。
• いかなる DDL 変更も阻止する DDL 状態
• 一時停止と操作実行の両方の操作期間中、ゴースト クリーンアップはビルド内のインデックスでブロックされます。

現在の機能上の制限

再開可能なインデックス作成操作に対して次の機能は無効になります。

• 再開可能なオンライン インデックス作成操作が一時停止された後、MAXDOP の初期値を変更することはできません。

• 次のものを含むインデックスの作成

  • キー列としての計算列または TIMESTAMP 列
  • 再開可能なインデックス作成に含まれる列としての LOB 列
  • フィルター選択されたインデックス

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

適用対象:SQL Server (開始値 SQL Server 2019 (15.x)) および Azure SQL データベース

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

• オンラインでのインデックス作成は、RESUMABLE = ON オプションを使って再開可能として指定できます。
• RESUMABLE オプションは特定のインデックス用のメタデータ内に保持されるのではなく、現在の DDL ステートメントの実行中にのみ適用されます。 したがって、再開機能を有効にするには、RESUMABLE = ON 句を明示的に指定する必要があります。
• MAX_DURATION オプションは、RESUMABLE = ON オプションに対してのみサポートされます。
• RESUMABLE オプションの MAX_DURATION では、構築するインデックスの時間間隔を指定します。 この時間が経過すると、インデックスの構築が一時停止するか、またはその実行が完了します。 ユーザーは、一時停止されたインデックスの構築を再開可能にするタイミングを決定します。 の MAX_DURATION (分単位) は、0 分より長く、かつ 1 週間 (7 * 24 * 60 = 10080 分) 以下とする必要があります。 インデックス操作の一時停止時間を長くすると、特定のテーブルでの DML パフォーマンスおよびデータベースのディスク容量に影響を及ぼす可能性があります。元々存在するインデックスも新たに作成されたインデックスもディスク容量を必要とし、DML 操作中に更新される必要があるからです。 MAX_DURATION オプションを省略した場合、インデックス操作は、それが完了するかまたは障害が発生するまで続行されます。
• インデックス操作を直ちに一時停止するには、進行中のコマンドを停止するか (Ctrl + C キー)、ALTER INDEX PAUSE コマンドを実行するか、または KILL <session_id> コマンドを実行します。 コマンドが一時停止されたら、ALTER INDEX コマンドを使って再開することができます。
• 再開可能なインデックスに対する元の CREATE INDEX ステートメントを再実行すると、一時停止されていたインデックス作成操作が自動的に再開されます。
• SORT_IN_TEMPDB = ON オプションは、再開可能なインデックスに対してはサポートされていません。
• RESUMABLE = ON を指定した DDL コマンドを、明示的なトランザクション内で実行することはできません (begin TRAN ... COMMIT ブロックの一部にすることはできません)。
• インデックスの作成/再構築を再開または中止するには、 ALTER INDEX T-SQL 構文を使用します。
• 無効なインデックスはサポート対象外です。

インデックス作成が再開可能な操作として実行されることを示し、現在の実行状態を確認する方法については、「sys.index_resumable_operations」をご覧ください。

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

適用対象: 現在、この CREATE INDEX の構文は SQL Server 2022 (16.x)、Azure SQL Database、Azure SQL Managed Instance にのみ適用されます。 ALTER INDEX の場合、この構文は SQL Server (SQL Server 2014 (12.x) 以降) および Azure SQL データベース に適用されます。 詳細については、ALTER INDEX に関する記事をご覧ください。

low_priority_lock_wait 構文を使用すると、WAIT_AT_LOW_PRIORITY 動作を指定できます。 WAIT_AT_LOW_PRIORITY は、ONLINE=ON でのみ使用できます。

WAIT_AT_LOW_PRIORITY オプションを使用すると、DBA は、オンライン インデックス作成に必要な Sch-S ロックと Sch-M ロックを管理でき、3 つのオプションのいずれかを選択できます。 3 つのいずれのケースでも、待機時間 MAX_DURATION = n [minutes] 中にブロックするアクティビティがない場合は、オンライン インデックス再構築が待機なしで直ちに実行され、DDL ステートメントが完了します。

WAIT_AT_LOW_PRIORITY は、オンライン インデックス作成操作が優先度の低いロックを待機して、オンライン インデックス構築操作が待機している間、他の操作を続行可能にすることを示します。 WAIT AT LOW PRIORITY オプションを省略すると、WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE) と同等になります。

MAX_DURATION = time[MINUTES]

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

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

NONE: 通常の (標準) 優先度のロックを待機し続けます。

SELF: いずれのアクションも行わずに、現在実行中のオンライン インデックス作成の DDL 操作を終了します。 オプション SELF を、0 の MAX_DURATION と一緒に使用することはできません。

BLOCKERS: オンライン インデックス再構築の DDL 操作をブロックしているすべてのユーザー トランザクションを強制終了して、操作を続行できるようにします。 BLOCKERS オプションを使用するには、ログインに ALTER ANY CONNECTION 権限が必要です。

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

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

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

シーケンシャル キー

適用対象:SQL Server (開始値 SQL Server 2019 (15.x)) および Azure SQL データベース

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

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

インデックス情報の表示

インデックスに関する情報を返すには、カタログ ビュー、システム関数、およびシステム ストアド プロシージャを使用できます。

データ圧縮

データの圧縮については、「Data Compression」に記載されています。 特に次の点に注意してください。

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

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

• 固定されていないインデックスがテーブルにある場合、その 1 つのパーティションの圧縮設定を変更できません。
• ALTER INDEX <index> ... REBUILD PARTITION ... 構文は、そのインデックスの指定のパーティションを再構築します。
• ALTER INDEX <index> ... REBUILD WITH ... 構文は、そのインデックスのすべてのパーティションを再構築します。

圧縮状態の変更による、テーブル、インデックス、またはパーティションへの影響を評価するには、 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 圧縮状態を確認するには、xml_compression カタログ ビューの sys.partitions 列に対してクエリを実行します。

アクセス許可

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

制限事項と制約事項

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

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

Metadata

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

バージョンに関するメモ

SQL Database では、filegroup オプションおよび filestream オプションはサポートされません。

例 :すべてのバージョン。 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 (開始値 SQL Server 2019 (15.x)) および Azure SQL データベース

-- 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 (開始値 SQL Server 2019 (15.x)) および Azure SQL データベース

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