Deaktivieren von Indizes und Constraints

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

In diesem Artikel wird beschrieben, wie ein Index oder Constraints in SQL Server mithilfe von SQL Server Management Studio oder Transact-SQL deaktiviert werden. 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. Die Indexdefinition verbleibt jedoch in den Metadaten, und bei nicht gruppierten Indizes werden die Indexstatistiken beibehalten. Beim Deaktivieren von gruppierten Indizes in einer Sicht oder nicht gruppierten Indizes werden die Indexdaten physisch gelöscht.

Durch das Deaktivieren eines gruppierten Indexes für eine Tabelle wird der Benutzerzugriff auf die Daten verhindert. Die Daten verbleiben in der Tabelle, sind jedoch erst für DML-Vorgänge (Datenbearbeitungssprache) verfügbar, wenn der Index gelöscht oder neu erstellt wurde.

Begrenzungen

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

Der deaktivierte 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 mit dem gleichen Namen erstellen, den ein vorhandener deaktivierter Index aufweist.

Ein deaktivierter Index kann nicht gelöscht werden.

Beim Deaktivieren eines eindeutigen Indexes werden die Constraint PRIMARY KEY oder UNIQUE sowie alle FOREIGN KEY-Constraints, die aus anderen Tabellen auf die indizierten Spalten verweisen, ebenfalls deaktiviert. Beim Deaktivieren eines gruppierten Indexes werden alle eingehenden und ausgehenden FOREIGN KEY-Constraints der zugrunde liegenden Tabelle ebenfalls deaktiviert. Die Namen der Einschränkungen werden in einer Warnmeldung aufgeführt, wenn der Index deaktiviert wird. Nach dem Neuerstellen des Indexes müssen alle Constraints mithilfe der Anweisung ALTER TABLE CHECK CONSTRAINT manuell aktiviert werden.

Nicht gruppierte Indizes werden mit dem Deaktivieren des gruppierten Indexes, dem sie zugeordnet sind, automatisch deaktiviert. Sie können erst dann wieder aktiviert werden, wenn der gruppierte Index der Tabelle oder die Sicht aktiviert bzw. der gruppierte Index der Tabelle gelöscht wurde. Nicht gruppierte Indizes müssen explizit aktiviert werden, es sei denn, der gruppierte Index wurde mithilfe der Anweisung ALTER INDEX ALL REBUILD aktiviert.

Die Anweisung ALTER INDEX ALL REBUILD 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.

Beim Deaktivieren eines gruppierten Indexes auf einer Tabelle werden auch alle gruppierten und nicht gruppierten Indizes in Sichten deaktiviert, die auf diese Tabelle verweisen. Diese Indizes müssen genau wie die Indizes der Tabelle, auf die sie verweisen, neu erstellt werden.

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

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 einen deaktivierten gruppierten Index jedoch immer offline neu erstellen, wenn Sie die Anweisung ALTER INDEX REBUILD oder CREATE INDEX WITH DROP_EXISTING verwenden. Weitere Informationen zu Vorgängen für Onlineindexes finden Sie unter Ausführen von Onlineindexvorgängen.

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

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

  • Für AUTO_CREATE_STATISTICS ist ON festgelegt.
  • Es sind keine Statistiken für die Spalte vorhanden.
  • Statistiken sind während der Abfrageoptimierung erforderlich.

Ist ein gruppierter Index deaktiviert, kann DBCC CHECKDB keine Informationen über die zugrunde liegende Tabelle zurückgeben. Stattdessen meldet die Anweisung, dass der gruppierte Index deaktiviert ist. DBCC INDEXDEFRAG kann nicht zur Defragmentierung eines deaktivierten Indexes verwendet werden. Die Anweisung schlägt mit einer Fehlermeldung fehl. Zum Neuerstellen eines deaktivierten Indexes können Sie DBCC DBREINDEX verwenden.

Durch Erstellen eines neuen gruppierten Indexes werden zuvor deaktivierte nicht gruppierte Indizes aktiviert. Weitere Informationen finden Sie unter Enable Indexes and Constraints.

Wenn die Tabelle ein Heap ist, werden alle nicht gruppierten Indizes neu erstellt.

Berechtigungen

Zum Ausführen von ALTER INDEX benötigen Sie mindestens die ALTER-Berechtigung für die Tabelle oder Ansicht.

Verwenden von SQL Server Management Studio

