次の方法で共有

ALTER INDEX (Transact-SQL)

  • [アーティクル]

インデックスの無効化、再構築、再構成によって、またはインデックスに関するオプションの設定によって、既存のテーブルやビュー インデックス (リレーショナルまたは XML) を変更します。

適用対象:SQL Server (SQL Server 2008 から現在のバージョン)、Azure AQL データベース。

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

構文

-- SQL Server Syntax

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD
        [ PARTITION = ALL ]
        [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
        | [ PARTITION = partition_number 
              [ WITH ( <single_partition_rebuild_index_option> ) [ ,...n ] ]
          ]  
    | DISABLE
    | REORGANIZE 
        [ PARTITION = partition_number ]
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
    | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

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

<rebuild_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 }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE} }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

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

-- Windows Azure SQL Database Syntax

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [
          [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 

        ] 
    | DISABLE
    | SET ( <set_index_option> [ ,...n ] ) 
    }
 [ ; ] 

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

<rebuild_index_option > ::= 
{
   IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
}

<set_index_option>::=
{
   IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

引数

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

  • ALL
    インデックスの種類に関係なく、テーブルまたはビューに関連付けられているすべてのインデックスを指定します。 1 つ以上のインデックスがオフラインまたは読み取り専用のファイル グループにあるか、指定した操作が 1 つ以上のインデックスの種類に許可されていない場合、ALL を指定するとステートメントは失敗します。 次の表は、インデックス操作と、許可されないインデックスの種類の一覧です。

    ALL を指定する操作

    テーブル内に存在すると操作が失敗するインデックスの種類

    REBUILD WITH ONLINE = ON

    XML インデックス

    空間インデックス

    列ストア インデックス

    適用対象 SQL Server 2012 から SQL Server 2014。

    REBUILD PARTITION = partition_number

    非パーティション インデックス、XML インデックス、空間インデックス、または無効化されたインデックス

    REORGANIZE

    ALLOW_PAGE_LOCKS が OFF に設定されたインデックス

    REORGANIZE PARTITION = partition_number

    非パーティション インデックス、XML インデックス、空間インデックス、または無効化されたインデックス

    IGNORE_DUP_KEY = ON

    XML インデックス

    空間インデックス

    列ストア インデックス

    適用対象 SQL Server 2012 から SQL Server 2014。

    ONLINE = ON

    XML インデックス

    空間インデックス

    列ストア インデックス

    適用対象 SQL Server 2012 から SQL Server 2014。

    注意

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

    ALL を PARTITION = partition_number と共に指定する場合、すべてのインデックスを固定する必要があります。 つまり、すべてのインデックスは、等価パーティション関数に基づいてパーティション分割されます。 ALL を PARTITION 句と共に使用すると、同じ partition_number のすべてのインデックス パーティションが再構築または再構成されることになります。 パーティション インデックスの詳細については、「パーティション テーブルとパーティション インデックス」を参照してください。

  • database_name
    データベースの名前です。

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

  • table_or_view_name
    インデックスに関連付けられているテーブルまたはビューの名前を指定します。 オブジェクトに対するインデックスのレポートを表示するには、sys.indexes カタログ ビューを使用します。

    Windows Azure SQL データベースでは、database_name が現在のデータベースの場合、または database_name が tempdb で、table_or_view_name が # で始まる場合に、3 つの要素で構成された名前形式 database_name.[schema_name].table_or_view_name をサポートします。

  • REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]
    同じ列、インデックスの種類、一意性属性、および並べ替え順に従って、インデックスを再構築します。 列ストア インデックスの場合、並べ替え順は適用されません。 この句には DBCC DBREINDEX と同じ機能があります。 REBUILD では、無効化されたインデックスが有効になります。 クラスター化インデックスを再構築しても、キーワード ALL を指定しない限り、関連付けられている非クラスター化インデックスは再構築されません。 インデックス オプションを指定しない場合は、sys.indexes に格納されている既存のインデックス オプション値が適用されます。 値が sys.indexes に格納されていないインデックス オプションについては、オプションの引数に定義されている既定値が適用されます。

    ALL を指定した場合で、基になるテーブルがヒープの場合、テーブルは再構築操作の影響を受けません。 テーブルに関連付けられている非クラスター化インデックスは再構築されます。

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

    注意

    プライマリ XML インデックスを再構築するとき、基になるユーザー テーブルはインデックス操作の間使用できなくなります。

  • PARTITION

    適用対象:SQL Server 2008 から SQL Server 2014。

    インデックスの 1 つのパーティションのみを再構築または再構成します。 PARTITION は、index_name がパーティション インデックス以外の場合は指定できません。

    PARTITION = ALL により、すべてのパーティションが再構築されます。

    注意

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

  • partition_number

    適用対象:SQL Server 2008 から SQL Server 2014。

    再構築または再構成するパーティション インデックスのパーティション番号を指定します。 partition_number は、変数を参照できる定数式です。 これにはユーザー定義型変数または関数、およびユーザー定義関数が含まれますが、Transact-SQL ステートメントを参照することはできません。 partition_number は必須であり、指定しないとステートメントは失敗します。

  • WITH (<single_partition_rebuild_index_option>)

    適用対象:SQL Server 2008 から SQL Server 2014。

    SORT_IN_TEMPDB、MAXDOP、および DATA_COMPRESSION は、単一のパーティションを再構築するとき (PARTITION = n) に指定できるオプションです。 XML インデックスは、単一のパーティションの再構築操作では指定できません。

  • DISABLE
    インデックスを無効とマークし、データベース エンジンで使用されないようにします。 どのインデックスも無効にできます。 無効になったインデックスのインデックス定義は、基になるインデックス データがなくてもシステム カタログに残ります。 クラスター化インデックスを無効にすると、ユーザーは基になるテーブルのデータにアクセスできなくなります。 インデックスを有効にするには、ALTER INDEX REBUILD または CREATE INDEX WITH DROP_EXISTING を使用します。 詳細については、「インデックスと制約の無効化」および「インデックスと制約の有効化」を参照してください。

  • REORGANIZE
    インデックスのリーフ レベルを再構成します。 クラスター化列ストア インデックスの場合、すべての閉じた行グループを列グループに移動するように指定します。 ALTER INDEX REORGANIZE ステートメントは、常にオンラインで実行されます。 つまり、ALTER INDEX REORGANIZE トランザクション中は、長期にわたって他をブロックするテーブル ロックは保持されず、基になるテーブルへのクエリまたは更新を続行できます。 REORGANIZE は、無効化されたインデックスまたは ALLOW_PAGE_LOCKS が OFF に設定されているインデックスに対しては指定できません。 トランザクションがロールバックされても、トランザクション内で実行された REORGANIZE はロールバックされません。

  • WITH ( LOB_COMPACTION = { ON | OFF } )

    適用対象:SQL Server 2008 から SQL Server 2014。

    ラージ オブジェクト (LOB) データを含むすべてのページを圧縮します。 LOB データ型は、image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max)、および xml です。 このデータを圧縮すると、ディスク領域をより効率よく使用できます。 既定値は ON です。

    • ON
      ラージ オブジェクト データを含むすべてのページが圧縮されます。

      特定のクラスター化インデックスを再構成すると、そのクラスター化インデックスに含まれるすべての LOB 列が圧縮されます。

      非クラスター化インデックスを再構成すると、そのインデックス内で非キー列 (付加列) となっているすべての LOB 列が圧縮されます。ALL を指定すると、指定したテーブルまたはビューに関連付けられているすべてのインデックスは再構成され、クラスター化インデックスに関連付けられているすべての LOB 列、基になるテーブル、または付加列のある非クラスター化インデックスは圧縮されます。

    • OFF
      ラージ オブジェクト データを含むページは圧縮されません。

      OFF の指定は、ヒープには影響しません。

    LOB_COMPACTION 句は、LOB 列が存在しない場合無視されます。

  • SET ( <set_index option> [ ,... n] )
    インデックスを再構築または再構成しないでインデックス オプションを指定します。 無効化されたインデックスには、SET は指定できません。

  • PAD_INDEX = { ON | OFF }

    適用対象:SQL Server 2008 から SQL Server 2014。

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

    • ON
      FILLFACTOR で指定される空き領域のパーセンテージが、インデックスの中間レベルのページに適用されます。 PAD_INDEX を ON に設定するときに FILLFACTOR を指定しない場合は、sys.indexes に格納されている FILL FACTOR 値が使用されます。

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

    詳細については、「CREATE INDEX (Transact-SQL)」を参照してください。

  • FILLFACTOR = fillfactor

    適用対象:SQL Server 2008 から SQL Server 2014。

    インデックスの作成時または変更時に、データベース エンジン が各インデックス ページのリーフ レベルをどの程度まで埋めるかを、パーセント値で指定します。 fillfactor 値には、1 ～ 100 の整数値を指定してください。 既定値は 0 です。 FILL FACTOR 値 0 と 100 の機能は、まったく同じです。

    明示的な FILLFACTOR 設定値は、インデックスの初回作成時または再構築時のみ適用されます。 データベース エンジンでは、ページ内で指定されたパーセント分の空領域は動的に保持されません。 詳細については、「CREATE INDEX (Transact-SQL)」を参照してください。

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

    重要

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

  • SORT_IN_TEMPDB = { ON | OFF }

    適用対象:SQL Server 2008 から SQL Server 2014。

    tempdb に並べ替え結果を格納するかどうかを指定します。 既定値は OFF です。

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

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

    並べ替え操作が必要ない場合、または並べ替えをメモリ内で実行できる場合、SORT_IN_TEMPDB オプションは無視されます。

    詳細については、「インデックスの SORT_IN_TEMPDB オプション」を参照してください。

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

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

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

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

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

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

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

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

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

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

    重要

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

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

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

    • ベース テーブルにパーティションで固定されていないインデックスを使用して作成された統計。

    • AlwaysOn の読み取り可能なセカンダリ データベースに対して作成された統計。

    • 読み取り専用のデータベースに対して作成された統計。

    • フィルター選択されたインデックスに対して作成された統計。

    • ビューに対して作成された統計。

    • 内部テーブルに対して作成された統計。

    • 空間インデックスまたは XML インデックスを使用して作成された統計。

    適用対象:SQL Server 2014 から SQL Server 2014。

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

    XML インデックスまたは空間インデックスの場合、ONLINE = OFF だけがサポートされます。ONLINE を ON に設定すると、エラーが発生します。

    注意

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

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

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

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

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

    • XML インデックス数

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

    • パーティション インデックスのサブセット (パーティション インデックス全体の再構築はオンラインで実行できます)

  • ALLOW_ROW_LOCKS = { ON | OFF }

    適用対象:SQL Server 2008 から SQL Server 2014。

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

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

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

  • ALLOW_PAGE_LOCKS = { ON | OFF }

    適用対象:SQL Server 2008 から SQL Server 2014。

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

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

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

    注意

    ALLOW_PAGE_LOCKS を OFF に設定した場合、インデックスを再構成することはできません。

  • MAXDOP **=**max_degree_of_parallelism

    適用対象:SQL Server 2008 から SQL Server 2014。

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

    重要

    MAXDOP オプションはすべての XML インデックスで構文的にサポートされていますが、空間インデックスまたはプライマリ XML インデックスの場合、現在の ALTER INDEX では単一のプロセッサのみが使用されます。

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

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

    • >1
      並列インデックス操作で使用される最大プロセッサ数を、指定した数に制限します。

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

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

    注意

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

  • DATA_COMPRESSION

    適用対象:SQL Server 2008 から SQL Server 2014。

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

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

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

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

    • COLUMNSTORE

      適用対象:SQL Server 2014 から SQL Server 2014。

      非クラスター化列ストア インデックスとクラスター化列ストア インデックスの両方を含む列ストア インデックスにのみ適用されます。 COLUMNSTORE は、COLUMNSTORE_ARCHIVE オプションで圧縮されたインデックスまたは指定のパーティションを解凍するように指定します。 復元されるデータは、すべての列ストア インデックスに使用された列ストア圧縮を使用して引き続き圧縮されます。

    • COLUMNSTORE_ARCHIVE

      適用対象:SQL Server 2014 から SQL Server 2014。

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

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

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

    適用対象:SQL Server 2008 から SQL Server 2014。

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

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

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

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

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

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

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

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

  • ONLINE = { ON | OFF } <single_partition_rebuild_index_option に適用する場合>
    インデックスまたは基になるテーブルのインデックス パーティションをオンラインまたはオフラインで再構築できるかどうかを指定します。 REBUILD がオンライン (ON) で行われる場合、このテーブルのデータは、インデックス操作中にクエリやデータ変更で使用できます。 既定値は OFF です。

    • ON
      長期のテーブル ロックは、インデックス操作の間は保持されません。 インデックス操作の主要フェーズの期間、ソース テーブルではインテント共有 (IS) ロックのみが保持されます。 インデックスの再構築を開始するときにテーブルに対する S ロックが必要であり、オンライン インデックス再構築を終了するときにテーブルに対する Sch-M ロックが必要です。 どちらのロックも短いメタデータ ロックですが、特に Sch-M ロックは、すべてのブロックしているトランザクションの完了を待機する必要があります。 待機中、Sch-M ロックは、同じテーブルにアクセスするためにこのロックの後に待機している他のすべてのトランザクションをブロックします。

      注意

      オンライン インデックス再構築では、このセクションの後の方で説明されている low_priority_lock_wait オプションを設定できます。

    • OFF
      テーブル ロックは、インデックス操作の間適用されます。 このため、操作中は、すべてのユーザーは基になるテーブルにアクセスできません。

  • WAIT_AT_LOW_PRIORITY

    適用対象:SQL Server 2014 から SQL Server 2014。

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

  • MAX_DURATION = time [MINUTES ]

    適用対象:SQL Server 2014 から SQL Server 2014。

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

  • ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

    適用対象:SQL Server 2014 から SQL Server 2014。

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

    • SELF
      いずれのアクションも行わずに、現在実行中のオンライン インデックス再構築の DDL 操作を終了します。

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

