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

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

統計の自動更新を復元するには、句なしで to OFF、または execute UPDATE STATISTICS をNORECOMPUTE設定STATISTICS_NORECOMPUTEします。

警告

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

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

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

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

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指定されていない場合、or XML_COMPRESSION オプションはDATA_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 = 時間 [ MINUTES ]

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

ABORT_AFTER_WAIT = { NONE |Standard Edition LF |BLOCKERS }

NONE

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

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

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

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

Share via

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 = 時間 [ MINUTES ]

ABORT_AFTER_WAIT = { NONE |Standard Edition LF |BLOCKERS }

フィードバック

フィードバック

その他のリソース

Share via

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 = 時間 [ MINUTES ]

ABORT_AFTER_WAIT = { NONE |Standard Edition LF |BLOCKERS }

関連するコンテンツ

フィードバック

フィードバック

その他のリソース