ALTER TABLE index_option (Transact-SQL)
適用対象: 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_DURATION
RESUMABLE = 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
アクセス許可が必要です。