{"id":352,"date":"2012-03-18T00:00:52","date_gmt":"2012-03-18T00:00:52","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=352"},"modified":"2026-05-02T14:31:17","modified_gmt":"2026-05-02T12:31:17","slug":"stockage-des-donnees-et-index-partie-3-index-nonclustered","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/stockage-des-donnees-et-index-partie-3-index-nonclustered\/","title":{"rendered":"Stockage des donn\u00e9es et index – Partie 3 : Index NonClustered"},"content":{"rendered":"

Apr\u00e8s avoir pr\u00e9sent\u00e9 dans deux pr\u00e9c\u00e9dents articles les Heaps<\/a> et les Index Clustered<\/a>, ce dernier article de la s\u00e9rie va d\u00e9tailler ce qu\u2019est un index non clustered.
\nA la diff\u00e9rence des index clustered, les index non clustered ne contiennent pas l\u2019ensemble des colonnes de la table mais seulement une ou plusieurs colonnes, constituant la cl\u00e9.
\nCet index est aussi stock\u00e9 sous forme d\u2019un arbre balanc\u00e9 (de m\u00eame que pour l\u2019index clustered<\/a>) et permet pour les diff\u00e9rentes valeurs de la cl\u00e9 d\u2019atteindre l\u2019endroit o\u00f9 sont stock\u00e9es l\u2019ensemble des colonnes, c\u2019est-\u00e0-dire le Heap<\/a> ou l\u2019Index clustered<\/a>.
\nPour cela, dans les pages feuilles de l\u2019index non clustered, on retrouve les valeurs de la cl\u00e9 compl\u00e9t\u00e9es selon le cas :<\/p>\n

– du RowId de l\u2019enregistrement correspondant dans le Heap s\u2019il n\u2019existe pas d\u2019index clustered pour la table.<\/p>\n

CREATE TABLE TableSansClustered (Id int identity, Nom varchar(50), Prenom varchar(50), DateNaissance date, Commentaires varchar(MAX) DEFAULT 'Pas de commentaires')\r\n\r\nINSERT INTO TableSansClustered (Nom, Prenom, DateNaissance)\r\nVALUES ('DUPONT','Pierre','19871029'),\r\n\t\t('DURAND','C\u00e9dric','20010412'),\r\n\t\t('SCHMIDT','Paul','19990305')\r\n\r\nCREATE INDEX IX_NomPrenom ON TableSansClustered(Nom,Prenom)<\/pre>\n
Le contenu de la page feuille de cet index non clustered peut \u00eatre r\u00e9sum\u00e9 de la mani\u00e8re suivante :<\/p>\n
\"\"<\/a><\/p>\n
On voit donc pour chaque ligne la pr\u00e9sence des deux colonnes de la cl\u00e9 (Nom et Prenom) ainsi que du l\u2019identifiant de ligne (Row ID = RID) du segment de donn\u00e9es.<\/p>\n
– de la cl\u00e9 de l\u2019Index Clustered s\u2019il en existe un pour la table.<\/p>\n
CREATE TABLE TableAvecClustered (Id int identity PRIMARY KEY, Nom varchar(50), Prenom varchar(50), DateNaissance date, Commentaires varchar(MAX) DEFAULT 'Pas de commentaires')\r\n\r\nINSERT INTO TableAvecClustered (Nom, Prenom, DateNaissance)\r\nVALUES ('DUPONT','Pierre','19871029'),\r\n\t\t('DURAND','C\u00e9dric','20010412'),\r\n\t\t('SCHMIDT','Paul','19990305')\r\n\r\nCREATE INDEX IX_NomPrenom ON TableAvecClustered(Nom,Prenom)<\/pre>\n
Le contenu de la page feuille de cette index non clustered peut \u00eatre r\u00e9sum\u00e9 de la mani\u00e8re suivante :<\/p>\n
\"\"<\/a><\/p>\n
On voit pour chaque ligne que l\u2019on a la cl\u00e9 de l\u2019index non clustered et aussi la cl\u00e9 de l\u2019index clustered (dans notre cas la colonne Id, qui est Primary Key et par d\u00e9faut index clustered). A noter au passage que si l\u2019index clustered avait eu des colonnes en commun avec l\u2019index non clustered, seules les nouvelles colonnes auraient \u00e9t\u00e9 r\u00e9p\u00e9t\u00e9es.<\/p>\n
Lorsqu\u2019une requ\u00eate passe par l\u2019index non clustered, elle peut ainsi renvoyer l\u2019ensemble des colonnes de l\u2019index non clustered, plus le cas \u00e9ch\u00e9ant l\u2019ensemble des colonnes de l\u2019index clustered, sans avoir besoin d\u2019aller chercher plus loin.<\/p>\n
select Nom, Prenom\r\nFROM TableSansClustered\r\nwhere Nom='DURAND'<\/pre>\n
\"\"<\/a><\/p>\n
select Nom, Prenom, Id\r\nFROM TableAvecClustered\r\nwhere Nom='DURAND'<\/pre>\n
\"\"<\/a><\/p>\n
Maintenant, si la requ\u00eate retourne une ou plusieurs colonnes qui ne font pas partie du contenu des feuilles de l\u2019index non cllustered, et si l\u2019on cherche n\u00e9anmoins \u00e0 forcer un passage par cet index, alors le moteur aura besoin d\u2019aller chercher les colonnes manquantes dans l\u2019index clustered s\u2019il existe ou \u00e0 d\u00e9faut dans le heap, c\u2019est-\u00e0-dire l\u00e0 o\u00f9 sont stock\u00e9es toutes les colonnes.
\nAinsi, on voit que pour la table sans index clustered, une op\u00e9ration de RowId lookup est n\u00e9cessaire pour, \u00e0 partir du RowId pr\u00e9sent dans les feuilles de l\u2019index clustered, obtenir le contenu de la colonne DateNaissance.
\n\"\"<\/a>
\nEt pour la table avec l\u2019index clustered, c\u2019est une op\u00e9ration de Key lookup qui permet, \u00e0 partir de la cl\u00e9 de l\u2019index clustered, d\u2019obtenir la colonne manquante.<\/p>\n
\"\"<\/a><\/p>\n
A noter depuis SQL Server 2005 la notion de colonne incluse permet d\u2019ajouter une ou plusieurs colonnes uniquement au niveau feuille de l\u2019index non clustered, sans que celles-ci n\u2019aient \u00e0 \u00eatre pr\u00e9sentes sur l\u2019ensemble de l\u2019arbre d\u2019index. C\u2019est notamment utile lorsqu\u2019une colonne prend trop d\u2019espace (longues cha\u00eenes de caract\u00e8res par exemple) ou est d\u2019un type de donn\u00e9es non triable (XML, type CLR, \u2026). On peut ainsi obtenir des index dits \u00ab couvrants \u00bb pour telle ou telle requ\u00eate, c\u2019est-\u00e0-dire que le contenu de leurs feuilles est suffisant pour obtenir l\u2019ensemble des colonnes requises pour satisfaire aux besoins de la requ\u00eate.<\/p>\n
CREATE INDEX IX_NomPrenom_DateNaissanceIncluse\r\nON TableAvecClustered(Nom,Prenom)\r\nINCLUDE (DateNaissance)<\/pre>\n
Cet index est couvrant pour la requ\u00eate suivante :<\/p>\n
select Nom, Prenom, Id,DateNaissance\r\nFROM TableAvecClustered\r\nwhere Nom='DURAND'<\/pre>\n
\"\"<\/a><\/p>\n
Bien entendu, cette notion de colonne incluse n\u2019a pas de sens pour un index non-clustered, puisqu\u2019un tel index contient d\u00e9j\u00e0 par d\u00e9finition toutes les colonnes au niveau de ses feuilles.<\/p>\n
Enfin, SQL Server propose \u00e0 partir de sa version 2008 la notion d\u2019index filtr\u00e9, qui permet de sortir d\u2019un index les colonnes qui ne sont pas n\u00e9cessaires d\u2019un point de vue fonctionnel.
\nPar exemple, dans l\u2019exemple courant, on peut d\u00e9cider de s\u2019attacher uniquement aux personnes n\u00e9es le si\u00e8cle dernier.<\/p>\n
CREATE INDEX IX_20emeSiecle\r\nON TableAvecClustered(Nom,Prenom)\r\nWHERE DateNaissance<'20000101'<\/pre>\n
La page feuille de cet index ne contient que les enregistrements correspondant aux personnes n\u00e9es le si\u00e8cle dernier :<\/p>\n
\"\"<\/a><\/p>\n
Mais ces informations sont suffisantes pour les requ\u00eates ne ciblant que des enregistrements parmi ce sous-groupe.<\/p>\n
select Nom, Prenom, Id\r\nFROM TableAvecClustered\r\nWHERE DateNaissance<'20000101'\r\nAND Nom='DUPONT'<\/pre>\n
\"\"<\/a><\/p>\n
N\u2019h\u00e9sitez pas \u00e0 utiliser des index pour optimiser certaines de vos requ\u00eates de s\u00e9lection, mais gardez toutefois bien en t\u00eate qu\u2019ils sont maintenus en direct par le syst\u00e8me, et donc qu\u2019\u00e0 ce titre ils peuvent nettement ralentir les requ\u00eates de modification des donn\u00e9es. Il conviendra donc de trouver le juste milieu \u2026<\/p>\n","protected":false},"excerpt":{"rendered":"
Apr\u00e8s avoir pr\u00e9sent\u00e9 dans deux pr\u00e9c\u00e9dents articles les Heaps et les Index Clustered, ce dernier article de la s\u00e9rie va d\u00e9tailler ce qu\u2019est un index non clustered.<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-352","post","type-post","status-publish","format-standard","hentry","category-article_sql"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/352","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=352"}],"version-history":[{"count":26,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/352\/revisions"}],"predecessor-version":[{"id":1959,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/352\/revisions\/1959"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=352"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=352"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=352"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}