• Artikel

Richtlinien zum Deaktivieren von Indizes

Wenn Indizes deaktiviert werden, können Benutzer nicht mehr darauf zugreifen, und bei gruppierten Indizes können sie auch nicht mehr auf die dem Index zugrunde liegenden Tabellendaten zugreifen. SQL Server Database Engine (Datenbankmodul) deaktiviert Indizes während eines Updates von SQL Server möglicherweise automatisch. Sie können Indizes aber auch manuell deaktivieren. Weitere Informationen finden Sie unter Deaktivieren von Indizes.

Es können alle Indextypen deaktiviert werden. Beim Deaktivieren von Indizes gelten folgende Regeln:

  • Wenn der Index eindeutig ist, werden die PRIMARY KEY- oder UNIQUE-Einschränkung sowie alle FOREIGN KEY-Einschränkungen, die aus anderen Tabellen auf die indizierten Spalten verweisen, deaktiviert. Der Benutzer, der den Index deaktiviert, muss ALTER-Berechtigungen für diese Tabellen besitzen. Andernfalls erzeugt die ALTER INDEX DISABLE-Anweisung einen Fehler. Wenn es sich um einen gruppierten Index handelt, werden alle eingehenden und ausgehenden FOREIGN KEY-Einschränkungen der zugrunde liegenden Tabelle deaktiviert.

    Die Namen der Einschränkungen werden in einer Warnmeldung aufgeführt, wenn der Index deaktiviert wird. Nach dem Neuerstellen des Indexes müssen die Einschränkungen mithilfe der ALTER TABLE CHECK CONSTRAINT-Anweisung manuell aktiviert werden.

  • Der Index wird nicht beibehalten, während er deaktiviert ist.

  • Der Index wird beim Erstellen von Abfrageausführungsplänen nicht vom Abfrageoptimierer berücksichtigt. Des weiteren erzeugen Abfragen einen Fehler, die über einen Tabellenhinweis auf den deaktivierten Index verweisen.

  • Sie können keinen Index erstellen, der denselben Namen verwendet wie ein vorhandener deaktivierter Index, da die Indexdefinition weiterhin in den Metadaten vorhanden ist.

  • Ein deaktivierter Index kann nicht gelöscht werden.

Deaktivieren nicht gruppierter Indizes

Das Deaktivieren eines nicht gruppierten Indexes löscht die physischen Indexdaten. Die Indexdefinition bleibt jedoch in den Metadaten bestehen. Für das Deaktivieren von nicht gruppierten Indizes gelten folgende zusätzliche Richtlinien:

  • Die Statistiken des Indexes bleiben bestehen und werden gegebenenfalls automatisch aktualisiert.

  • Nicht gruppierte Indizes werden mit dem Deaktivieren des gruppierten Indexes, dem sie zugeordnet sind, automatisch deaktiviert. Sie können nicht mehr aktiviert werden, bis der gruppierte Index der Tabelle oder Sicht aktiviert bzw. der gruppierte Index der Tabelle gelöscht wird. Nicht gruppierte Indizes müssen explizit aktiviert werden, es sei denn, der gruppierte Index wurde mithilfe der ALTER INDEX ALL REBUILD-Anweisung aktiviert. Weitere Informationen finden Sie unter Richtlinien für das Aktivieren von Indizes und Einschränkungen.

Deaktivieren gruppierter Indizes

Für das Deaktivieren von gruppierten Indizes gelten folgende zusätzliche Richtlinien:

  • Auf die Datenzeilen des deaktivierten gruppierten Indexes kann nicht zugegriffen werden, mit Ausnahme von Zugriffen zum Löschen oder Neuerstellen des gruppierten Indexes. Dies bedeutet Folgendes:

    • Die folgenden Vorgänge erzeugen einen Fehler: SELECT-, UPDATE-, DELETE-, INSERT-, CREATE INDEX-, CREATE STATISTICS-, UPDATE STATISTICS- (für den Index) und ALTER TABLE-Anweisungen, die Tabellenspalten oder Einschränkungen ändern.

    • Die folgenden Vorgänge können erfolgreich ausgeführt werden: CREATE VIEW, DROP VIEW, CREATE TRIGGER, DROP TRIGGER, DROP INDEX, ALTER TABLE ENABLE/DISABLE TRIGGER, TRUNCATE TABLE und DROP TABLE.

    • Es können keine nicht gruppierten Indizes erstellt werden, während der gruppierte Index deaktiviert ist.

  • Vorhandene nicht gruppierte Indizes und XML-Indizes, die der Tabelle zugeordnet sind, werden automatisch deaktiviert und es kann nicht auf sie zugegriffen werden.

  • Alle gruppierten und nicht gruppierten Indizes von Sichten, die auf die Tabelle verweisen, wenden deaktiviert. Diese Indizes müssen genau wie die Indizes der Tabelle, auf die sie verweisen, neu erstellt werden.

