インデックスと制約を無効にするためのガイドライン

非クラスター化インデックスの無効化

非クラスター化インデックスを削除すると、インデックス データが物理的に削除されます。ただし、インデックスの定義はメタデータに残ります。非クラスター化インデックスの無効化には、次のガイドラインも適用されます。

• インデックスの統計はそのまま残り、必要なときに自動的に更新されます。

• 非クラスター化インデックスは、関連付けられたクラスター化インデックスを無効にすると自動的に無効になります。非クラスター化インデックスを有効にするには、クラスター化インデックスを有効にする (テーブルまたはビューの場合) か、クラスター化インデックスを削除する (テーブルの場合) 必要があります。ALTER INDEX ALL REBUILD ステートメントを使用してクラスター化インデックスを有効にした場合を除き、非クラスター化インデックスは明示的に有効にする必要があります。詳細については、「インデックスと制約を有効にするためのガイドライン」を参照してください。

• 新しいクラスター化インデックスを作成すると、以前無効になった非クラスター化インデックスが有効になります。詳細については、「インデックスと制約を有効にするためのガイドライン」を参照してください。

クラスター化インデックスの無効化

クラスター化インデックスの無効化には、次のガイドラインも適用されます。

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

  • SELECT、UPDATE、DELETE、INSERT、CREATE INDEX、CREATE STATISTICS、UPDATE STATISTICS (対インデックス)、およびテーブル列または制約を変更する ALTER TABLE の各ステートメントの操作は失敗します。

  • CREATE VIEW、DROP VIEW、CREATE TRIGGER、DROP TRIGGER、DROP INDEX、ALTER TABLE ENABLE/DISABLE TRIGGER、TRUNCATE TABLE、および DROP TABLE の各操作は成功します。

  • クラスター化インデックスが無効である間は、非クラスター化インデックスを作成できません。

• テーブルに関連付けられている既存の非クラスター化インデックスおよび XML インデックスは自動的に無効になり、アクセスできません。

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

制約の無効化

PRIMARY KEY 制約、FOREIGN KEY 制約、および UNIQUE 制約の無効化には、次のガイドラインも適用されます。

• PRIMARY KEY 制約および UNIQUE 制約を無効にするには、ALTER INDEX DISABLE ステートメントを使用して、関連付けられているインデックスを無効にします。

• PRIMARY KEY 制約を無効にすると、関連付けられたすべての FOREIGN KEY 制約も無効になります。これは NOCHECK CONSTRAINT オプションを制約に設定した場合と同等の作用です。

• 参照先テーブルの ALTER 権限または CONTROL 権限が必要です。

• CASCADE UPDATE または CASCADE DELETE アクションを外部キー参照に対して宣言してあるときに、その参照が無効である場合、更新ステートメントまたは削除ステートメントを実行して参照元テーブルの変更を反映しようとしても、そのステートメントが失敗します。

• PRIMARY KEY インデックスまたは UNIQUE インデックスが無効な場合、または SQL Server のアップグレード時にインデックスを無効にするような変更を行ったことによって、テーブルに重複する値が誤って追加されることがあります。インデックスを正常に有効にするには、重複する行を手動で修正する必要があります。解決策は次のとおりです。

  • 重複する値を手動で削除または変更します。

  • UNIQUE 制約を作成した結果として UNIQUE インデックスが作成されなかった場合、CREATE INDEX WITH DROP_EXISTING を使用し、UNIQUE を指定せずにインデックスを再作成します。

  • PRIMARY KEY 制約または UNIQUE 制約の副産物としてインデックスが生成された場合、制約を削除する必要があります。続けて、インデックスを削除します。PRIMARY KEY 制約の場合は FOREIGN KEY 制約も削除する必要があります。

• 無効な FOREIGN KEY 制約および CHECK 制約には、is_not_trusted のマークが付きます。そのようすは、sys.check_constraints カタログ ビューおよび sys.foreign_keys カタログ ビューで確認できます。このマークが付いた制約ではテーブルのどの行も検証されません。制約を再度有効にしても、ALTER TABLE の WITH CHECK オプションを指定しない限り、テーブルでは既存の行について検証されません。WITH CHECK を指定すると、制約には再び信頼済みのマークが付きます。

  次の例では、データとして許容される給与を制限する制約を無効にします。ALTER TABLE と共に NOCHECK CONSTRAINT を使用すると制約が無効になり、通常であれば制約違反とされるデータを挿入できるようになります。WITH CHECK CHECK CONSTRAINT を実行すると制約が再度有効になり、既存のデータがその制約によって検証されます。

  CREATE TABLE cnst_example 
  (id INT NOT NULL,
      name VARCHAR(10) NOT NULL,
      salary MONEY NOT NULL
      CONSTRAINT salary_cap CHECK (salary < 100000);
  )
  
  -- Disable the constraint.
  ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap;
  
  -- Reenable the constraint.
  ALTER TABLE cnst_example WITH CHECK CHECK CONSTRAINT salary_cap;
  

ビューのインデックスの無効化

ビューのクラスター化インデックスを無効にすると、インデックス データが物理的に削除されます。ビューのインデックスの無効化には、次のガイドラインも適用されます。

• ビューのクラスター化インデックスを無効にしても、基になるテーブルは変更できます。

• ビューのクラスター化インデックスを無効にすると、そのビューの非クラスター化インデックスもすべて無効になります。

• インデックスのデータ行は、クラスター化、非クラスター化を問わず削除されます。しかし、ビューおよびインデックスの定義はメタデータに残るので、インデックスを再構築することで定義を再度作成できます。

• 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 に設定されている

  • 列の統計がまだ存在しない

  • クエリの最適化で統計が必要である

• 指定したテーブルのクラスター化インデックスが無効である場合、sp_autostats が失敗します。

• sp_updatestats を実行しても、無効化されたクラスター化インデックスの統計は更新されません。

• sp_createstats を実行すると、場合により無効化されたインデックスの先頭列を含めた列の統計が作成されます。indexonly を指定した場合、無効化されたインデックス内の列については、その列が別の有効なインデックスで使用されていない限り、統計が作成されません。

DBCC コマンド

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

無効化されたインデックスの状態の確認

インデックス、PRIMARY KEY 制約、UNIQUE 制約のいずれかが無効である場合、影響を受けるインデックスと FOREIGN KEY 制約または CHECK 制約をすべて列挙した警告メッセージが表示されます。また、sys.indexes カタログ ビューまたは INDEXPROPERTY 関数を使用して、インデックスの無効状態を確認できます。FOREIGN KEY 制約および CHECK 制約の無効状態は、それぞれ sys.foreign_keys カタログ ビューおよび sys.check_constraints カタログ ビューで確認できます。詳細については、「インデックス情報の表示」を参照してください。

使用例

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

USE AdventureWorks2008R2;
GO
ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode 
ON HumanResources.Employee DISABLE;

関連項目

参照

概念