説明

インデックスをパーティションに再分割するか別のファイル グループに移動する場合、ALTER INDEX は使用できません。 このステートメントは、列の追加や削除、または列の順序変更など、インデックス定義の変更には使用できません。 これらの操作を実行するには、CREATE INDEX を DROP_EXISTING 句と共に使用します。

オプションを明示的に指定しない場合は、現在の設定が適用されます。 たとえば、REBUILD 句で FILLFACTOR 設定を指定しなかった場合、再構築処理では、システム カタログに格納されている FILL FACTOR 値が使用されます。 現在のインデックス オプション設定を表示するには、sys.indexes を使用します。

注意

ONLINE、MAXDOP、および SORT_IN_TEMPDB の値は、システム カタログに格納されません。インデックス ステートメントでオプション値を指定しない限り、各オプションの既定値が使用されます。

マルチプロセッサ コンピューター上では、ALTER INDEX REBUILD は他のクエリと同様、自動的に使用プロセッサの数を増やしてインデックスの変更に関連するスキャンや並べ替え操作を実行します。 LOB_COMPACTION の有無に関係なく、ALTER INDEX REORGANIZE を実行すると、max degree of parallelism の値は単一スレッドの操作になります。 詳細については、「並列インデックス操作の構成」を参照してください。

インデックスのあるファイル グループがオフラインまたは読み取り専用に設定されていると、インデックスを再構成または再構築することはできません。 キーワード ALL を指定した場合で、1 つ以上のインデックスがオフラインまたは読み取り専用のファイル グループにある場合、ステートメントは失敗します。

