ALTER TABLE index_option (Transact-SQL)

[アーティクル]
05/23/2024

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

ALTER TABLE で作成される制約定義の一部であるインデックスに適用できる一連のオプションを指定します。

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

構文

{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ]
  | ONLINE = { ON | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [ MINUTES ]
}

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

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

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

Note

SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

PAD_INDEX = { ON | OFF }

適用対象: SQL Server 2008 (10.0.x) 以降のバージョン

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

ON

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

中間レベルページは、中間ページの一連のキーを考慮しつつ、インデックスが持つことのできる最大サイズの行が少なくとも 1 つ格納できる領域を残して、ほぼ容量いっぱいに使用されます。

FILLFACTOR = fillfactor

適用対象: SQL Server 2008 (10.0.x) 以降のバージョン

インデックスの作成時または変更時に、データベースエンジンが各インデックスページのリーフレベルをどの程度まで埋めるかを、パーセント値で指定します。 1 ～ 100 の整数値を指定する必要があります。既定値は 0 です。

Note

FILL FACTOR 値 0 と 100 は、すべての面でまったく同じ結果になります。

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 を使用します。

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

STATISTICS_NORECOMPUTE = { ON | OFF }

指定したインデックスに関連する統計の統計の自動更新オプション ( AUTO_STATISTICS_UPDATE) を無効または有効にします。既定値は、OFF です。

ON

統計の自動更新は、インデックスの再構築後に無効になります。
OFF

統計の自動更新は、インデックスの再構築後に有効になります。

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

警告

統計の自動更新を無効にすると、クエリオプティマイザーがテーブルを含むクエリの最適な実行プランを選択できなくなる可能性があります。このオプションは、資格のあるデータベース管理者のみが慎重に使用する必要があります。

この設定では、再構築操作中にインデックス関連の統計のフルスキャンによる自動更新が禁止されることはありません。

ALLOW_ROW_LOCKS = { ON | OFF }

適用対象: SQL Server 2008 (10.0.x) 以降のバージョン

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

ON

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

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

ALLOW_PAGE_LOCKS = { ON | OFF }

適用対象: SQL Server 2008 (10.0.x) 以降のバージョン

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

ON

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

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

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

適用対象: SQL Server 2019 (15.x) 以降のバージョン

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

SORT_IN_TEMPDB = { ON | OFF }

適用対象: SQL Server 2008 (10.0.x) 以降のバージョン

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

ON

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

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

ONLINE = { ON | OFF }

適用対象: SQL Server 2008 (10.0.x) 以降のバージョン

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

Note

一意の非クラスター化インデックスをオンラインで作成することはできません。これには、 UNIQUE 制約または PRIMARY KEY 制約のために作成されたインデックスが含まれます。

ON

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

Note

オンラインインデックス再構築では、このセクションの後の方で説明されている low_priority_lock_wait オプションを設定できます。 low_priority_lock_wait は、オンラインインデックス再構築中の S および Sch-M ロックの優先度を管理します。
OFF

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

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

Note

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

RESUMABLE = { ON | OFF}

適用対象: SQL Server 2022 (16.x) 以降のバージョン

ALTER TABLE ADD CONSTRAINT 操作が再開可能かどうかを指定します。 ON の場合はテーブル制約追加操作を再開できます。 OFF の場合、テーブル制約追加操作を再開できません。既定値は OFF です。 RESUMABLE オプションが ON に指定されている場合、オプション ONLINE = ON が必須です。

MAX_DURATIONRESUMABLE = ONと共に使用する場合 (ONLINE = ONが必要) は、再開可能なオンライン追加制約操作が一時停止される前に実行される時間 (分単位で指定された整数値) を示します。指定されていない場合、操作は完了するまで続行されます。 MAXDOP は、 RESUMABLE = ON でもサポートされています。

再開可能な ALTER TABLE ADD CONSTRAINT 操作の有効化と使用の詳細については、「再開可能なテーブル制約の追加」を参照してください。

MAXDOP = max_degree_of_parallelism

適用対象: SQL Server 2008 (10.0.x) 以降のバージョン

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

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

1: 並列プランの生成を抑制します。
>1: 並列インデックス操作で使用されるプロセッサの最大数を、指定した数に制限します。
0 (既定値): 現在のシステムワークロードに基づいて、プロセッサの実際の数以下を使用します。

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

Note

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

DATA_COMPRESSION

適用対象: SQL Server 2008 (10.0.x) 以降のバージョン

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

NONE

テーブルまたは指定したパーティションが圧縮されません。行ストアテーブルにのみ適用され、列ストアテーブルには適用されません。
ROW

行の圧縮を使用して、テーブルまたは指定したパーティションが圧縮されます。行ストアテーブルにのみ適用され、列ストアテーブルには適用されません。
PAGE