Deaktivieren von Einschränkungen

Diese zusätzlichen Richtlinien betreffen das Deaktivieren von PRIMARY KEY-, FOREIGN KEY- und UNIQUE-Einschränkungen:

  • PRIMARY KEY- und UNIQUE-Einschränkungen werden deaktiviert, indem der ihnen zugeordnete Index mithilfe der ALTER INDEX DISABLE-Anweisung deaktiviert wird.

  • Wenn eine PRIMARY KEY-Einschränkung deaktiviert wird, werden alle zugeordneten FOREIGN KEY-Einschränkungen ebenfalls deaktiviert. Dies entspricht dem Festlegen der NOCHECK CONSTRAINT-Option für die Einschränkung.

  • Sie müssen über ALTER- oder CONTROL-Berechtigungen für die Tabelle verfügen, auf die verwiesen wird.

  • Wenn eine CASCADE UPDATE- oder DELETE-Aktion für einen Fremdschlüsselverweis deklariert und dieser Verweis deaktiviert wird, erzeugen sämtliche UPDATE- oder DELETE-Anweisungen einen Fehler, durch die die Einschränkung die Änderung an die verweisende Tabelle weitergeben könnte.

  • Doppelte Werte können einer Tabelle unbeabsichtigterweise hinzugefügt werden, während der PRIMARY KEY- oder UNIQUE-Index deaktiviert ist, oder – bei einem Update von SQL Server – durch eine Änderung, die das Deaktivieren des Indexes verursacht. Sie müssen die doppelten Zeilen manuell korrigieren, bevor der Index wieder aktiviert werden kann. Es stehen folgende Lösungen zur Verfügung:

    • Entfernen oder ändern Sie die doppelten Werte manuell.

    • Wenn der eindeutige Index nicht als Ergebnis einer UNIQUE-Einschränkung erstellt wurde, verwenden Sie CREATE INDEX WITH DROP_EXISTING um den Index neu zu erstellen, ohne UNIQUE anzugeben.

    • Wenn der Index als Nebenprodukt einer PRIMARY KEY- oder UNIQUE -Einschränkung erstellt wurde, müssen Sie die Einschränkung löschen. Der Index wird dann gelöscht. Bei PRIMARY KEY-Einschränkungen müssen auch alle eventuell vorhandenen FOREIGN KEY-Einschränkungen gelöscht werden.

  • Deaktivierte FOREIGN KEY- und CHECK-Einschränkungen werden mit is_not_trusted markiert und können in den Katalogsichten sys.check_constraints und sys.foreign_keys angezeigt werden. Dies bedeutet, dass das System nicht mehr alle Zeilen der Tabelle auf die Einschränkung überprüft. Auch wenn Sie die Einschränkung erneut aktivieren, werden nicht alle vorhandenen Zeilen für die Tabelle erneut überprüft, es sei denn, Sie geben die WITH CHECK-Option der ALTER TABLE-Anweisung an. Wenn Sie WITH CHECK angeben, wird die Einschränkung wieder als 'trusted' markiert.

    Im folgenden Beispiel wird eine Einschränkung deaktiviert, die die in den Daten akzeptierten Gehälter begrenzt. NOCHECK CONSTRAINT wird mit der ALTER TABLE-Anweisung verwendet, um die Einschränkung zu deaktivieren und eine Einfügung zuzulassen, die die Einschränkung andernfalls verletzen würde. WITH CHECK CHECK CONSTRAINT aktiviert die Einschränkung erneut und überprüft die vorhandenen Daten auf Konformität mit der neu aktivierten Einschränkung.

    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;

Deaktivieren von Indizes für Sichten