インデックスの再構築

インデックスの再構築では、インデックスを削除し再作成します。 この操作では、断片化をなくし、指定されているか既に存在する FILL FACTOR 設定に基づいてページを圧縮することによりディスク領域を取り戻した後、連続するページにインデックス行を再び並べ替えます。 ALL を指定した場合、テーブル上のすべてのインデックスが、1 回のトランザクションで削除され再構築されます。 FOREIGN KEY 制約は、前もって削除しておく必要はありません。 128 以上のエクステントがあるインデックスを再構築すると、データベース エンジンでは、トランザクションがコミットされるまで実際のページの割り当て解除とそれに関連するロックが延期されます。

小さなインデックスを再構築または再構成しても、多くの場合、断片化が解消することはありません。 小さなインデックスのページは、混合エクステントに格納されます。 混合エクステントは最大 8 つのオブジェクトで共有されるため、小さなインデックスを再構成または再構築しても、その断片化は解消されない場合があります。

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

以前のバージョンの SQL Server では、非クラスター化インデックスを再構築することで、ハードウェア障害により発生した不一致を修正できる場合がありました。 SQL Server 2008 以降でも、非クラスター化インデックスをオフラインで再構築することで、インデックスとクラスター化インデックス間の不一致を修正できますが、 オンラインでインデックスを再構築する場合、既存の非クラスター化インデックスを基に再構築が行われるので、不一致を維持してしまい非クラスター化インデックスの不一致を修復できません。 一方、オフラインでインデックスを再構築すると、クラスター化インデックス (ヒープ) のスキャンが行われ、不一致が解消されます。 不一致を解消する場合、以前のバージョンと同様に影響を受けたデータをバックアップから復元することをお勧めします。ただし、非クラスター化インデックスをオフラインで再構築しても、インデックスの不一致を修復できます。 詳細については、「DBCC CHECKDB (Transact-SQL)」を参照してください。