ページの圧縮を使用して、テーブルまたは指定したパーティションが圧縮されます。行ストアテーブルにのみ適用され、列ストアテーブルには適用されません。
COLUMNSTORE

適用対象: SQL Server 2014 (12.x) 以降のバージョン

列ストアテーブルにのみ適用されます。 COLUMNSTORE は、 COLUMNSTORE_ARCHIVE オプションで圧縮されたパーティションを展開することを指定します。データが復元されると、 COLUMNSTORE インデックスは、すべての列ストアテーブルに使用される列ストア圧縮で圧縮され続けます。
COLUMNSTORE_ARCHIVE

適用対象: SQL Server 2014 (12.x) 以降のバージョン

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

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

XML_COMPRESSION

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

テーブル内のすべての xml データ型列に XML 圧縮オプションを指定します。次のようなオプションがあります。

ON

xml データ型を使用した列が圧縮されます。
OFF

xml データ型を使用した列は圧縮されません。

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

適用対象: SQL Server 2008 (10.0.x) 以降のバージョン

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 オプションを複数回指定します。例:

--For rowstore tables
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)
)

--For columnstore tables
REBUILD WITH
(
  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (1, 3, 5),
  DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4, 6 TO 8)
)

<single_partition_rebuild__option>

ほとんどの場合、インデックスが再構築されると、パーティションインデックスのすべてのパーティションも再構築されます。次のオプションを 1 つのパーティションに適用した場合は、すべてのパーティションの再構築は行われません。

SORT_IN_TEMPDB
MAXDOP
DATA_COMPRESSION
XML_COMPRESSION

low_priority_lock_wait

適用対象: SQL Server 2014 (12.x) 以降のバージョン

SWITCHまたはオンラインインデックスの再構築は、このテーブルに対するブロック操作がないとすぐに完了します。 WAIT_AT_LOW_PRIORITY は、 SWITCH またはオンラインインデックス再構築操作をすぐに完了できない場合は待機することを示します。この操作は優先度の低いロックを保持し、DDL ステートメントと競合するロックを保持する他の操作が続行できるようにします。 WAIT AT LOW PRIORITY オプションを省略すると、WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE) と同等になります。

MAX_DURATION = time [ MINUTES ]

取得する必要がある SWITCH またはオンラインインデックス再構築ロックが DDL コマンドの実行時に待機する待機時間 (分単位で指定された整数値)。 SWITCHまたはオンラインインデックス再構築操作は、すぐに完了しようとします。操作が MAX_DURATION 時間ブロックされている場合は、 ABORT_AFTER_WAIT アクションのいずれかが実行されます。 MAX_DURATION time は常に分単位で、 MINUTES という単語は省略できます。

ABORT_AFTER_WAIT = { NONE |SELF |BLOCKERS }

NONE

(通常の優先度を使用して) ロック優先度を変更せずに、 SWITCH またはオンラインインデックス再構築操作を続行します。
SELF

アクションを実行せずに、現在実行中の SWITCH またはオンラインインデックス再構築 DDL 操作を終了します。
BLOCKERS

操作を続行できるように、現在 SWITCH またはオンラインインデックス再構築 DDL 操作をブロックしているすべてのユーザートランザクションを強制終了します。

BLOCKERS には、 ALTER ANY CONNECTION アクセス許可が必要です。

次の方法で共有

ALTER TABLE index_option (Transact-SQL)

構文

引数

PAD_INDEX = { ON | OFF }

FILLFACTOR = fillfactor

IGNORE_DUP_KEY = { ON | OFF }

STATISTICS_NORECOMPUTE = { ON | OFF }

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

SORT_IN_TEMPDB = { ON | OFF }

ONLINE = { ON | OFF }

RESUMABLE = { ON | OFF}

MAXDOP = max_degree_of_parallelism

DATA_COMPRESSION

XML_COMPRESSION

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

<single_partition_rebuild__option>

low_priority_lock_wait

MAX_DURATION = time [ MINUTES ]

ABORT_AFTER_WAIT = { NONE |SELF |BLOCKERS }

フィードバック

フィードバック

その他のリソース

次の方法で共有

ALTER TABLE index_option (Transact-SQL)

構文

引数

PAD_INDEX = { ON | OFF }

FILLFACTOR = fillfactor

IGNORE_DUP_KEY = { ON | OFF }

STATISTICS_NORECOMPUTE = { ON | OFF }

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

SORT_IN_TEMPDB = { ON | OFF }

ONLINE = { ON | OFF }

RESUMABLE = { ON | OFF}

MAXDOP = max_degree_of_parallelism

DATA_COMPRESSION

XML_COMPRESSION

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

<single_partition_rebuild__option>

low_priority_lock_wait

MAX_DURATION = time [ MINUTES ]

ABORT_AFTER_WAIT = { NONE |SELF |BLOCKERS }

関連するコンテンツ

フィードバック

フィードバック

その他のリソース