停用索引的指導方針

發行項
12/15/2008

停用索引會防止使用者存取索引，而停用叢集索引則會防止存取基礎資料表資料。SQL Server 2005 Database Engine 在 SQL Server 升級期間會自動停用索引，或者您也可以手動停用索引。如需詳細資訊，請參閱＜停用索引＞。

任何索引類型都可以停用。當索引停用時，適用下列規則：

如果是唯一索引，則 PRIMARY KEY 或 UNIQUE 條件約束，以及從其他資料表參考索引資料行的所有 FOREIGN KEY 條件約束，都會被停用。停用索引的使用者必須擁有這些資料表的 ALTER 權限，否則 ALTER INDEX DISABLE 陳述式會失敗。如果是叢集索引，則基礎資料表上的所有內送和外送 FOREIGN KEY 條件約束都會停用。
當索引停用時，會在警告訊息中列出條件約束名稱。重建索引之後，必須使用 ALTER TABLE CHECK CONSTRAINT 陳述式來手動啟用條件約束。
索引停用時，不會進行維護。
查詢最佳化工具在建立查詢執行計劃時，不會考慮索引。此外，使用資料表提示來參考已停用索引的查詢會失敗。
您無法建立與現有已停用索引同名的索引，因為索引定義仍存在於中繼資料內。
已停用的索引可將其卸除。

停用非叢集索引

停用非叢集索引實際上會刪除索引資料。然而，索引定義會保留在中繼資料內。下列額外的指導方針適用於停用非叢集索引時：

索引的統計資料會保留在原位，並視需要自動更新。
當關聯的叢集索引停用時，非叢集索引也會自動停用。直到啟用資料表或檢視上的叢集索引，或卸除資料表上的叢集索引時，才可以啟用非叢集索引。除非已使用 ALTER INDEX ALL REBUILD 陳述式啟用叢集索引，否則必須以明確方式啟用非叢集索引。如需詳細資訊，請參閱＜啟用索引和條件約束的指導方針＞。

停用叢集索引

下列額外的指導方針適用於停用叢集索引時：

除了用於卸除或重建叢集索引，否則無法存取已停用叢集索引的資料列。這表示：
- 下列作業將會失敗：SELECT、UPDATE、DELETE、INSERT、CREATE INDEX、CREATE STATISTICS、UPDATE STATISTICS (在索引上)，以及修改資料表資料行或條件約束的 ALTER TABLE 陳述式。
- 下列作業將會成功：REATE VIEW、DROP VIEW、CREATE TRIGGER、DROP TRIGGER、DROP INDEX、ALTER TABLE ENABLE/DISABLE TRIGGER、TRUNCATE TABLE 和 DROP TABLE。
- 當叢集索引停用時，不可已建立非叢集索引。
與資料表關聯的現有非叢集索引和 XML 索引會自動停用，而且無法存取。
在參考資料表的檢視上，會停用所有的叢集和非叢集索引。這些索引與被參考資料表上的那些索引一樣，都必須重建。

停用條件約束

下列額外的指導方針適用於停用 PRIMARY KEY、FOREIGN KEY 和 UNIQUE 條件約束時：

使用 ALTER INDEX DISABLE 陳述式來停用相關聯的索引，就會同時停用 PRIMARY KEY 和 UNIQUE 條件約束。
當 PRIMARY KEY 條件約束停用時，所有關聯的 FOREIGN KEY 條件約束也會停用。這相當於設定條件約束的 NOCHECK CONSTRAINT 選項。
您必須擁有被參考資料表的 ALTER 或 CONTROL 權限。
如果在外部索引鍵參考上宣告 CASCADE UPDATE 或 DELETE 動作，而後來該參考被停用，則會造成條件約束傳播修改至參考資料表的任何更新或刪除陳述式都會失敗。
當 PRIMARY KEY 或 UNIQUE 索引停用時，或是在 SQL Server 升級時發生了會停用索引的變更，重複值可能會被意外加入至資料表。您必須手動更正重複的資料列，才能成功啟用索引。可能的解決方案如下：
- 手動移除或變更重複值。
- 如果建立的 UNIQUE 索引不是因為建立 UNIQUE 條件約束所產生，則可在未指定 UNIQUE 的情況下使用 CREATE INDEX WITH DROP_EXISTING 重建索引。
- 如果建立的索引是 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 會將條件約束重新標記為信任的。
下例會停用限制資料中所接受薪資的條件約束。NOCHECK CONSTRAINT 可搭配 ALTER TABLE 使用，以停用條件約束，並允許插入通常會違反條件約束的值。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 條件約束時，會出現一個警告訊息，其中列出所有受影響的索引及 PRIMARY KEY 或 UNIQUE 條件約束。此外，您可以在 sys.indexes 目錄檢視中或使用 INDEXPROPERTY 函數，來檢視索引的停用狀態。您可以在 sys.foreign_keys 和 sys.check_constraints 目錄檢視中，分別檢視 FOREIGN KEY 和 CHECK 條件約束的停用狀態。如需詳細資訊，請參閱＜檢視索引資訊＞。

範例

下例會停用 Employee 資料表上的非叢集索引。

USE AdventureWorks;
GO
ALTER INDEX IX_Employee_ManagerID 
ON HumanResources.Employee DISABLE;

請參閱

共用方式為