クラスター化列ストア インデックスを再構築する際、SQL Server は以下のように動作します。

  1. 再構築が行われている間、テーブルまたはパーティションを排他的にロックします。 再構築の間、データは "オフライン" になって使用できません。

  2. テーブルから論理的に削除された行を物理的に削除することで、列ストアをデフラグします。削除されたバイトは物理メディアで再利用されます。

  3. 元の列ストア インデックスから、デルタストアを含むすべてのデータを読み取ります。 データを新しい行グループに結合し、行グループを列ストアに圧縮します。

  4. 再構築中に、物理メディア上の領域を要求して列ストア インデックスのコピーを 2 つ格納します。 再構築が完了すると、SQL Server は元のクラスター化列ストア インデックスを削除します。

インデックスの再構成

インデックスの再構成では、最小のシステム リソースが使用されます。 この操作では、リーフ レベル ページをリーフ ノードの論理順序 (左から右) に合わせて物理的に並べ替えることにより、テーブルやビュー上にあるクラスター化および非クラスター化インデックスのリーフ レベルをデフラグします。 再構成でも、インデックス ページは圧縮されます。 圧縮は既存の FILL FACTOR 値に基づいて行われます。 FILL FACTOR 設定を表示するには、sys.indexes を使用します。

ALL を指定した場合、テーブル上のクラスター化および非クラスター化両方のリレーショナル インデックスと XML インデックスが再構成されます。 ALL を指定した場合は、いくつかの制限が適用されます。「引数」の ALL の定義を参照してください。

