Les index sont utilisés pour accélérer le processus de requête dans SQL Server, ce qui entraîne des performance. Ils sont similaires aux index de manuels. Dans les manuels, si vous avez besoin d’accéder à un chapitre particulier, vous allez à l’index, trouvez le numéro de page du chapitre et allez directement à cette page. Sans index, le processus de recherche du chapitre souhaité aurait été très lent.

La même chose s’applique aux index des bases de données. Sans index, un SGBD doit parcourir tous les enregistrements de la table pour récupérer les résultats souhaités. Ce processus est appelé analyse de table et est extrêmement lent. En revanche, si vous créez des index, la base de données accède d’abord à cet index, puis récupère directement les enregistrements de table correspondants.

Il existe deux types d’index dans SQL Server:

1. Index en cluster
2. Index non en cluster

Index clusterisé

Un index clusterisé définit l’ordre dans lequel les données sont physiquement stockées dans une table. Les données de table peuvent être triées de manière unique, par conséquent, il ne peut y avoir qu’un seul index cluster par table. Dans SQL Server, la contrainte de clé primaire crée automatiquement un index clusterisé sur cette colonne particulière.

Jetons un œil. Tout d’abord, créez une table « student » dans « schooldb » en exécutant le script suivant, ou assurez-vous que votre base de données est entièrement sauvegardée si vous utilisez vos données en direct:

Remarque ici, dans le tableau « étudiant », nous avons défini prima contrainte de clé ry sur la colonne « id ». Cela crée automatiquement un index clusterisé sur la colonne « id ». Pour voir tous les index d’une table particulière, exécutez la procédure stockée « sp_helpindex ». Cette procédure stockée accepte le nom de la table en tant que paramètre et récupère tous les index de la table. La requête suivante récupère les index créés sur la table des élèves.

La requête ci-dessus renverra ce résultat:

Dans la sortie, vous pouvez voir le seul index. Il s’agit de l’index qui a été automatiquement créé en raison de la contrainte de clé primaire sur la colonne « id ».

Cet index cluster stocke l’enregistrement dans la table des étudiants dans l’ordre croissant de « id ». Par conséquent, si l’enregistrement inséré a l’ID de 5, l’enregistrement sera inséré dans la 5ème ligne du tableau au lieu de la première ligne. De même, si le quatrième enregistrement a un identifiant de 3, il sera inséré dans la troisième ligne au lieu de la quatrième ligne. En effet, l’index clusterisé doit maintenir l’ordre physique des enregistrements stockés en fonction de la colonne indexée, c’est-à-dire id. Pour voir cet ordre en action, exécutez le script suivant:

Le script ci-dessus insère dix enregistrements dans la table des élèves. Notez ici que les enregistrements sont insérés dans l’ordre aléatoire des valeurs de la colonne « id ». Mais en raison de l’index cluster par défaut sur la colonne id, les enregistrements sont physiquement stockés dans l’ordre croissant des valeurs de la colonne « id ». Exécutez l’instruction SELECT suivante pour récupérer les enregistrements de la table Student.

Les enregistrements seront récupérés dans l’ordre suivant:

Création d’un index clusterisé personnalisé

Vous pouvez créer votre propre index personnalisé ainsi que l’index clusterisé par défaut. Pour créer un nouvel index clusterisé sur une table, vous devez d’abord supprimer l’index précédent.

Pour supprimer un index, allez dans « Explorateur d’objets- > Bases de données- > Database_Name- > Tables- > Table_Name – > Index». Cliquez avec le bouton droit sur l’index que vous voulez supprimer et sélectionnez SUPPRIMER . Voir la capture d’écran ci-dessous.

Maintenant, pour créer un nouvel index clusterisé, exécutez le script suivant:

Le processus de création Un index clusterisé est similaire à un index normal à une exception près. Avec l’index clusterisé, vous devez utiliser le mot-clé « CLUSTERED » avant « INDEX ».

Le script ci-dessus crée un index clusterisé nommé « IX_tblStudent_Gender_Score » sur la table Student. Cet index est créé sur les colonnes « gender » et « total_score ». Un index créé sur plusieurs colonnes est appelé « index composé ».

