Parmi les fondamentaux de SQL Server figure la notion d’Index Clustered, souvent mal comprise par les débutants. Avant de détailler dans un prochain billet cette notion d’index clustered, nous allons d’abord nous attacher à décrire le stockage des données dans le cas où une table ne contient pas d’index clustered.
Avant toute chose, un des points clé de SQL Server réside dans l’unité de stockage, à savoir la Page. Une page est de manière invariable un bloc de 8Ko de données. Au niveau des fichiers de données, tout est découpé sous forme de pages, y compris des pages permettant de décrire le rôle de chaque page (Allocation Maps). Mais n’allons pas trop loin ici, et revenons sur le cœur du sujet.
Les données sont stockées dans ces pages de 8Ko. Deux formats de stockage sont possibles :
– Soit les données sont triées d’après un index clustered (ce point fera l’objet d’un prochain billet)
– Soit les données sont positionnées à la suite les unes des autres, telles qu’elles viennent. On parle alors de « segment de données », et c’est le comportement par défaut d’une table lorsqu’il n’y a pas d’index clustered défini.
Voici les principes de fonctionnement d’un segment de données (Heap).
– Lorsqu’une nouvelle ligne de données est insérée, elle se positionne à la fin.
– Lorsqu’une ligne de données est supprimée, l’espace qu’elle occupait est déclaré vacant.
– Lors de la modification d’une ligne, si l’espace nécessaire pour son stockage n’augmente pas, ou s’il y a suffisamment d’espace disponible pour stocker la nouvelle ligne sans déplacement, alors les données sont mises à jour sur place. Si la ligne « grossit trop », alors elle est déplacée en fin de liste.
Voici par exemple une table définie sans index clustered.
create table TableHeap (Id bigint identity, Donnee1 varchar(500), Donnee2 varchar(500))
insert into TableHeap (Donnee1,Donnee2)
values ('Court1A','Court2A'),
('Court1X','Court2X'),
('Court1B','Court2B'),
('Court1C','Court2C'),
('Court1Z','Court2Z')
Voici le détail du stockage dans la seule page qu’occupent les 4 lignes insérées :
Slot 0 Offset 0x60 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000005BC8A060
0000000000000000: 30000c00 01000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 72743141 436f7572 743241 .Court1ACourt2A
Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 1
Slot 0 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1A
Slot 0 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2A
Slot 1 Offset 0x83 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000005BC8A083
0000000000000000: 30000c00 02000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 72743158 436f7572 743258 .Court1XCourt2X
Slot 1 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 2
Slot 1 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1X
Slot 1 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2X
Slot 2 Offset 0xa6 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000005BC8A0A6
0000000000000000: 30000c00 03000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 72743142 436f7572 743242 .Court1BCourt2B
Slot 2 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 3
Slot 2 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1B
Slot 2 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2B
Slot 3 Offset 0xc9 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000005BC8A0C9
0000000000000000: 30000c00 04000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 72743142 436f7572 743242 .Court1BCourt2B
Slot 3 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 4
Slot 3 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1B
Slot 3 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2B
Slot 4 Offset 0xec Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x000000005BC8A0EC
0000000000000000: 30000c00 05000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 7274315a 436f7572 74325a .Court1ZCourt2Z
Slot 4 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 5
Slot 4 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1Z
Slot 4 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2Z
Lorsque l’on supprime l’enregistrement portant l’Id 3, on constate l’évolution du contenu de la page : le Slot 2, qui contenait cet enregistrement, a simplement disparu, et les offsets de stockage des autres slots sont restés inchangés.
delete from TableHeap where id=3
Slot 0 Offset 0x60 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x0000000057FEA060
0000000000000000: 30000c00 01000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 72743141 436f7572 743241 .Court1ACourt2A
Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 1
Slot 0 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1A
Slot 0 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2A
Slot 1 Offset 0x83 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x0000000057FEA083
0000000000000000: 30000c00 02000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 72743158 436f7572 743258 .Court1XCourt2X
Slot 1 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 2
Slot 1 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1X
Slot 1 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2X
Slot 3 Offset 0xc9 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x0000000057FEA0C9
0000000000000000: 30000c00 04000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 72743142 436f7572 743242 .Court1BCourt2B
Slot 3 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 4
Slot 3 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1B
Slot 3 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2B
Slot 4 Offset 0xec Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x0000000057FEA0EC
0000000000000000: 30000c00 05000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 7274315a 436f7572 74325a .Court1ZCourt2Z
Slot 4 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 5
Slot 4 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1Z
Slot 4 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2Z
Si l’on met à jour un enregistrement, mais que la ligne est trop longue pour rentrer dans l’espace disponible, on voit que la ligne se positionne à la suite des autres enregistrements, à la fin (le numéro de slot 1 est certes conservé, mais l’offset est bien supérieur à celui du dernier slot, le slot 3).
update TableHeap set Donnee1='Nouvelle valeur (trop longue pour rentrer dans l''espace disponible) N°1', Donnee2='Nouvelle valeur (trop longue pour rentrer dans l''espace disponible) N°2' where Id=2
Slot 0 Offset 0x60 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x0000000059B2A060
0000000000000000: 30000c00 01000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 72743141 436f7572 743241 .Court1ACourt2A
Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 1
Slot 0 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1A
Slot 0 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2A
Slot 1 Offset 0x10f Length 163
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 163
Memory Dump @0x0000000059B2A10F
0000000000000000: 30000c00 02000000 00000000 03000002 005c00a3 0…………….\.£
0000000000000014: 004e6f75 76656c6c 65207661 6c657572 20287472 .Nouvelle valeur (tr
0000000000000028: 6f70206c 6f6e6775 6520706f 75722072 656e7472 op longue pour rentr
000000000000003C: 65722064 616e7320 6c276573 70616365 20646973 er dans l’espace dis
0000000000000050: 706f6e69 626c6529 204eb031 4e6f7576 656c6c65 ponible) N°1Nouvelle
0000000000000064: 2076616c 65757220 2874726f 70206c6f 6e677565 valeur (trop longue
0000000000000078: 20706f75 72207265 6e747265 72206461 6e73206c pour rentrer dans l
000000000000008C: 27657370 61636520 64697370 6f6e6962 6c652920 ‘espace disponible)
00000000000000A0: 4eb032 N°2
Slot 1 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 2
Slot 1 Column 2 Offset 0x15 Length 71 Length (physical) 71
Donnee1 = Nouvelle valeur (trop longue pour rentrer dans l”espace disponible) N°1
Slot 1 Column 3 Offset 0x5c Length 71 Length (physical) 71
Donnee2 = Nouvelle valeur (trop longue pour rentrer dans l”espace disponible) N°2
Slot 3 Offset 0xc9 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x0000000059B2A0C9
0000000000000000: 30000c00 04000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 72743142 436f7572 743242 .Court1BCourt2B
Slot 3 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 4
Slot 3 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1B
Slot 3 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2B
Slot 4 Offset 0xec Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x0000000059B2A0EC
0000000000000000: 30000c00 05000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 7274315a 436f7572 74325a .Court1ZCourt2Z
Slot 4 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 5
Slot 4 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1Z
Slot 4 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2Z
Si enfin on insère un nouvel enregistrement, on voit que la ligne se positionne à la suite des autres enregistrements, à la fin (le numéro de slot 2 est certes récupéré, mais l’offset est bien supérieur à celui du slot 2, celui qui a l’offset le plus important).
insert into TableHeap (Donnee1,Donnee2)
values ('Nouvelles données N°1','Nouvelles données N°2')
Slot 0 Offset 0x60 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x0000000059B2A060
0000000000000000: 30000c00 01000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 72743141 436f7572 743241 .Court1ACourt2A
Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 1
Slot 0 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1A
Slot 0 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2A
Slot 1 Offset 0x10f Length 163
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 163
Memory Dump @0x0000000059B2A10F
0000000000000000: 30000c00 02000000 00000000 03000002 005c00a3 0…………….\.£
0000000000000014: 004e6f75 76656c6c 65207661 6c657572 20287472 .Nouvelle valeur (tr
0000000000000028: 6f70206c 6f6e6775 6520706f 75722072 656e7472 op longue pour rentr
000000000000003C: 65722064 616e7320 6c276573 70616365 20646973 er dans l’espace dis
0000000000000050: 706f6e69 626c6529 204eb031 4e6f7576 656c6c65 ponible) N°1Nouvelle
0000000000000064: 2076616c 65757220 2874726f 70206c6f 6e677565 valeur (trop longue
0000000000000078: 20706f75 72207265 6e747265 72206461 6e73206c pour rentrer dans l
000000000000008C: 27657370 61636520 64697370 6f6e6962 6c652920 ‘espace disponible)
00000000000000A0: 4eb032 N°2
Slot 1 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 2
Slot 1 Column 2 Offset 0x15 Length 71 Length (physical) 71
Donnee1 = Nouvelle valeur (trop longue pour rentrer dans l”espace disponible) N°1
Slot 1 Column 3 Offset 0x5c Length 71 Length (physical) 71
Donnee2 = Nouvelle valeur (trop longue pour rentrer dans l”espace disponible) N°2
Slot 2 Offset 0x1b2 Length 63
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 63
Memory Dump @0x0000000059B2A1B2
0000000000000000: 30000c00 06000000 00000000 03000002 002a003f 0…………….*.?
0000000000000014: 004e6f75 76656c6c 65732064 6f6e6ee9 6573204e .Nouvelles données N
0000000000000028: b0314e6f 7576656c 6c657320 646f6e6e e9657320 °1Nouvelles données
000000000000003C: 4eb032 N°2
Slot 2 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 6
Slot 2 Column 2 Offset 0x15 Length 21 Length (physical) 21
Donnee1 = Nouvelles données N°1
Slot 2 Column 3 Offset 0x2a Length 21 Length (physical) 21
Donnee2 = Nouvelles données N°2
Slot 3 Offset 0xc9 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x0000000059B2A0C9
0000000000000000: 30000c00 04000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 72743142 436f7572 743242 .Court1BCourt2B
Slot 3 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 4
Slot 3 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1B
Slot 3 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2B
Slot 4 Offset 0xec Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x0000000059B2A0EC
0000000000000000: 30000c00 05000000 00000000 03000002 001c0023 0………………#
0000000000000014: 00436f75 7274315a 436f7572 74325a .Court1ZCourt2Z
Slot 4 Column 1 Offset 0x4 Length 8 Length (physical) 8
Id = 5
Slot 4 Column 2 Offset 0x15 Length 7 Length (physical) 7
Donnee1 = Court1Z
Slot 4 Column 3 Offset 0x1c Length 7 Length (physical) 7
Donnee2 = Court2Z
Du point de vue de l’usage, les segments de données (Heap) ne sont pas préconisés, car toute requête dans une table de la sorte, « non-organisée », oblige un parcours systématique de l’ensemble des lignes pour extraire au vol celles qui correspondent aux critères de recherche.
select * from TableHeap where Id=4
Bien entendu, lorsque nous n’avons qu’une poignée d’enregistrements, un parcours complet de la table n’a qu’un coût limité. Nous verrons dans un prochain billet que ce coût est loin d’être négligeable lorsque le volume de données augmente…
Ping : SQLServer.fr » Stockage des données et index – Partie 3 : Index NonClustered