クラスター化列ストア インデックスを再編成するために、SQL Server は、"閉じている" とマークされているすべての行グループを列ストアに移動します。閉じた行グループを列ストアに移動するために再編成を行う必要はありません。 組ムーバー プロセスでは、最終的にすべての閉じた行グループが発見され移動されます。 ただし、組ムーバーはシングル スレッドであるため、ワークロードに対応できるだけ十分な速度で行グループを移動できない可能性があります。 確実に行グループが閉じた後に移動されようにするには、各読み込みの後で ALTER INDEX REORGANIZE を実行します。

詳細については、「インデックスの再編成と再構築」を参照してください。

インデックスの無効化

インデックスを無効化すると、ユーザーはインデックスにアクセスできなくなり、クラスター化インデックスの場合は基になるテーブル データにもアクセスできなくなります。 インデックス定義はシステム カタログに残ります。 ビュー上で非クラスター化インデックスまたはクラスター化インデックスを物理的に無効にすると、インデックス データが削除されます。 クラスター化インデックスを無効にすると、データにアクセスできなくなりますが、データはインデックスが削除または再構築されるまで B ツリーに残ります。このデータは管理されません。 有効化または無効化されたインデックスの状態を表示するには、sys.indexes カタログ ビューの is_disabled 列にクエリを実行します。

