Das Erstellen eines Index in SQL Server ist eine mäßig einfache Aufgabe, die die Abfrageleistung verbessert. Wir können sowohl einen Clustered-Index als auch einen nicht-Clustered-Index erstellen, der je nach Anforderung entweder eindeutig oder nicht eindeutig ist.
Wenn wir bereits einen Index für eine Tabelle erstellt haben, müssen Sie jedoch aufgrund einer Anforderungsänderung eine oder mehrere Spalten zu einem vorhandenen Index hinzufügen oder entfernen. In diesem Fall können wir die verwenden DROP_EXISTING Option, ohne die zusätzliche Codezeile zu schreiben, um den vorhandenen Index zuerst zu löschen und dann neu zu erstellen. (Dies ist ein altmodischer Codierstil: Drop, falls vorhanden und Neues erstellen.)
Mit DROP_EXISTING = ON, SQL Server löscht den vorhandenen gruppierten / nicht gruppierten Index mit geänderten Indexspezifikationen und erstellt ihn neu. Der Indexname bleibt dabei erhalten. Wir können Spalten hinzufügen / entfernen, die Sortierreihenfolge ändern oder auch eine Dateigruppe ändern.
DROP_EXISTING ist standardmäßig deaktiviertund es funktioniert nicht, wenn Sie diese Option beim erstmaligen Erstellen eines neuen Indexes verwenden. DROP_EXISTING = ON in SQL Server funktioniert nur, wenn Sie bereits einen Index mit dem angegebenen Namen für eine Tabelle haben.
Mit DROP_EXISTING können wir Folgendes tun:
1. Das Ändern eines nicht gruppierten Index in einen gruppierten Index ist zulässig.
Mit DROP_EXISTING können wir Folgendes nicht tun:
1. Das Ändern eines Clustered-Index in einen anderen Indextyp ist nicht zulässig.
Vorteile der Verwendung von DROP_EXISTING = ON
Wenn Sie einen Clustered-Index für eine Tabelle, für die bereits ein nicht-Clustered-Index vorhanden ist, neu erstellen, verwenden Sie DROP_EXISTING = ON In SQL Server bietet das erneute Erstellen eines gruppierten Index die folgenden Vorteile:
• Nicht gruppierte Indizes werden nicht erneut erstellt. Wenn wir einen Clustered-Index mit DROP_EXISTING = ON in SQL Server neu erstellen, weiß er, dass der Clustering-Schlüssel im Non-Clustered-Index nicht geändert wurde. Das spart viel Zeit. Jeder nicht gruppierte Index verfügt über einen Clustering-Schlüssel, der auf den gruppierten Index verweist. Wenn ein gruppierter Index neu erstellt wird, muss SQL Server auch den nicht gruppierten Index ändern. Die Verwendung von DROP_EXISTING = ON ist jedoch von Vorteil, da die nicht gruppierten Indizes nicht erneut erstellt werden müssen.
• Wenn Sie die alte Methode zum Löschen und erneuten Erstellen eines Clustered-Indexes verwenden, werden alle nicht-Clustered-Indizes für diese Tabelle erneut erstellt, um dem Clustering-Schlüssel zu entsprechen. Dies kann mit gespeichert werden DROP_EXISTING = ON mit gruppiertem Index.
Syntax für die Indexerstellung mit DROP_EXISTING
CREATE [Unique] [Clustered | NonClustered] INDEX Index_Name ON (Spaltenname) WITH DROP_EXISTING = {ON | AUS}; Beispiele
Erstellen Sie eine Mustertabelle, Mitarbeiter.
WENN OBJECT_ID ('Employee', 'U') NICHT NULL DROP TABLE Employee ist; CREATE TABLE Mitarbeiter (Id INT PRIMARY KEY, Name VARCHAR (50), Telefon VARCHAR (10), Geschlecht CHAR (1), Land VARCHAR (100), Gehalt DECIMAL (18,2)); 1. DROP_EXISTING = ON schlägt beim Erstellen eines neuen Index fehl
Im folgenden Beispiel versucht die Option DROP_EXISTING = ON, einen vorhandenen Index mit zu finden NCI_Employee_TelphoneGender Wenn der Name nicht beendet wird, schlägt die Indexerstellung mit der folgenden Fehlermeldung fehl.
CREATE NONCLUSTERED INDEX NCI_Employee_TelphoneGender ON Mitarbeiter (Telefon, Geschlecht) WITH (DROP_EXISTING = ON);
Meldung 7999, Ebene 16, Status 9, Zeile 1
Für die Tabelle 'Employee' konnte kein Index namens 'NCI_Employee_TelphoneGender' gefunden werden.
Index erstellen
CREATE NONCLUSTERED INDEX NCI_Employee_TelphoneGender ON Mitarbeiter (Telefon, Geschlecht);
Abfrage zum Anzeigen der Liste der Spalten in einem Index
Die folgende Abfrage zeigt, dass die Spalten Telefon und Geschlecht in den erstellten Non-Clustered-Index aufgenommen wurden.
Wählen Sie den Namen ColumnListInIndex aus sys.index_columns als ic innerer Join aus. sys.columns c auf ic.column_id = c.column_id und ic.object_id = c.object_id wobei ic.object_id = (Wählen Sie object_id aus sys.indexes aus, wobei name = 'NCI_Employee_TelphoneGender' ist. und type_desc = 'NONCLUSTERED') und ic.index_id = 2;
2. DROP_EXISTING = ON in SQL Server funktioniert für den vorhandenen Index
Im obigen Beispiel haben wir einen INDEX erstellt. NCI_Employee_TelphoneGender in der Spalte Telefon und Geschlecht. Aufgrund einer geänderten Anforderung müssen wir jetzt auch eine Spalte "Land" in den vorhandenen nicht gruppierten Index aufnehmen. Dies erreichen wir mit der Option DROP_EXISTING = ON.
In dem folgenden Beispiel wird mit DROP_EXISTING = ON der angegebene Index zuerst von SQL Server gelöscht und ein Index mit demselben Namen und der enthaltenen Country-Spalte neu erstellt.
CREATE NONCLUSTERED INDEX NCI_Employee_TelphoneGender ON Mitarbeiter (Telefon, Geschlecht, Land) WITH (DROP_EXISTING = ON);
Alte Methode zum Schreiben des obigen Codes
Der obige Code mit DROP_EXISTING = ON ersetzt eine alte Methode zum Löschen und erneuten Erstellen eines Indexes. Wenn wir den folgenden Code verwenden, um Clustered-Indizes zu löschen oder neu zu erstellen, werden auch alle nicht-Clustered-Indizes neu erstellt, was einen großen Performance-Erfolg bedeuten kann.
IF EXISTS (SELECT * FROM sys.indexes AS si WHERE si.name = 'NCI_Employee_TelphoneGender') DROP INDEX NCI_Employee_TelphoneGender ON Employee; GO CREATE INDEX NCI_Employee_TelphoneGender ON Mitarbeiter (Telefon, Geschlecht, Land) GO
Zusammenfassung
Wir haben gesehen, wie DROP_EXISTING = ON in SQL Server uns hilft, mit Clustered-Index die Leistung mit wenigen Beispielen zu verbessern. DROP_EXISTING = ON kann nur mit dem bereits erstellten Index verwendet werden, um deren Spezifikationen zu ändern.