{"id":208,"date":"2012-01-22T00:00:58","date_gmt":"2012-01-22T00:00:58","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=208"},"modified":"2026-05-02T14:31:17","modified_gmt":"2026-05-02T12:31:17","slug":"stockage-des-donnees-et-index-partie-2-index-clustered","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/stockage-des-donnees-et-index-partie-2-index-clustered\/","title":{"rendered":"Stockage des donn\u00e9es et index &#8211; Partie 2 : Index Clustered"},"content":{"rendered":"<p>Parmi les fondamentaux de SQL Server figure la notion d\u2019Index Clustered, souvent mal comprise par les d\u00e9butants. Dans ce deuxi\u00e8me billet concernant l\u2019organisation des donn\u00e9es, nous chercherons \u00e0 comprendre ce qu\u2019est un index clustered.<!--more--><\/p>\n<p>Tout d\u2019abord, pr\u00e9sentons la notion d\u2019index. Un index s\u2019appuie sur une ou plusieurs colonnes, constituant la cl\u00e9. Au niveau final de l\u2019index, les diff\u00e9rents enregistrements sont class\u00e9s suivant la cl\u00e9. Pour cela, en partant d\u2019une page au niveau racine, et \u00e9ventuellement \u00e0 travers un certain nombre de niveaux interm\u00e9diaires, un arbre B (voir <a href=\"https:\/\/fr.wikipedia.org\/wiki\/Arbre_B\">https:\/\/fr.wikipedia.org\/wiki\/Arbre_B<\/a>). Ce type de structure permet, en consommant uniquement un nombre r\u00e9duit de lectures de pages, d\u2019arriver rapidement aux donn\u00e9es que l\u2019on cherche \u00e0 lire.<br \/>\nUn index Clustered est donc un arbre B qui va partir d\u2019une page racine et arriver au niveau des pages terminales, appel\u00e9es feuilles. Ces feuilles contiennent toutes les colonnes de chacun des enregistrements (je passerai volontairement ici outre les donn\u00e9es sorties des pages, comme peuvent l\u2019\u00eatre par exemple les donn\u00e9es de cha\u00eene ou binaires d\u2019une longueur sup\u00e9rieure \u00e0 8Ko).<br \/>\nPrenons dans un premier temps un exemple tr\u00e8s simple, avec seulement quelques enregistrements.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">create table TableDonnees (Id bigint identity, Donnee1 varchar(500), Donnee2 varchar(500))\r\n\r\ninsert into TableDonnees (Donnee1,Donnee2)\r\nvalues ('Court1A','Court2A'),\r\n\t\t('Court1X','Court2X'),\r\n\t\t('Court1B','Court2B'),\r\n\t\t('Court1C','Court2C'),\r\n\t\t('Court1Z','Court2Z')\r\n\r\ncreate unique clustered index Index_Clustered on TableDonnees(Id)<\/pre>\n<p>Voici le contenu de la page feuille de l\u2019index Clustered pour cette table :<br \/>\n<span style=\"font-size: x-small; color: #ff0000;\">Slot 0 Offset 0x60 Length 35<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span><br \/>\n<span style=\"font-size: x-small;\">Record Size = 35 <\/span><br \/>\n<span style=\"font-size: x-small;\">Memory Dump @0x000000000CFBA060<\/span><\/p>\n<p><span style=\"font-size: x-small;\">0000000000000000: 30000c00 01000000 00000000 03000002 \u20200&#8230;&#8230;&#8230;&#8230;&#8230; <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000010: 001c0023 00436f75 72743141 436f7572 \u2020&#8230;#.Court1ACour <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000020: 743241\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020t2A<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Id = 1<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 0 Column 2 Offset 0x15 Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee1 = Court1A<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 0 Column 3 Offset 0x1c Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee2 = Court2A<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 0 Offset 0x0 Length 0 Length (physical) 0<\/span><\/p>\n<p><span style=\"font-size: x-small;\">KeyHashValue = (1b7fe5b8af93) <\/span><br \/>\n<span style=\"font-size: x-small; color: #ff0000;\">Slot 1 Offset 0x83 Length 35<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span><br \/>\n<span style=\"font-size: x-small;\">Record Size = 35 <\/span><br \/>\n<span style=\"font-size: x-small;\">Memory Dump @0x000000000CFBA083<\/span><\/p>\n<p><span style=\"font-size: x-small;\">0000000000000000: 30000c00 02000000 00000000 03000002 \u20200&#8230;&#8230;&#8230;&#8230;&#8230; <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000010: 001c0023 00436f75 72743158 436f7572 \u2020&#8230;#.Court1XCour <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000020: 743258\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020t2X<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 1 Column 1 Offset 0x4 Length 8 Length (physical) 8<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Id = 2<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 1 Column 2 Offset 0x15 Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee1 = Court1X<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 1 Column 3 Offset 0x1c Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee2 = Court2X<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 1 Offset 0x0 Length 0 Length (physical) 0<\/span><\/p>\n<p><span style=\"font-size: x-small;\">KeyHashValue = (f03d7d8b0dcc) <\/span><br \/>\n<span style=\"font-size: x-small; color: #ff0000;\">Slot 2 Offset 0xa6 Length 35<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span><br \/>\n<span style=\"font-size: x-small;\">Record Size = 35 <\/span><br \/>\n<span style=\"font-size: x-small;\">Memory Dump @0x000000000CFBA0A6<\/span><\/p>\n<p><span style=\"font-size: x-small;\">0000000000000000: 30000c00 03000000 00000000 03000002 \u20200&#8230;&#8230;&#8230;&#8230;&#8230; <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000010: 001c0023 00436f75 72743142 436f7572 \u2020&#8230;#.Court1BCour <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000020: 743242\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020t2B<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 2 Column 1 Offset 0x4 Length 8 Length (physical) 8<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Id = 3<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 2 Column 2 Offset 0x15 Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee1 = Court1B<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 2 Column 3 Offset 0x1c Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee2 = Court2B<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 2 Offset 0x0 Length 0 Length (physical) 0<\/span><\/p>\n<p><span style=\"font-size: x-small;\">KeyHashValue = (a903f5656cf9) <\/span><br \/>\n<span style=\"font-size: x-small; color: #ff0000;\">Slot 3 Offset 0xc9 Length 35<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span><br \/>\n<span style=\"font-size: x-small;\">Record Size = 35 <\/span><br \/>\n<span style=\"font-size: x-small;\">Memory Dump @0x000000000CFBA0C9<\/span><\/p>\n<p><span style=\"font-size: x-small;\">0000000000000000: 30000c00 04000000 00000000 03000002 \u20200&#8230;&#8230;&#8230;&#8230;&#8230; <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000010: 001c0023 00436f75 72743143 436f7572 \u2020&#8230;#.Court1CCour <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000020: 743243\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020t2C<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 3 Column 1 Offset 0x4 Length 8 Length (physical) 8<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Id = 4<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 3 Column 2 Offset 0x15 Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee1 = Court1C<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 3 Column 3 Offset 0x1c Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee2 = Court2C<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 3 Offset 0x0 Length 0 Length (physical) 0<\/span><\/p>\n<p><span style=\"font-size: x-small;\">KeyHashValue = (cd59dedaa3da) <\/span><br \/>\n<span style=\"font-size: x-small; color: #ff0000;\">Slot 4 Offset 0xec Length 35<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span><br \/>\n<span style=\"font-size: x-small;\">Record Size = 35 <\/span><br \/>\n<span style=\"font-size: x-small;\">Memory Dump @0x000000000CFBA0EC<\/span><\/p>\n<p><span style=\"font-size: x-small;\">0000000000000000: 30000c00 05000000 00000000 03000002 \u20200&#8230;&#8230;&#8230;&#8230;&#8230; <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000010: 001c0023 00436f75 7274315a 436f7572 \u2020&#8230;#.Court1ZCour <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000020: 74325a\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020t2Z<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 4 Column 1 Offset 0x4 Length 8 Length (physical) 8<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Id = 5<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 4 Column 2 Offset 0x15 Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee1 = Court1Z<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 4 Column 3 Offset 0x1c Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee2 = Court2Z<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 4 Offset 0x0 Length 0 Length (physical) 0<\/span><\/p>\n<p><span style=\"font-size: x-small;\">KeyHashValue = (94675634c2ef)<\/span><\/p>\n<p><span style=\"font-size: small;\">Le principe de l\u2019index est que les donn\u00e9es sont toujours class\u00e9es par ordre. Ainsi, si l\u2019on rajoute une valeur, elle ne vient pas par d\u00e9faut se placer en fin de liste, mais va trouver sa place quitte \u00e0 d\u00e9caler les autres enregistrements.<\/span><\/p>\n<p><span style=\"font-size: small;\">set identity_insert TableDonnees on<\/span><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">insert into TableDonnees (Id,Donnee1,Donnee2)\r\n\tvalues (-1,'Court1-A','Court2-A')\r\nset identity_insert TableDonnees off<\/pre>\n<p><span style=\"font-size: small;\">On remarque bien que la valeur -1 est bien venue se placer sur le slot 0, et a de fait d\u00e9cal\u00e9 l\u2019ensemble des autres slots. A noter toutefois que ce d\u00e9calage n\u2019a eu lieu que sur les num\u00e9ros de slots (qui sont stock\u00e9s en fin de la page), mais que pour minimiser l\u2019impact, les donn\u00e9es en elles-m\u00eames n\u2019ont pas \u00e9t\u00e9 d\u00e9cal\u00e9es.<\/span><br \/>\n<span style=\"font-size: x-small; color: #ff0000;\">Slot 0 Offset 0x10f Length 37<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span><br \/>\n<span style=\"font-size: x-small;\">Record Size = 37 <\/span><br \/>\n<span style=\"font-size: x-small;\">Memory Dump @0x000000000FD5A10F<\/span><\/p>\n<p><span style=\"font-size: x-small;\">0000000000000000: 30000c00 ffffffff ffffffff 03000002 \u20200&#8230;\u00ff\u00ff\u00ff\u00ff\u00ff\u00ff\u00ff\u00ff&#8230;. <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000010: 001d0025 00436f75 7274312d 41436f75 \u2020&#8230;%.Court1-ACou <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000020: 7274322d 41\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020rt2-A<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Id = -1<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 0 Column 2 Offset 0x15 Length 8 Length (physical) 8<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee1 = Court1-A<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 0 Column 3 Offset 0x1d Length 8 Length (physical) 8<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee2 = Court2-A<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 0 Offset 0x0 Length 0 Length (physical) 0<\/span><\/p>\n<p><span style=\"font-size: x-small;\">KeyHashValue = (ffffffffffff) <\/span><br \/>\n<span style=\"font-size: x-small; color: #ff0000;\">Slot 1 Offset 0x60 Length 35<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span><br \/>\n<span style=\"font-size: x-small;\">Record Size = 35 <\/span><br \/>\n<span style=\"font-size: x-small;\">Memory Dump @0x000000000FD5A060<\/span><\/p>\n<p><span style=\"font-size: x-small;\">0000000000000000: 30000c00 01000000 00000000 03000002 \u20200&#8230;&#8230;&#8230;&#8230;&#8230; <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000010: 001c0023 00436f75 72743141 436f7572 \u2020&#8230;#.Court1ACour <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000020: 743241\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020t2A<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 1 Column 1 Offset 0x4 Length 8 Length (physical) 8<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Id = 1<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 1 Column 2 Offset 0x15 Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee1 = Court1A<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 1 Column 3 Offset 0x1c Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee2 = Court2A<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 1 Offset 0x0 Length 0 Length (physical) 0<\/span><\/p>\n<p><span style=\"font-size: x-small;\">KeyHashValue = (1b7fe5b8af93) <\/span><br \/>\n<span style=\"font-size: x-small; color: #ff0000;\">Slot 2 Offset 0x83 Length 35<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span><br \/>\n<span style=\"font-size: x-small;\">Record Size = 35 <\/span><br \/>\n<span style=\"font-size: x-small;\">Memory Dump @0x000000000FD5A083<\/span><\/p>\n<p><span style=\"font-size: x-small;\">0000000000000000: 30000c00 02000000 00000000 03000002 \u20200&#8230;&#8230;&#8230;&#8230;&#8230; <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000010: 001c0023 00436f75 72743158 436f7572 \u2020&#8230;#.Court1XCour <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000020: 743258\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020t2X<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 2 Column 1 Offset 0x4 Length 8 Length (physical) 8<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Id = 2<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 2 Column 2 Offset 0x15 Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee1 = Court1X<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 2 Column 3 Offset 0x1c Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee2 = Court2X<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 2 Offset 0x0 Length 0 Length (physical) 0<\/span><\/p>\n<p><span style=\"font-size: x-small;\">KeyHashValue = (f03d7d8b0dcc) <\/span><br \/>\n<span style=\"font-size: x-small; color: #ff0000;\">Slot 3 Offset 0xa6 Length 35<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span><br \/>\n<span style=\"font-size: x-small;\">Record Size = 35 <\/span><br \/>\n<span style=\"font-size: x-small;\">Memory Dump @0x000000000FD5A0A6<\/span><\/p>\n<p><span style=\"font-size: x-small;\">0000000000000000: 30000c00 03000000 00000000 03000002 \u20200&#8230;&#8230;&#8230;&#8230;&#8230; <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000010: 001c0023 00436f75 72743142 436f7572 \u2020&#8230;#.Court1BCour <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000020: 743242\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020t2B<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 3 Column 1 Offset 0x4 Length 8 Length (physical) 8<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Id = 3<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 3 Column 2 Offset 0x15 Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee1 = Court1B<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 3 Column 3 Offset 0x1c Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee2 = Court2B<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 3 Offset 0x0 Length 0 Length (physical) 0<\/span><\/p>\n<p><span style=\"font-size: x-small;\">KeyHashValue = (a903f5656cf9) <\/span><br \/>\n<span style=\"font-size: x-small; color: #ff0000;\">Slot 4 Offset 0xc9 Length 35<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span><br \/>\n<span style=\"font-size: x-small;\">Record Size = 35 <\/span><br \/>\n<span style=\"font-size: x-small;\">Memory Dump @0x000000000FD5A0C9<\/span><\/p>\n<p><span style=\"font-size: x-small;\">0000000000000000: 30000c00 04000000 00000000 03000002 \u20200&#8230;&#8230;&#8230;&#8230;&#8230; <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000010: 001c0023 00436f75 72743143 436f7572 \u2020&#8230;#.Court1CCour <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000020: 743243\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020t2C<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 4 Column 1 Offset 0x4 Length 8 Length (physical) 8<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Id = 4<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 4 Column 2 Offset 0x15 Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee1 = Court1C<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 4 Column 3 Offset 0x1c Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee2 = Court2C<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 4 Offset 0x0 Length 0 Length (physical) 0<\/span><\/p>\n<p><span style=\"font-size: x-small;\">KeyHashValue = (cd59dedaa3da) <\/span><br \/>\n<span style=\"font-size: x-small; color: #ff0000;\">Slot 5 Offset 0xec Length 35<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span><br \/>\n<span style=\"font-size: x-small;\">Record Size = 35 <\/span><br \/>\n<span style=\"font-size: x-small;\">Memory Dump @0x000000000FD5A0EC<\/span><\/p>\n<p><span style=\"font-size: x-small;\">0000000000000000: 30000c00 05000000 00000000 03000002 \u20200&#8230;&#8230;&#8230;&#8230;&#8230; <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000010: 001c0023 00436f75 7274315a 436f7572 \u2020&#8230;#.Court1ZCour <\/span><br \/>\n<span style=\"font-size: x-small;\">0000000000000020: 74325a\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020t2Z<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 5 Column 1 Offset 0x4 Length 8 Length (physical) 8<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Id = 5<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 5 Column 2 Offset 0x15 Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee1 = Court1Z<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 5 Column 3 Offset 0x1c Length 7 Length (physical) 7<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Donnee2 = Court2Z<\/span><\/p>\n<p><span style=\"font-size: x-small;\">Slot 5 Offset 0x0 Length 0 Length (physical) 0<\/span><\/p>\n<p><span style=\"font-size: x-small;\">KeyHashValue = (94675634c2ef)<\/span><\/p>\n<p><span style=\"font-size: small;\">De m\u00eame, si l\u2019on enl\u00e8ve des enregistrements, les slots seront renomm\u00e9s en cons\u00e9quence.<\/span><\/p>\n<p><span style=\"font-size: small;\">La question qui vient assez rapidement est : que se passe-t-il s\u2019il n\u2019y a pas suffisamment de plage dans la page de 8Ko pour stocker la donn\u00e9e ? Pour un stockage sans index clustered, on ne se pose pas la question, le syst\u00e8me cr\u00e9e simplement une nouvelle page dans laquelle il peut stocker les nouvelles donn\u00e9es. Mais l\u00e0, comme il est imp\u00e9ratif de respecter l\u2019ordre de classement impos\u00e9 par l\u2019index, il y a tout simplement un d\u00e9coupage de la page en deux (aussi appel\u00e9 Split). A noter que cela aura en cascade un impact sur l\u2019ensemble de l\u2019arbre d\u2019index, et pourra m\u00eame potentiellement aller jusqu\u2019\u00e0 ajouter un niveau \u00e0 l\u2019arbre (mais ce cas extr\u00eame est bien entendu tr\u00e8s rare).<\/span><br \/>\n<span style=\"font-size: small;\">M\u00eame si le nombre de pages impact\u00e9es reste tr\u00e8s souvent r\u00e9duit, le co\u00fbt reste n\u00e9anmoins cons\u00e9quent au regard du fait qu\u2019il s\u2019agit d\u2019une simple insertion d\u2019un unique enregistrement. C\u2019est pourquoi il est conseill\u00e9 de choisir pour les index clustered un ordre tel que les derniers enregistrements viennent se placer \u00e0 la fin (par exemple avec, en d\u00e9but d\u2019index, un num\u00e9ro chrono, ou une colonne DateTime de cr\u00e9ation), ou bien d\u2019utiliser de mani\u00e8re judicieuse le param\u00e8tre de Taux de Remplissage (Fillfactor, voir <a href=\"https:\/\/docs.microsoft.com\/fr-fr\/previous-versions\/sql\/sql-server-2008-r2\/ms177459(v=sql.105)\">https:\/\/docs.microsoft.com\/fr-fr\/previous-versions\/sql\/sql-server-2008-r2\/ms177459(v=sql.105)<\/a>) afin de conserver un certain pourcentage des pages disponible pour justement placer les nouveaux enregistrements ins\u00e9r\u00e9s (ou bien permettre aux enregistrements en plage de prendre un peu d\u2019embonpoint).<\/span><\/p>\n<p><span style=\"font-size: small;\">D\u00e8s lors qu\u2019une table commence \u00e0 grossir un peu, il devient tr\u00e8s vite \u00e9vident que l\u2019usage d\u2019un index pour retrouver les donn\u00e9es est primordial, afin de ne pas avoir \u00e0 chercher nos donn\u00e9es en parcourant syst\u00e9matiquement la Terre enti\u00e8re\u2026<\/span><br \/>\n<span style=\"font-size: small;\">Par exemple, remplissons une grosse table :<\/span><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">create table GrosseTableSansIndexClustered (Id int Identity, Donnee varchar(50))\r\ngo\r\ncreate table GrosseTableAvecIndexClustered (Id int Identity, Donnee varchar(50))\r\ncreate unique clustered index Index_GrosseTable on GrosseTableAvecIndexClustered(Id)\r\ngo\r\ndeclare @Donnee varchar(50)\r\nselect @Donnee=CONVERT(varchar(50),NEWID())\r\ninsert into GrosseTableSansIndexClustered(Donnee) values (@Donnee)\r\ninsert into GrosseTableAvecIndexClustered(Donnee) values (@Donnee)\r\ngo 100000<\/pre>\n<p><span style=\"font-size: x-small;\"><br \/>\n<\/span><\/p>\n<p><span style=\"font-size: small;\">Si maintenant nous cherchons un enregistrement particulier, les statistiques de dur\u00e9es et d\u2019entr\u00e9es-sorties parlent d\u2019elles-m\u00eames\u2026<\/span><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">set nocount on\r\nset statistics io on\r\nset statistics time on\r\nprint 'Sans index'\r\nselect * from GrosseTableSansIndexClustered where id=90000\r\nprint 'Avec index'\r\nselect * from GrosseTableAvecIndexClustered where id=90000<\/pre>\n<p><span style=\"font-size: x-small;\"><br \/>\n<\/span><span style=\"color: #ff0000; font-size: x-small;\">Sans index<\/span><\/p>\n<p><span style=\"font-size: x-small;\">SQL Server \\endash Temps d&rsquo;ex\u00e9cution : <\/span><br \/>\n<span style=\"font-size: x-small;\">, Temps UC = 0 ms, temps \u00e9coul\u00e9 = 0 ms.<\/span><br \/>\n<span style=\"font-size: x-small;\">Temps d&rsquo;analyse et de compilation de SQL Server : <\/span><br \/>\n<span style=\"font-size: x-small;\">, Temps UC = 0 ms, temps \u00e9coul\u00e9 = 0 ms.<\/span><br \/>\n<span style=\"font-size: x-small;\">Table &lsquo;GrosseTableSansIndexClustered&rsquo;. Nombre d&rsquo;analyses 1, <span style=\"color: #ff0000;\">lectures logiques 685<\/span>, lectures physiques 0, lectures anticip\u00e9es 38, lectures logiques de donn\u00e9es d&rsquo;objets volumineux 0, lectures physiques de donn\u00e9es d&rsquo;objets volumineux 0, lectures anticip\u00e9es de donn\u00e9es d&rsquo;objets volumineux 0.<\/span><\/p>\n<p><span style=\"font-size: x-small;\">SQL Server \\endash Temps d&rsquo;ex\u00e9cution : <\/span><br \/>\n<span style=\"font-size: x-small;\">, <span style=\"color: #ff0000;\">Temps UC = 16 ms, temps \u00e9coul\u00e9 = 11 ms<\/span>.<\/span><br \/>\n<span style=\"font-size: x-small;\">Avec index<\/span><\/p>\n<p><span style=\"font-size: x-small;\">SQL Server \\endash Temps d&rsquo;ex\u00e9cution : <\/span><br \/>\n<span style=\"font-size: x-small;\">, Temps UC = 0 ms, temps \u00e9coul\u00e9 = 0 ms.<\/span><br \/>\n<span style=\"font-size: x-small;\">Temps d&rsquo;analyse et de compilation de SQL Server : <\/span><br \/>\n<span style=\"font-size: x-small;\">, Temps UC = 0 ms, temps \u00e9coul\u00e9 = 0 ms.<\/span><br \/>\n<span style=\"font-size: x-small;\">Table &lsquo;GrosseTableAvecIndexClustered&rsquo;. Nombre d&rsquo;analyses 0, <span style=\"color: #ff0000;\">lectures logiques 3<\/span>, lectures physiques 0, lectures anticip\u00e9es 0, lectures logiques de donn\u00e9es d&rsquo;objets volumineux 0, lectures physiques de donn\u00e9es d&rsquo;objets volumineux 0, lectures anticip\u00e9es de donn\u00e9es d&rsquo;objets volumineux 0.<\/span><\/p>\n<p><span style=\"font-size: x-small;\">SQL Server \\endash Temps d&rsquo;ex\u00e9cution : <\/span><br \/>\n<span style=\"font-size: x-small;\">, <span style=\"color: #ff0000;\">Temps UC = 0 ms, temps \u00e9coul\u00e9 = 0 ms<\/span>.<\/span><\/p>\n<p><span style=\"font-size: small;\">Dans un prochain billet, nous \u00e9tudierons un autre type d\u2019index, les indexes non clustered. En attendant, n\u2019h\u00e9sitez pas m\u2019envoyer moult questions par mail sur les indexes clustered, j\u2019y r\u00e9pondrai avec plaisir\u2026<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Parmi les fondamentaux de SQL Server figure la notion d\u2019Index Clustered, souvent mal comprise par les d\u00e9butants. Dans ce deuxi\u00e8me billet concernant l\u2019organisation des donn\u00e9es, nous chercherons \u00e0 comprendre ce qu\u2019est un index 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-208","post","type-post","status-publish","format-standard","hentry","category-article_sql"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/208","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=208"}],"version-history":[{"count":7,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/208\/revisions"}],"predecessor-version":[{"id":1960,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/208\/revisions\/1960"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=208"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=208"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=208"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}