Après avoir présenté dans deux précédents articles les Heaps et les Index Clustered, ce dernier article de la série va détailler ce qu’est un index non clustered.
A la différence des index clustered, les index non clustered ne contiennent pas l’ensemble des colonnes de la table mais seulement une ou plusieurs colonnes, constituant la clé.
Cet index est aussi stocké sous forme d’un arbre balancé (de même que pour l’index clustered) et permet pour les différentes valeurs de la clé d’atteindre l’endroit où sont stockées l’ensemble des colonnes, c’est-à-dire le Heap ou l’Index clustered.
Pour cela, dans les pages feuilles de l’index non clustered, on retrouve les valeurs de la clé complétées selon le cas :
– du RowId de l’enregistrement correspondant dans le Heap s’il n’existe pas d’index clustered pour la table.
CREATE TABLE TableSansClustered (Id int identity, Nom varchar(50), Prenom varchar(50), DateNaissance date, Commentaires varchar(MAX) DEFAULT 'Pas de commentaires')
INSERT INTO TableSansClustered (Nom, Prenom, DateNaissance)
VALUES ('DUPONT','Pierre','19871029'),
('DURAND','Cédric','20010412'),
('SCHMIDT','Paul','19990305')
CREATE INDEX IX_NomPrenom ON TableSansClustered(Nom,Prenom)
Le contenu de la page feuille de cet index non clustered peut être résumé de la manière suivante :
On voit donc pour chaque ligne la présence des deux colonnes de la clé (Nom et Prenom) ainsi que du l’identifiant de ligne (Row ID = RID) du segment de données.
– de la clé de l’Index Clustered s’il en existe un pour la table.
CREATE TABLE TableAvecClustered (Id int identity PRIMARY KEY, Nom varchar(50), Prenom varchar(50), DateNaissance date, Commentaires varchar(MAX) DEFAULT 'Pas de commentaires')
INSERT INTO TableAvecClustered (Nom, Prenom, DateNaissance)
VALUES ('DUPONT','Pierre','19871029'),
('DURAND','Cédric','20010412'),
('SCHMIDT','Paul','19990305')
CREATE INDEX IX_NomPrenom ON TableAvecClustered(Nom,Prenom)
Le contenu de la page feuille de cette index non clustered peut être résumé de la manière suivante :
On voit pour chaque ligne que l’on a la clé de l’index non clustered et aussi la clé de l’index clustered (dans notre cas la colonne Id, qui est Primary Key et par défaut index clustered). A noter au passage que si l’index clustered avait eu des colonnes en commun avec l’index non clustered, seules les nouvelles colonnes auraient été répétées.
Lorsqu’une requête passe par l’index non clustered, elle peut ainsi renvoyer l’ensemble des colonnes de l’index non clustered, plus le cas échéant l’ensemble des colonnes de l’index clustered, sans avoir besoin d’aller chercher plus loin.
select Nom, Prenom FROM TableSansClustered where Nom='DURAND'
select Nom, Prenom, Id FROM TableAvecClustered where Nom='DURAND'
Maintenant, si la requête retourne une ou plusieurs colonnes qui ne font pas partie du contenu des feuilles de l’index non cllustered, et si l’on cherche néanmoins à forcer un passage par cet index, alors le moteur aura besoin d’aller chercher les colonnes manquantes dans l’index clustered s’il existe ou à défaut dans le heap, c’est-à-dire là où sont stockées toutes les colonnes.
Ainsi, on voit que pour la table sans index clustered, une opération de RowId lookup est nécessaire pour, à partir du RowId présent dans les feuilles de l’index clustered, obtenir le contenu de la colonne DateNaissance.
Et pour la table avec l’index clustered, c’est une opération de Key lookup qui permet, à partir de la clé de l’index clustered, d’obtenir la colonne manquante.
A noter depuis SQL Server 2005 la notion de colonne incluse permet d’ajouter une ou plusieurs colonnes uniquement au niveau feuille de l’index non clustered, sans que celles-ci n’aient à être présentes sur l’ensemble de l’arbre d’index. C’est notamment utile lorsqu’une colonne prend trop d’espace (longues chaînes de caractères par exemple) ou est d’un type de données non triable (XML, type CLR, …). On peut ainsi obtenir des index dits « couvrants » pour telle ou telle requête, c’est-à-dire que le contenu de leurs feuilles est suffisant pour obtenir l’ensemble des colonnes requises pour satisfaire aux besoins de la requête.
CREATE INDEX IX_NomPrenom_DateNaissanceIncluse ON TableAvecClustered(Nom,Prenom) INCLUDE (DateNaissance)
Cet index est couvrant pour la requête suivante :
select Nom, Prenom, Id,DateNaissance FROM TableAvecClustered where Nom='DURAND'
Bien entendu, cette notion de colonne incluse n’a pas de sens pour un index non-clustered, puisqu’un tel index contient déjà par définition toutes les colonnes au niveau de ses feuilles.
Enfin, SQL Server propose à partir de sa version 2008 la notion d’index filtré, qui permet de sortir d’un index les colonnes qui ne sont pas nécessaires d’un point de vue fonctionnel.
Par exemple, dans l’exemple courant, on peut décider de s’attacher uniquement aux personnes nées le siècle dernier.
CREATE INDEX IX_20emeSiecle ON TableAvecClustered(Nom,Prenom) WHERE DateNaissance<'20000101'
La page feuille de cet index ne contient que les enregistrements correspondant aux personnes nées le siècle dernier :
Mais ces informations sont suffisantes pour les requêtes ne ciblant que des enregistrements parmi ce sous-groupe.
select Nom, Prenom, Id FROM TableAvecClustered WHERE DateNaissance<'20000101' AND Nom='DUPONT'
N’hésitez pas à utiliser des index pour optimiser certaines de vos requêtes de sélection, mais gardez toutefois bien en tête qu’ils sont maintenus en direct par le système, et donc qu’à ce titre ils peuvent nettement ralentir les requêtes de modification des données. Il conviendra donc de trouver le juste milieu …