L’index ci-dessus trie d’abord tous les enregistrements dans l’ordre croissant du sexe. Si le sexe est le même pour deux enregistrements ou plus, les enregistrements sont triés dans l’ordre décroissant des valeurs de leur colonne « total_score ». Vous pouvez également créer un index clusterisé sur une seule colonne. Désormais, si vous sélectionnez tous les enregistrements dans la table student, ils seront récupérés dans l’ordre suivant:

Index non groupés

Un index non groupé ne trie pas les données physiques à l’intérieur de la table. l’index non clusterisé est stocké à un endroit et les données de la table sont stockées à un autre emplacement. Ceci est similaire à un manuel où le contenu du livre est situé à un endroit et l’index est situé à un autre. Cela permet plusieurs éléments non groupés index par table.

Il est important de mentionner ici qu’à l’intérieur de la table, les données seront triées par un index clusterisé. Cependant, à l’intérieur de l’index non groupé, les données sont stockées dans l’ordre spécifié. L’index contient valeurs de colonne sur lesquelles l’index est créé et adresse de l’enregistrement auquel appartient la valeur de colonne.

Lorsqu’une requête est émise sur une colonne sur laquelle l’index est créé, la base de données ira d’abord dans l’index et cherchera l’adresse de la ligne correspondante dans la table. Il ira ensuite à cette adresse de ligne et récupérera d’autres valeurs de colonne. C’est en raison de cette étape supplémentaire que les index non clusterisés sont plus lents que les index clusterisés.

Création d’un index non clusterisé

La syntaxe de création d’un index non clusterisé est similaire à celui de l’index clusterisé. Cependant, dans le cas d’un index non clusterisé, le mot clé « NONCLUSTERED » est utilisé à la place de « CLUSTERED ». Jetez un œil au script suivant.

Le script ci-dessus crée un index non clusterisé sur la colonne « name » de la table Student. L’index est trié par nom par ordre croissant. Comme nous l’avons dit précédemment, les données et l’index de la table seront stockés à des endroits différents. Les enregistrements de la table seront triés selon un index clusterisé s’il y en a un. L’index sera trié selon sa définition et sera stocké séparément de la table.

Données de la table des étudiants:

IX_tblStudent_Name Index Data

Avis , ici, dans l’index, chaque ligne a une colonne qui stocke l’adresse de la ligne à laquelle appartient le nom. Ainsi, si une requête est émise pour récupérer le sexe et la date de naissance de l’étudiant nommé «Jon», la base de données recherchera d’abord le nom «Jon» dans l’index. Il lira ensuite l’adresse de la ligne «Jon» et ira directement à cette ligne dans la table «étudiant» pour récupérer le sexe et la date de naissance de Jon.

Conclusion

De la discussion nous trouvons les différences suivantes entre les index clusterisés et non clusterisés.

1. Il ne peut y avoir qu’un seul index clusterisé par table. Cependant, vous pouvez créer plusieurs index non clusterisés sur une seule table.
2. Les index clusterisés trient uniquement les tables. Par conséquent, ils ne consomment pas de stockage supplémentaire. Les index non groupés sont stockés dans un endroit distinct de la table réelle, réclamant plus d’espace de stockage.
3. Les index groupés sont plus rapides que les index non groupés car ils n’impliquent aucune étape de recherche supplémentaire.

Autres excellents articles de Ben

Différence entre identité & Séquence dans SQL Server

Quelle est la différence entre les index en cluster et non en cluster dans SQL Server?

• Auteur
• Messages récents

Ben Richardson dirige Acuity Training, l’un des principaux fournisseurs de formation SQL au Royaume-Uni. Il propose une gamme complète de formations SQL, des cours d’introduction aux formations avancées d’administration et d’entrepôt de données – voir ici pour plus de détails. Acuity a des bureaux à Londres et à Guildford, dans le Surrey. Il blogue également occasionnellement sur le blog d’Acuity
Voir tous les articles de Ben Richardson

Derniers articles de Ben Richardson (tout voir)

• Power BI: Waterfall Charts and Combined Visuals – 19 janvier 2021
• Power BI : Mise en forme conditionnelle et couleurs des données en action – 14 janvier 2021
• Power BI: importation de données depuis SQL Server et MySQL – 12 janvier 2021

573 279 vues