テーブルがトランザクション レプリケーション パブリケーション内にある場合、主キー列に関連付けられているインデックスを無効にすることはできません。 これらのインデックスはレプリケーションで必要です。 インデックスを無効にするには、まずパブリケーションからテーブルを削除する必要があります。 詳細については、「データとデータベース オブジェクトのパブリッシュ」を参照してください。

インデックスを有効にするには、ALTER INDEX REBUILD ステートメントまたは CREATE INDEX WITH DROP_EXISTING ステートメントを使用します。 ONLINE オプションが ON に設定されていると、無効化されたクラスター化インデックスを再構築できません。 詳細については、「インデックスと制約の無効化」を参照してください。

オプションの設定

特定のインデックスに対して、再構築または再構成を行わずに ALLOW_ROW_LOCKS、ALLOW_PAGE_LOCKS、IGNORE_DUP_KEY および STATISTICS_NORECOMPUTE オプションを設定できます。 変更された値はすぐにインデックスに適用されます。 これらの設定を表示するには、sys.indexes を使用します。 詳細については、「インデックス オプションの設定」を参照してください。

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

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

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

行またはページ ロック オプションが設定されている場合に ALL を指定すると、この設定はすべてのインデックスに適用されます。 基になるテーブルがヒープの場合、この設定は次のように適用されます。

ALLOW_ROW_LOCKS = ON または OFF

ヒープおよび関連する非クラスター化インデックスに適用。

ALLOW_PAGE_LOCKS = ON

ヒープおよび関連する非クラスター化インデックスに適用。

ALLOW_PAGE_LOCKS = OFF

非クラスター化インデックスに完全に適用。 この場合、非クラスター化インデックスではすべてのページ ロックが許可されません。 ヒープでは、ページに対して共有 (S)、更新 (U) および排他 (X) ロックだけが許可されなくなります。 データベース エンジンでは内部目的用にインテント ページ ロック (IS、IU または IX) を引き続き取得できます。

オンライン インデックス操作

インデックスを再構築する場合で ONLINE オプションが ON に設定されている場合、クエリおよびデータ変更で、基になるオブジェクト、テーブルおよび関連インデックスを使用できます。 1 つのパーティションに存在するインデックスの一部をオンラインで再構築することもできます。 排他テーブル ロックは、変更処理中の非常に短い時間だけ保持されます。

インデックスの再構成は、常にオンラインで実行されます。 この処理ではロックが長期間保持されないので、実行中のクエリや更新はブロックされません。

同じテーブルまたはテーブル パーティションでのオンライン インデックス操作は、次の場合のみ同時に実行できます。

  • 複数の非クラスター化インデックスを作成する。

  • 同じテーブルで異なるインデックスを再構成する。

  • 同じテーブルで重複しないインデックスを再構築する間、別のインデックスを再構成する。

その他すべてのオンライン インデックス操作は、同時に実行しようとしても失敗します。 たとえば、同じテーブル上で同時に複数のインデックスを再構築したり、同じテーブルで既存のインデックスを再構築する間に新しいインデックスを作成することはできません。

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

WAIT_AT_LOW_PRIORITY

