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

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

この記事では、SQL Server で、SQL Server Management Studio または Transact-SQL を使用して、インデックスまたは製薬を無効にする方法について説明します。 インデックスを無効にすると、ユーザーはそのインデックスにアクセスできなくなります。クラスター化インデックスの場合は、基になるテーブルのデータにもアクセスできなくなります。 ただし、インデックス定義はメタデータに残り、インデックス統計は非クラスター化インデックス上に保持されます。 ビュー上でクラスター化インデックスまたは非クラスター化インデックスを物理的に無効にすると、インデックス データが削除されます。

テーブルのクラスター化インデックスを無効にすると、データにアクセスができなくなります。 データはテーブルに残りますが、そのインデックスを削除するか再構築するまでは、データ操作言語 (DML) 操作で使用できなくなります。

制限事項

無効なインデックスは保守されません。

クエリ オプティマイザーは、クエリの実行プランの作成時に無効なインデックスを考慮しません。 また、無効化されたインデックスをテーブル ヒントで参照するクエリは失敗します。

無効になっている既存のインデックスと同じ名前のインデックスを作成することはできません。

無効化されたインデックスは削除できます。

一意なインデックスを無効する場合、PRIMARY KEY 制約または UNIQUE 制約と、他のテーブルのインデックス付き列を参照するすべての FOREIGN KEY 制約も無効化されます。 クラスター化インデックスを無効にする場合、基になるテーブルを参照元または参照先とするすべての FOREIGN KEY 制約も無効になります。 インデックスを無効にすると、警告メッセージに制約名が表示されます。 インデックスを再構築した後で、ALTER TABLE CHECK CONSTRAINT ステートメントを使用してすべての制約を手動で有効にする必要があります。

非クラスター化インデックスは、関連付けられたクラスター化インデックスを無効にすると自動的に無効になります。 非クラスター化インデックスを有効にするには、クラスター化インデックスを有効にする (テーブルまたはビューの場合) か、クラスター化インデックスを削除する (テーブルの場合) 必要があります。 ALTER INDEX ALL REBUILD ステートメントを使用してクラスター化インデックスを有効にした場合を除き、非クラスター化インデックスは明示的に有効にする必要があります。

ALTER INDEX ALL REBUILD ステートメントを実行すると、ビューの無効化されたインデックスを除く、テーブル上の無効化されたインデックスがすべて再構築されて有効になります。 ビューのインデックスは、別の ALTER INDEX ALL REBUILD ステートメントで有効にする必要があります。

テーブルのクラスター化インデックスを無効にすると、そのテーブルを参照しているビューのクラスター化インデックスおよび非クラスター化インデックスもすべて無効になります。 このようなインデックスは、参照先テーブルのインデックスと同時に再構築する必要があります。

無効化されたクラスター化インデックスのデータ行には、クラスター化インデックスを削除または再構築する場合以外はアクセスできません。

無効化された非クラスター化インデックスは、無効化されたクラスター化インデックスがテーブルに存在しなければオンラインで再構築できます。 ただし、ALTER INDEX REBUILD ステートメントまたは CREATE INDEX WITH DROP_EXISTING ステートメントのいずれかを使用している場合は常に、無効化されたクラスター化インデックスをオフラインで再構築する必要があります。 オンラインでのインデックス操作の詳細については、「オンラインでのインデックス操作の実行」を参照してください。

クラスター化インデックスが無効になっているテーブルに対して、CREATE STATISTICS ステートメントを正常に実行することはできません。

インデックスが無効であり、次の条件に一致する場合、AUTO_CREATE_STATISTICS データベース オプションを指定することで列の新しい統計が作成されます:

  • AUTO_CREATE_STATISTICS が ON に設定されます。
  • 列の統計がまだ存在しない
  • クエリの最適化で統計が必要である

クラスター化インデックスが無効になっている場合、DBCC CHECKDB は基になるテーブルに関する情報を返すことができません。代わりに、クラスター化インデックスが無効であることが報告されます。 DBCC INDEXDEFRAG を使用して、無効化されたインデックスの断片化を解消することはできません。このステートメントはエラー メッセージを出力して失敗します。 無効化されたインデックスの再構築には DBCC DBREINDEX を使用できます。

新しいクラスター化インデックスを作成すると、以前に無効化された非クラスター化インデックスが有効になります。 詳しくは、「 Enable Indexes and Constraints」をご覧ください。

