La création d'un index dans SQL Server est une tâche moyennement simple qui améliore les performances de la requête. Nous pouvons créer un index en cluster ainsi qu'un index non en cluster qui peut être unique ou non unique selon les besoins.
Si nous avons déjà créé un index sur une table, toutefois, en raison d'un changement d'exigence, nous devons ajouter / supprimer une ou plusieurs colonnes à un index existant. Dans ce cas, nous pouvons utiliser le DROP_EXISTING option sans écrire la ligne de code supplémentaire pour supprimer l’index existant d’abord, puis le recréer. (Il s'agit d'un style de codage à l'ancienne: supprimer si existant et créer un nouveau.)
Avec DROP_EXISTING = ON, SQL Server supprimera et reconstruira l’index existant en cluster / non-cluster avec les spécifications d’index modifiées, en conservant le nom de l’index tel qu’il existe. Nous pouvons ajouter / supprimer des colonnes, modifier l'ordre de tri ou modifier un groupe de fichiers.
DROP_EXISTING est désactivé par défautet cela ne fonctionne pas si vous incluez cette option lors de la création d'un nouvel index pour la première fois. DROP_EXISTING = ON dans SQL Server ne fonctionne que si vous avez déjà un index avec le nom spécifié dans une table.
Avec DROP_EXISTING, nous pouvons procéder comme suit:
1. La modification d'un index non clusterisé en un index clusterisé est autorisée.
Avec DROP_EXISTING, nous ne pouvons pas effectuer les opérations suivantes:
1. La modification d'un index clusterisé en un autre type d'index n'est pas autorisée.
Avantages de l'utilisation de DROP_EXISTING = ON
Si nous recréons un index clusterisé sur une table, qui a déjà un index non clusterisé, en utilisant DROP_EXISTING = ON dans SQL Server lors de la recréation d'un index en cluster présente les avantages suivants:
• Les index non clusterisés ne sont pas reconstruits à nouveau. Si nous recréons un index en cluster avec DROP_EXISTING = ON dans SQL Server, il sait que la clé de cluster n'a pas été modifiée dans l'index non en cluster. Cela permet de gagner beaucoup de temps. Chaque index non-cluster a une clé de cluster qui fait référence à l'index cluster. Si un index en cluster est reconstruit, SQL Server doit également modifier l'index non en cluster. Cependant, l’utilisation de DROP_EXISTING = ON est intéressante car nous n’avons pas besoin de reconstruire à nouveau les index non clusterisés.
• Si nous utilisons l'ancienne méthode pour supprimer et recréer un index en cluster, tous les index non en cluster de cette table sont à nouveau reconstruits pour correspondre à la clé de cluster. Cela peut être enregistré en utilisant DROP_EXISTING = ON avec index clusterisé.
Syntaxe de création d'index avec DROP_EXISTING
CREATE [Unique] [Clustered | Non Clustered] INDEX Index_Name ON (Nom de la colonne / s) WITH DROP_EXISTING = {ON | DE}; Exemples
Créez un exemple de table, Employé.
SI OBJECT_ID ('Employee', 'U') N'EST PAS NULL DROP TABLE Employee; CREATE TABLE Employé (Id INT PRIMARY KEY, Nom VARCHAR (50), Téléphone VARCHAR (10), Sexe CHAR (1), Pays VARCHAR (100), Salaire DECIMAL (18,2)); 1. DROP_EXISTING = ON échoue lors de la création d'un nouvel index
Dans l’exemple suivant, l’option DROP_EXISTING = ON essaie de trouver un index existant avec NCI_Employee_TelphoneGender nom, car la création de l'index échoue avec le message d'erreur suivant.
CREATE INDEXCLUSTERED INDEX NCI_Employee_TelphoneGender ON employé (téléphone, sexe) WITH (DROP_EXISTING = ON);
Msg 7999, niveau 16, état 9, ligne 1
Impossible de trouver un index nommé "NCI_Employee_TelphoneGender" pour la table "Employé".
Créer un index
CREATE INDEX CLUSTERED INDEX NCI_Employee_TelphoneGender ON employé (téléphone, sexe);
Requête pour voir la liste des colonnes dans un index
La requête ci-dessous montre que les colonnes Téléphone et Sexe ont été incluses dans l'index non-cluster créé.
sélectionnez name en tant que ColumnListInIndex à partir de sys.index_columns en tant que ic joint interne sys.columns c on ic.column_id = c.column_id et ic.object_id = c.object_id où ic.object_id = (sélectionnez object_id à partir de sys.indexes où nom = 'NCI_Employee_Telphoneender et type_desc = 'NONCLUSTERED') et ic.index_id = 2;
2. DROP_EXISTING = ON dans SQL Server Works sur l'index existant
Dans l'exemple ci-dessus, nous avons créé un INDEX, NCI_Employee_TelphoneGender dans la colonne Téléphone et genre. Maintenant, en raison d'une modification de l'exigence, nous devons également inclure une colonne Pays dans l'index non clusterisé existant. Nous pouvons y parvenir en utilisant l’option DROP_EXISTING = ON.
Dans l'exemple ci-dessous, avec DROP_EXISTING = ON, SQL Server supprime d'abord l'index spécifié et recrée un index avec le même nom et la colonne Pays inclus.
CREATE INDEXCLUSTERED INDEX NCI_Employee_TelphoneGender ON employé (téléphone, sexe, pays) WITH (DROP_EXISTING = ON);
Ancienne méthode pour écrire le code ci-dessus
Le code ci-dessus avec DROP_EXISTING = ON remplace une ancienne méthode de suppression et de recréation d'un index. Si nous utilisons le code ci-dessous pour supprimer / recréer un index en cluster, il reconstruira également tous les index non en cluster, ce qui pourrait avoir un impact important sur les performances.
SI EXISTE (SELECT * FROM sys.indexes AS WHERE si.name = 'NCI_Employee_TelphoneGender') DROP INDEX NCI_Employee_TelphoneGender ON Employee; GO CREATE INDEX NCI_Employee_TelphoneGender ON Employé (Téléphone, Sexe, Pays) GO
Résumé
Nous avons vu comment DROP_EXISTING = ON dans SQL Server nous aide avec un index clusterisé à améliorer les performances avec quelques exemples, et DROP_EXISTING = ON ne peut être utilisé qu'avec un index créé existant afin de modifier leurs spécifications.