オンライン インデックス再構築の DDL ステートメントを実行するには、特定のテーブルで実行されているすべてのアクティブなブロック トランザクションが完了する必要があります。 オンライン インデックス再構築を実行すると、このテーブルに対する実行の開始が準備できているすべての新しいトランザクションがブロックされます。 オンライン インデックス再構築のロックの期間は非常に短いですが、特定のテーブルに対して開かれているすべてのトランザクションの完了を待機し、新しいトランザクションの開始をブロックすることで、スループットに大きな影響を与える場合があり、ワークロードの速度低下またはタイムアウトを引き起こしたり、基になるテーブルへのアクセスが大幅に制限されたりします。 WAIT_AT_LOW_PRIORITY オプションは、オンライン インデックス再構築に必要な S ロックおよび Sch-M ロックを DBA が管理できるようにし、3 つのオプションのいずれかを選択できるようにします。 3 つのいずれのケースでも、待機時間 ((MAX_DURATION = n [minutes])) 中にブロックするアクティビティがない場合は、オンライン インデックス再構築が待機なしで直ちに実行され、DDL ステートメントが完了します。

空間インデックスに関する制限

空間インデックスを再構築するとき、基になるユーザー テーブルはインデックス操作の間使用できなくなります。これは、空間インデックスがスキーマ ロックを保持するためです。

ユーザー テーブルの PRIMARY KEY 制約は、そのテーブルの列に空間インデックスが定義されているときは変更できません。 PRIMARY KEY 制約を変更する場合は、初めにテーブルのすべての空間インデックスを削除してください。 PRIMARY KEY 制約を変更した後、各空間インデックスを再作成できます。

単一のパーティションの再構築操作では、空間インデックスを指定できません。 ただし、パーティションの完全な再構築では、空間インデックスを指定できます。

BOUNDING_BOX や GRID など、空間インデックス固有のオプションを変更するには、DROP_EXISTING = ON を指定する CREATE SPATIAL INDEX ステートメントを使用するか、空間インデックスを削除して新しく作成します。 例については、「CREATE SPATIAL INDEX (Transact-SQL)」を参照してください。

データの圧縮

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

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

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

  • ALTER INDEX ALL ... を使用しても、固定されていないインデックスがテーブルにあると、そのパーティションの圧縮の設定を変更できません。

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

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

統計

テーブルで ALTER INDEX ALL … を実行すると、インデックスに関連付けられた統計のみが更新されます。 (インデックスではなく) テーブルで作成されている自動または手動の統計は更新されません。

権限

ALTER INDEX を実行するには、少なくとも、テーブルまたはビューの ALTER 権限が必要です。

使用例

A. インデックスを再構築する

次の例では、AdventureWorks2012 データベースにある Employee テーブルで単一のインデックスを再構築します。

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;

B. テーブルですべてのインデックスを再構築し、オプションを指定する

次の例では、キーワード ALL を指定します。 これにより、AdventureWorks2012 データベースの Production.Product テーブルに関連付けられたすべてのインデックスが再構築されます。 3 つのオプションが指定されます。

適用対象:SQL Server 2008 から SQL Server 2014。

 
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

次の例は、優先度の低いロック オプションを含めて ONLINE オプションを追加し、行の圧縮オプションを追加します。

適用対象:SQL Server 2014 から SQL Server 2014。

 
ALTER INDEX ALL ON Production.Product
REBUILD WITH 
(
    FILLFACTOR = 80, 
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ), 
    DATA_COMPRESSION = ROW
)
;

C. クラスター化列ストア インデックスの再構築

この最初の手順では、クラスター化列ストア インデックスを含む FactInternetSales2 テーブルを準備し、最初の 4 つの列にデータを挿入します。