テーブルがヒープの場合、すべての非クラスター化インデックスが再構築されます。

アクセス許可

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

SQL Server Management Studio を使用する

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

  1. オブジェクト エクスプローラーで、プラス記号をクリックして、インデックスを無効にしたいテーブルが格納されているデータベースを展開します。

  2. プラス記号を選択して [テーブル] フォルダーを展開します。

  3. プラス記号をクリックして、インデックスを無効にするテーブルを展開します。

  4. プラス記号をクリックして [インデックス] フォルダーを開きます。

  5. 無効にするインデックスを右クリックし、 [無効化]を選択します。

    Note

    テーブルが デザイン モードで開いている場合、無効化 コントロールは使用できません。 続行するには、テーブル デザイナーを閉じてから、操作をやり直します。

  6. [インデックスの無効化] ダイアログ ボックスで、[無効にするインデックス] グリッドに目的のインデックスが表示されていることを確認し、[OK] をクリックします。

テーブルのすべてのインデックスを無効にする

  1. オブジェクト エクスプローラーで、プラス記号をクリックして、インデックスを無効にするテーブルが格納されているデータベースを展開します。

  2. プラス記号を選択して [テーブル] フォルダーを展開します。

  3. プラス記号をクリックして、インデックスを無効にするテーブルを展開します。

  4. [インデックス] フォルダーを右クリックし、 [すべて無効にする]を選択します。

  5. [インデックスの無効化] ダイアログ ボックスで、[無効にするインデックス] グリッドに目的のインデックスが表示されていることを確認し、[OK] をクリックします。 [無効にするインデックス] グリッドからインデックスを削除するには、インデックスを選択し、[Delete] キーを押します。

[無効にするインデックス] ダイアログ ボックスには、次の情報が表示されます。

  • Index Name

    インデックスの名前を表示します。 実行中は、状態を表すアイコンもこの列に表示されます。

  • テーブル名

    インデックスが作成されているテーブルまたはビューの名前を表示します。

  • [インデックスの種類]

    インデックスの種類 ( [クラスター化]、 [非クラスター化]、 [空間]、または [XML]) を表示します。

  • Status

    無効化操作の状態を表示します。 実行後の値は、次のいずれかになります。

    • 空白

      実行前に、[ステータス] は空白です。

    • 処理中

      インデックスの無効化操作が開始されていますが、まだ完了していません。

    • 成功

      インデックスの無効化操作は正常に終了しました。

    • エラー

      インデックスの無効化操作の実行中にエラーが発生したため、操作が正常に終了しませんでした。

    • 停止

      インデックスの無効化操作はユーザーによって中止されたため、正常に終了しませんでした。

  • メッセージ

    無効化操作中にエラー メッセージのテキストを表示します。 実行中、エラーはハイパーリンクとして表示されます。 エラーの内容は、ハイパーリンクのテキストに示されます。 多くの場合、 [メッセージ] 列が狭いためにメッセージ テキスト全体が表示されません。 テキスト全体を表示するには、次の 2 つの方法があります。

    • マウス ポインターをメッセージ セル上に移動して、ヒントにエラー テキストが表示されるようにします。
    • ハイパーリンクを選択して、エラー全体を表示するダイアログ ボックスを開きます。

Transact-SQL の使用

この記事の Transact-SQL コード サンプルは AdventureWorks2022 サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。

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

  1. オブジェクト エクスプローラーで、 データベース エンジンのインスタンスに接続します。

  2. 標準バーで、 [新しいクエリ] を選択します。

  3. 次の例をコピーしてクエリ ウィンドウに貼り付け、 [実行] を選択します。 このサンプルでは、HumanResources.Employee テーブルの IX_Employee_OrganizationLevel_OrganizationNode インデックスを無効にします。

    USE AdventureWorks2022;
GO

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode
    ON HumanResources.Employee
DISABLE;

テーブルのすべてのインデックスを無効にする

  1. オブジェクト エクスプローラーで、 データベース エンジンのインスタンスに接続します。

  2. 標準バーで、 [新しいクエリ] を選択します。

  3. 次の例をコピーしてクエリ ウィンドウに貼り付け、 [実行] を選択します。 このサンプルでは、HumanResources.Employee テーブルのすべてのインデックスを無効にします。

    USE AdventureWorks2022;
GO

ALTER INDEX ALL ON HumanResources.Employee
DISABLE;

関連するコンテンツ