Das Deaktivieren eines gruppierten Indizes für eine Sicht löscht die physischen Indexdaten. Für das Deaktivieren von Indizes für Sichten gelten folgende zusätzliche Richtlinien:

  • Das Deaktivieren eines gruppierten Indexes für eine Sicht verhindert nicht, dass Änderungen an der zugrunde liegenden Tabelle vorgenommen werden können.

  • Das Deaktivieren eines gruppierten Indexes für eine Sicht deaktiviert ebenfalls alle eventuell vorhandenen nicht gruppierten Indizes für diese Sicht.

  • Die Datenzeilen des Indexes werden sowohl bei gruppierten als auch bei nicht gruppierten Indizes gelöscht. Die Sicht- und Indexdefinitionen bleiben jedoch in den Metadaten erhalten und können durch das Neuerstellen des Indexes bzw. der Indizes wiederhergestellt werden.

  • Die ALTER INDEX ALL REBUILD-Anweisung erstellt alle deaktivierten Indizes der Tabelle neu und aktiviert sie, mit Ausnahme von deaktivierten Indizes für Sichten. Indizes für Sichten müssen durch eine separate ALTER INDEX ALL REBUILD-Anweisung aktiviert werden.

  • Das Neuerstellen eines gruppierten Indexes für eine Sicht aktiviert nicht automatisch die nicht gruppierten Indizes für diese Sicht.

  • Die nicht gruppierten Indizes müssen manuell aktiviert werden, indem sie nach dem Neuerstellen des gruppierten Indexes neu erstellt werden.

Ausführen von Onlineindexvorgängen für deaktivierte Indizes

Sie können einen deaktivierten nicht gruppierten Index online neu erstellen, wenn die Tabelle über einen gruppierten Index verfügt, der nicht deaktiviert ist. Sie müssen jedoch einen deaktivierten gruppierten Index immer offline neu erstellen, wenn Sie die ALTER INDEX REBUILD- oder CREATE INDEX WITH DROP_EXISTING-Anweisung verwenden. Weitere Informationen zu Onlineindexvorgängen finden Sie unter Ausführen von Onlineindexvorgängen.

Statistiken für deaktivierte Indizes

Folgende Einschränkungen gelten für Indexstatistiken deaktivierter Indizes:

  • Die CREATE STATISTICS-Anweisung kann nicht für eine Tabelle mit einem deaktivierten gruppierten Index ausgeführt werden.

  • Die AUTO_CREATE_STATISTICS-Datenbankoption erstellt neue Statistiken für eine Spalte, wenn der Index deaktiviert ist und folgende Bedingungen zutreffen:

    • AUTO_CREATE_STATISTICS ist auf ON festgelegt.

    • Es sind keine Statistiken für die Spalte vorhanden.

    • Statistiken sind während der Abfrageoptimierung erforderlich.

  • sp_autostats erzeugt einen Fehler, wenn die angegebene Tabelle einen deaktivierten gruppierten Index besitzt.

  • sp_updatestats aktualisiert keine Statistiken für deaktivierte gruppierte Indizes.

  • sp_createstats erstellt Statistiken für Spalten, die möglicherweise führende Spalten eines deaktivierten Indexes sind. Wenn indexonly angegeben ist, werden keine Statistiken für eine Spalte eines deaktivierten Indexes erstellt, es sei denn, diese Spalte wird auch in einem anderen, aktivierten Index verwendet.

DBCC-Befehle

Wenn ein gruppierter Index deaktiviert wird, kann DBCC CHECKDB keine Informationen zu der zugrunde liegenden Tabelle zurückgeben. Stattdessen meldet die Anweisung, dass der gruppierte Index deaktiviert ist. DBCC INDEXDEFRAG kann nicht zum Defragmentieren von deaktivierten Indizes verwendet werden. Die Anweisung gibt eine Fehlermeldung zurück. Zum Neuerstellen eines deaktivierten Indexes können Sie DBCC DBREINDEX verwenden.

Anzeigen des Status eines deaktivierten Indexes

Wenn ein Index oder eine PRIMARY KEY- oder UNIQUE-Einschränkung deaktiviert ist, wird eine Warnmeldung mit der Liste aller betroffenen Indizes und FOREIGN KEY- oder CHECK-Einschränkungen angezeigt. Sie können den Deaktivierungsstatus eines Indexes auch in der sys.indexes-Katalogsicht oder über die INDEXPROPERTY-Funktion anzeigen lassen. Den Deaktivierungsstatus der FOREIGN KEY- und CHECK-Einschränkungen können Sie jeweils in den Katalogsichten sys.foreign_keys und sys.check_constraints anzeigen. Weitere Informationen finden Sie unter Anzeigen von Indexinformationen.

Beispiele

Das folgende Beispiel deaktiviert einen nicht gruppierten Index für die Employee-Tabelle.

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