USE AdventureWorksDW2012;
GO
CREATE TABLE dbo.FactInternetSales2 (
    ProductKey [int] NOT NULL, 
    OrderDateKey [int] NOT NULL, 
    DueDateKey [int] NOT NULL, 
    ShipDateKey [int] NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2
ON dbo.FactInternetSales2;

INSERT INTO dbo.FactInternetSales2
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.FactInternetSales;

SELECT * FROM sys.column_store_row_groups;

その結果は、開いた行グループが 1 つあることを示しています。これは、SQL Serverが、行グループを閉じてデータを列ストアに移動する前に、より多くの行が追加されるのを待機することを意味します。 次のステートメントにより、クラスター化列ストア インデックスが再構築されます。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;
SELECT * FROM sys.column_store_row_groups;

SELECT ステートメントの結果は行グループが圧縮されていることを示しています。つまり、行グループの列セグメントは圧縮され、列ストアに格納されます。

D. LOB 圧縮を行いインデックスを再構成する

次の例では、AdventureWorks2012 データベースの単一のクラスター化インデックスを再構成します。 インデックスではリーフ レベルに LOB データ型が含まれるので、このステートメントではラージ オブジェクト データを含むすべてのページが圧縮されます。 既定値が ON であるため、WITH (LOB_COMPACTION) オプションの指定は必須ではありません。

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;

E. インデックスにオプションを設定する

次の例では、AdventureWorks2012 データベースのインデックス AK_SalesOrderHeader_SalesOrderNumber にいくつかのオプションを設定します。

適用対象:SQL Server 2008 から SQL Server 2014。

 
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

F. インデックスを無効にする

次の例では、AdventureWorks2012 データベースにある Employee テーブルで非クラスター化インデックスを無効にします。

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee
DISABLE ;

G. 制約を無効化する

次の例では、AdventureWorks2012 データベースの PRIMARY KEY インデックスを無効にすることにより PRIMARY KEY 制約を無効にします。 基になるテーブルに対する FOREIGN KEY 制約は自動的に無効になり、警告メッセージが表示されます。

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
DISABLE ;

結果セットでは、次の警告メッセージが返されます。

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'

on table 'EmployeeDepartmentHistory' referencing table 'Department'

was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

H. 制約を有効にする

次の例では、例 F で無効にした PRIMARY KEY および FOREIGN KEY 制約を有効にします。

PRIMARY KEY 制約は、PRIMARY KEY インデックスを再構築することにより有効にできます。

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
REBUILD ;

次に FOREIGN KEY 制約を有効にします。

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

I. パーティション インデックスを再構築する

次の例では、AdventureWorks2012 データベースのパーティション インデックス IX_TransactionHistory_TransactionDate のパーティション番号 5 の単一パーティションを再構築します。 パーティション 5 はオンラインで再構築され、優先度の低いロックの 10 分間の待機時間が、インデックスの再構築操作によって取得された各ロックに個別に適用されます。 この期間中に、インデックスの再構築を完了するためのロックを取得できない場合は、再構築操作のステートメントが中止されます。

適用対象:SQL Server 2014 から SQL Server 2014。

 
-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5 
   WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF )))
;
GO

J. インデックスの圧縮設定を変更する

次の例では、非パーティション行ストア テーブルのインデックスを再構築します。

適用対象:SQL Server 2008 から SQL Server 2014。

 
ALTER INDEX IX_INDEX1 
ON T1
REBUILD 
WITH ( DATA_COMPRESSION = PAGE );
GO

次の例では、保存用圧縮を使用するクラスター化列ストア インデックスを再構築し、次に保管用圧縮を削除する方法を示します。 最終結果では、列ストア圧縮のみを使用します。

適用対象:SQL Server 2014 から SQL Server 2014。

 
--Prepare the example by creating a table with a clustered columnstore index.
CREATE TABLE SimpleTable (
    ProductKey [int] NOT NULL, 
    OrderDateKey [int] NOT NULL, 
    DueDateKey [int] NOT NULL, 
    ShipDateKey [int] NOT NULL
);

CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);

CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable
ON SimpleTable
WITH ( DROP_EXISTING = ON );

--Compress the table further by using archival compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );

--Remove the archive compression and only use columnstore compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH ( DATA_COMPRESSION = COLUMNSTORE );
GO

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

関連項目

参照

CREATE INDEX (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

EVENTDATA (Transact-SQL)

概念

インデックスと制約の無効化

XML インデックス (SQL Server)

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

インデックスの再編成と再構築