Deaktivieren eines Index

  1. Klicken Sie im Objekt-Explorer auf das Pluszeichen, um die Datenbank zu erweitern, die die Tabelle enthält, in der Sie einen Index deaktivieren möchten.

  2. Klicken Sie auf das Pluszeichen, um den Ordner Tabellen zu erweitern.

  3. Klicken Sie auf das Pluszeichen, um die Tabelle zu erweitern, in der Sie einen Index deaktivieren möchten.

  4. Klicken Sie auf das Pluszeichen, um den Ordner Indizes zu erweitern.

  5. Klicken Sie mit der rechten Maustaste auf den Index, den Sie deaktivieren möchten, und wählen Sie Deaktivierenaus.

    Hinweis

    Wenn die Tabelle im Modus Entwurf geöffnet ist, ist das Steuerelement Deaktivieren nicht verfügbar. Um fortzufahren, schließen Sie den Tabellen-Designer, und beginnen Sie von vorne.

  6. Überprüfen Sie im Dialogfeld Indizes deaktivieren, dass der richtige Index im Raster Zu deaktivierende Indizes ausgewählt ist, und klicken sie auf OK.

So deaktivieren Sie alle Indizes in einer Tabelle

  1. Klicken Sie in Objekt-Explorer auf das Pluszeichen, um die Datenbank zu erweitern, die die Tabelle enthält, in der Sie die Indizes deaktivieren möchten.

  2. Klicken Sie auf das Pluszeichen, um den Ordner Tabellen zu erweitern.

  3. Klicken Sie auf das Pluszeichen, um die Tabelle zu erweitern, in der Sie die Indizes deaktivieren möchten.

  4. Klicken Sie mit der rechten Maustaste auf den Ordner Indizes , und wählen Sie Alle deaktivierenaus.

  5. Überprüfen Sie im Dialogfeld Indizes deaktivieren, dass die richtigen Indizes im Raster Zu deaktivierende Indizes ausgewählt sind, und klicken sie auf OK. Um einen Index aus dem Raster Zu deaktivierende Indizes zu entfernen, wählen Sie den Index aus, und drücken Sie die ENTF-Taste.

Die folgenden Informationen sind im Dialogfeld Index deaktivieren verfügbar:

  • Indexname

    Zeigt den Namen des Indexes an. Während der Ausführung wird in dieser Spalte auch ein Symbol angezeigt, das den Status wiedergibt.

  • Tabellenname

    Zeigt den Namen der Tabelle oder Sicht an, für die der Index erstellt wurde.

  • Indextyp

    Zeigt den Typ des Indexes an: Gruppiert, Nicht gruppiert, Räumlichoder XML.

  • Status

    Zeigt den Status des Deaktivierungsvorgangs an. Mögliche Werte nach der Ausführung:

    • Leer

      Vor der Ausführung ist der Status leer.

    • In Bearbeitung

      Die Deaktivierung der Indizes wurde gestartet, ist aber noch nicht abgeschlossen.

    • Erfolgreich

      Der Deaktivierungsvorgang ist erfolgreich abgeschlossen.

    • Fehler

      Während des Indexdeaktivierungsvorgangs ist ein Fehler aufgetreten, und der Vorgang wurde nicht erfolgreich abgeschlossen.

    • Beendet

      Die Deaktivierung des Indexes wurde nicht erfolgreich abgeschlossen, weil der Benutzer den Vorgang gestoppt hat.

  • Meldung

    Stellt den Text der Fehlermeldungen während des Deaktivierungsvorgangs bereit. Während der Ausführung werden die Fehler als Links angezeigt. Der Text der Links beschreibt den Hauptteil des Fehlers. Die Spalte Meldung ist meist nicht breit genug, um den vollständigen Meldungstext lesen zu können. Der vollständige Text kann auf zwei Arten abgerufen werden:

    • Bewegen Sie den Mauszeiger über die Meldungszelle, um eine QuickInfo mit dem Fehlertext anzuzeigen.
    • Klicken Sie auf den Link, um ein Dialogfeld mit dem vollständigen Fehler anzuzeigen.

Verwenden von Transact-SQL

Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022- oder AdventureWorksDW2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

Deaktivieren eines Index

  1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-Engine-Instanz her.

  2. Klicken Sie in der Standardleiste auf Neue Abfrage.

  3. Kopieren Sie das folgende Beispiel in das Abfragefenster, und klicken Sie dann auf Ausführen. In diesem Beispiel wird der IX_Employee_OrganizationLevel_OrganizationNode-Index der HumanResources.Employee-Tabelle deaktiviert.

    USE AdventureWorks2022;
GO

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode
    ON HumanResources.Employee
DISABLE;

So deaktivieren Sie alle Indizes in einer Tabelle

  1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-Engine-Instanz her.

  2. Klicken Sie in der Standardleiste auf Neue Abfrage.

  3. Kopieren Sie das folgende Beispiel in das Abfragefenster, und klicken Sie dann auf Ausführen. In diesem Beispiel werden alle Indizes der HumanResources.Employee-Tabelle deaktiviert.

    USE AdventureWorks2022;
GO

ALTER INDEX ALL ON HumanResources.Employee
DISABLE;

Zugehöriger Inhalt