SQL Server · types de données
Choisir un type, c'est choisir combien de lignes tiennent dans une page de 8 Ko, combien de pages il faut lire, et combien de mémoire une requête réserve. Exemple avec la clé primaire d'une table d'un milliard de lignes.
Chaque Go de trop est lu sur le disque, occupe de la mémoire, puis est sauvegardé, répliqué et restauré. Et il se multiplie avec le nombre d'index.
00 · Les bases
Quatre idées suffisent pour comprendre tout le reste de la page. Les tailles sont données en octets (o) : 1 Ko = 1 024 octets.
SQL Server ne range pas les lignes une par une : il les met dans des « pages » de 8 Ko, comme des fiches dans des boîtes de taille fixe. Plus une ligne est petite, plus il en tient dans une boîte.
Pour lire une seule ligne, SQL Server charge la page complète, du disque vers la mémoire. Moins de boîtes, c'est moins de lectures disque, plus de données en cache, des sauvegardes plus courtes.
Les boîtes sont triées selon la clé primaire, comme un classeur trié par numéro. Chaque index secondaire est un petit répertoire à part qui recopie la clé pour retrouver la ligne, comme l'index à la fin d'un livre renvoie à un numéro de page. Les DBA parlent d'index clusterisé pour la table rangée par sa clé, et d'index non clusterisés pour les index secondaires.
Avant un tri (ORDER BY), un regroupement (GROUP BY) ou certaines jointures, SQL Server réserve de la mémoire, comme une table au restaurant, en devinant la taille des données d'après les types déclarés. Une réservation trop grande bloque les autres requêtes, une trop petite oblige à trier sur le disque.
01 · Atelier
Glisser des types depuis la palette vers la version A ou B, puis cliquer sur une colonne pour régler son nom et ses longueurs. Chaque version montre une page de 8 Ko remplie au maximum, puis le nombre de pages nécessaires sur le disque.
En clair : la même table Client, écrite deux fois. Seuls les types changent. La version B fait tenir presque deux fois plus de clients par page, donc presque deux fois moins de pages à lire et à garder en mémoire.
Lire la palette : o = octet. Pour les chaînes, n est la longueur déclarée, lg la longueur réelle du texte stocké, et +2 les 2 octets que SQL Server ajoute pour savoir où le texte s'arrête. Jusqu'à 8 colonnes bit partagent un seul octet. decimal et money sont exacts, float est approché (0,1 + 0,2 n'y vaut pas exactement 0,3) : à éviter pour des montants.
La première colonne sert de clé primaire. Glisser une colonne vers la palette pour la supprimer. En gris dans la bande, le surcoût que SQL Server ajoute à chaque ligne : un en-tête de 4 octets, des marqueurs indiquant quelles colonnes sont NULL, et la position de chaque texte. SQL Server range physiquement les colonnes fixes avant les colonnes variables, quel que soit l'ordre du CREATE TABLE : la bande de chaque version suit cet ordre réel.
Chaque index secondaire recopie la clé primaire (la première colonne) de toutes les lignes. Une clé GUID alourdit donc la table et chacun de ses index : la section 02 montre comment.
02 · Clé primaire
Une fois en largeur de ligne, une fois à chaque insertion, et une fois dans chaque index secondaire, qui recopie la clé pour retrouver la ligne.
En clair : avec un compteur (int IDENTITY), chaque nouvelle ligne va à la fin, comme on ajoute une fiche au fond du classeur. Avec un GUID aléatoire, chaque nouvelle ligne doit s'insérer au milieu d'une boîte déjà pleine : SQL Server coupe la boîte en deux et déménage la moitié des fiches. C'est lent, et les boîtes restent à moitié vides.
Une clé primaire technique est le plus souvent générée automatiquement par la base. Voici les trois manières comparées dans la suite, avec les valeurs qu'elles produisent pour cinq insertions successives.
Un compteur : chaque nouvelle ligne reçoit le numéro suivant. 4 octets, toujours croissant.
CREATE TABLE Client ( id int IDENTITY(1,1) PRIMARY KEY, ...
Un GUID tiré au hasard, unique au monde. 16 octets, sans aucun ordre : deux valeurs successives n'ont rien en commun. C'est aussi ce que produit Guid.NewGuid() côté application.
id uniqueidentifier DEFAULT NEWID() PRIMARY KEY
Un GUID généré par SQL Server dans l'ordre croissant : chaque valeur est un peu plus grande que la précédente. On garde un identifiant unique de 16 octets, mais les insertions se font en fin de table, comme avec un compteur.
id uniqueidentifier DEFAULT NEWSEQUENTIALID() PRIMARY KEY
À savoir sur NEWSEQUENTIALID() : il ne s'utilise que comme valeur par défaut d'une colonne, pas dans un SELECT ni dans le code applicatif. Les valeurs étant prévisibles, elles ne conviennent pas comme jeton secret (lien de réinitialisation de mot de passe, par exemple). La séquence peut repartir plus bas après un redémarrage du serveur. Enfin, un GUID « ordonné » généré par l'application, comme un UUID v7, n'est pas forcément dans l'ordre pour SQL Server, qui compare d'abord les derniers octets (voir plus bas « Le tri d'un GUID ne suit pas le texte »).
Au départ, chaque table contient déjà 120 lignes, bien rangées par une reconstruction d'index (le réglage « fill factor » est expliqué juste après). Les trois méthodes insèrent ensuite les mêmes nouvelles lignes en parallèle. Avec une clé croissante, chaque nouvelle ligne va dans la dernière page ; quand elle est pleine, SQL Server en ouvre une nouvelle. Avec NEWID(), chaque ligne doit se ranger à sa place dans l'ordre de la clé, souvent dans une page déjà pleine : SQL Server la coupe en deux et déplace la moitié des lignes dans une nouvelle page, ajoutée en fin de fichier. Les DBA appellent cela un page split. Cliquer sur « Lancer » pour voir l'effet.
Chaque case est une page de la table, rangée dans l'ordre de la clé. Le chiffre en gros indique combien de lignes elle contient, le petit « p12 » sa position sur le disque. Une page qui vient d'être coupée en deux clignote.
Ordre de lecture sur le disque : pour lire la table dans l'ordre de la clé, SQL Server suit cette courbe. Une ligne droite veut dire des pages rangées les unes après les autres, lues d'un seul trait. Un zigzag veut dire des sauts permanents d'un bout à l'autre du fichier. Avec NEWID(), les pages sont aussi à moitié vides : il en faut plus pour les mêmes lignes, sur le disque comme en mémoire. NEWSEQUENTIALID() évite les coupures et le désordre, mais garde des lignes de 16 octets de clé.
Un index se « reconstruit » : c'est une opération de maintenance, souvent planifiée la nuit par le DBA, qui réécrit toutes les pages bien rangées et bien remplies. À cette occasion, on peut demander de ne remplir chaque page qu'en partie, par exemple à 80 %. Ce pourcentage s'appelle le fill factor (taux de remplissage). La place laissée libre absorbe les futures insertions au milieu, sans avoir à couper les pages.
| Fill factor | Fragmentation | |
|---|---|---|
| Ce que c'est | De la place libre laissée exprès dans chaque page | Des pages à moitié vides et rangées dans le désordre |
| D'où ça vient | Un réglage choisi au moment de la reconstruction | Les insertions au milieu et les pages coupées en deux |
| Quand | Appliqué une seule fois, lors de la reconstruction | S'accumule au fil des insertions |
| Ce que ça coûte | Plus de pages dès le départ, en échange de moins de coupures | Plus de pages, et des lectures en zigzag sur le disque |
À essayer dans la simulation ci-dessus : choisir 70 %, puis « Lancer ». Avec NEWID(), les pages absorbent les premières insertions sans être coupées, mais les tables démarrent avec plus de pages. Une fois les trous comblés, les coupures reprennent : « Reconstruire l'index » remet tout en ordre, comme le fait une maintenance planifiée. Regarder aussi la colonne int IDENTITY : les pages à 70 % y restent à 70 % pour toujours.
Une table créée sans clé primaire, ou avec une clé primaire déclarée NONCLUSTERED, n'est rangée dans aucun ordre : SQL Server pose chaque nouvelle ligne là où il reste de la place. On appelle ce rangement un tas (heap en anglais). On en croise souvent dans les tables de logs, d'import ou de travail.
En clair : dans un tas, quand une ligne grossit lors d'un UPDATE (un commentaire rempli après coup, un texte qui passe de 10 à 2 000 caractères) et ne tient plus dans sa page, SQL Server la déménage dans une autre page. Les index secondaires connaissent encore l'ancienne adresse : il laisse donc à sa place une adresse de réexpédition, comme un suivi de courrier après un déménagement. Les DBA parlent de forwarded record. Chaque lecture qui passe par l'ancienne adresse fait ensuite un détour.
Chaque mise à jour remplit le commentaire d'un client : sa ligne passe d'environ 400 à 1 900 octets. Chaque petit rectangle est un client, sa hauteur suit sa taille. Vert : client à sa place. Rouge : client déménagé. Pointillés rouges : adresse de réexpédition laissée à l'ancienne place. Survoler un rectangle pour le détail.
Avec un index clusterisé, ce phénomène n'existe pas : la ligne reste à sa place dans l'ordre de la clé, et c'est la page qui est coupée en deux, comme vu plus haut. Pour nettoyer un tas existant, un DBA lance ALTER TABLE ... REBUILD. Le plus simple reste de donner une clé primaire clusterisée à chaque table, et de se méfier des colonnes de texte créées vides puis remplies plus tard.
Pour chercher vite un client par son email, on crée un index secondaire sur email. C'est une petite liste triée par email, et à côté de chaque email, SQL Server recopie la clé primaire du client : c'est l'adresse qui permet de retrouver la ligne complète dans la table.
Conséquence : la clé primaire est recopiée dans chaque ligne de chaque index secondaire. Une table avec 4 index stocke donc la clé 5 fois. Avec un GUID de 16 octets au lieu d'un int de 4, c'est 12 octets de plus à chaque copie : des index plus gros, moins d'entrées par page d'index, plus de pages à lire pour chaque recherche.
SQL Server ne trie pas un uniqueidentifier comme un texte : il compare d'abord le dernier bloc de 12 caractères, puis le bloc de 4 qui le précède, et remonte ainsi vers la gauche. Conséquence pratique : un GUID que l'application croit ordonné (UUID v7, ou trié par ordre alphabétique) peut se comporter comme un GUID aléatoire une fois en base, avec les pages coupées vues plus haut.
03 · Tri et mémoire
Un varchar ne stocke que sa longueur réelle. Mais avant d'exécuter un tri (ORDER BY) ou un regroupement (GROUP BY, certaines jointures), SQL Server réserve de la mémoire sans connaître ces longueurs. L'optimiseur, la partie de SQL Server qui prépare le plan d'exécution avant de lancer la requête, suppose chaque colonne variable remplie à moitié de sa taille déclarée, et compte 4 000 octets pour une colonne (max).
En clair : déclarer nvarchar(255) « pour être tranquille » ne prend pas plus de place sur le disque. Mais au moment d'un tri, SQL Server se dit que chaque nom fait peut-être 255 caractères, et réserve la mémoire en conséquence. Sur un serveur partagé, ces réservations gonflées font attendre toutes les autres requêtes.
SELECT * FROM Client ORDER BY nom; -- sur les versions A et B de l'atelier
Ordre de grandeur, hypothèses simplifiées : environ 75 % de la mémoire du serveur disponible pour l'exécution des requêtes, 25 % de ce volume au maximum par requête. Une réservation trop grande fait attendre les autres requêtes (attente RESOURCE_SEMAPHORE). Une réservation trop petite fait déborder le tri dans tempdb, une base temporaire sur disque, bien plus lente que la mémoire.
Le gain le plus simple : la réservation dépend de toutes les colonnes sélectionnées. Remplacer SELECT * par les seules colonnes utiles réduit d'autant la mémoire demandée, quels que soient les types.
04 · Texte et Unicode
Un ordinateur ne stocke que des nombres. Pour stocker du texte, il faut une table de correspondance entre chaque caractère et un nombre : c'est l'encodage. varchar et nvarchar n'utilisent pas la même table.
En clair : varchar utilise une table de 256 caractères, choisie par la collation de la base, le réglage qui fixe l'alphabet utilisé et les règles de comparaison (« é » vaut-il « e » ? « A » vaut-il « a » ?). En France, c'est en général Windows-1252 : l'alphabet latin, les accents français, le symbole €. Un caractère absent de cette table est remplacé par un ?, sans aucune erreur. nvarchar utilise Unicode : tous les alphabets du monde, mais au moins 2 octets par caractère, même pour un simple « a ».
En SQL, 'Łukasz' est une chaîne varchar et N'Łukasz' une chaîne Unicode. Sans le N, le texte est abîmé avant même d'arriver dans la colonne nvarchar : elle stocke alors « ?ukasz ».
Pour du texte saisi par des personnes du monde entier : noms, adresses, commentaires. Pas pour des codes, des références, des emails ou des numéros, qui ne contiennent que des caractères simples.
Depuis SQL Server 2019, un varchar avec une collation se terminant par _UTF8 accepte tous les alphabets : 1 octet pour une lettre simple, 2 pour un « é », 3 pour un idéogramme.
05 · Téléphone
Saisir un numéro pour voir ce que chaque déclaration stocke réellement, ce qu'elle coûte sur le disque et ce qu'elle fait réserver pour un tri.
En clair : le réflexe varchar(255) ou nvarchar(255) vient souvent de l'ORM ou du copier-coller. Un numéro au format international tient en 16 caractères, sans accent : varchar(16) suffit et documente l'intention.
Un numéro n'est pas un nombre : on ne l'additionne pas, il commence par 0 ou par +. Le format E.164 (un +, au plus 15 chiffres) tient dans un varchar(16), se compare et s'indexe proprement.
06 · Dates et heures
datetime est rarement le bon choix : 8 octets, une précision de 3,33 ms avec arrondi, et une plage qui commence en 1753. Glisser le type le plus adapté sur chaque besoin (ou cliquer un type puis une case).
En clair : SQL Server propose un type par besoin. Une date seule n'a pas besoin d'heure (date, 3 octets), une heure seule n'a pas besoin de date (time), et un horodatage moderne s'écrit en datetime2, dont on choisit la précision.
| Type | Contient | Exemple | Octets |
|---|---|---|---|
| date | une date seule | 2026-10-09 | 3 |
| time(p) | une heure seule ; p = nombre de chiffres après la seconde (0 à 7) | 14:37:05 | 3 à 5 |
| smalldatetime | date et heure à la minute, de 1900 à 2079 | 2026-10-09 14:37 | 4 |
| datetime | l'ancien type historique, précision 3,33 ms | 2026-10-09 14:37:05.127 | 8 |
| datetime2(p) | le remplaçant moderne de datetime, de l'an 1 à 9999 | 2026-10-09 14:37:05.128 | 6 à 8 |
| datetimeoffset(p) | comme datetime2, avec le décalage par rapport à UTC | 2026-10-09 14:37:05 +02:00 | 8 à 10 |
datetime arrondit les millisecondes à .000, .003 ou .007. Une borne de fin écrite 23:59:59.999 devient minuit le lendemain, et un BETWEEN, qui inclut ses deux bornes, ramène alors aussi les lignes de minuit le lendemain. La bonne écriture reste >= jour AND < jour + 1.
07 · Requêtes
Un index ne sert que si SQL Server peut utiliser son ordre. Une condition qui le permet est dite sargable (de l'anglais Search ARGument ABLE).
En clair : un index, c'est un annuaire trié par nom. Pour trouver tous les « Martin », on ouvre l'annuaire à la lettre M et on s'arrête après le dernier Martin. Pour trouver tous les noms qui contiennent « mar », l'ordre ne sert à rien : il faut lire l'annuaire en entier. La règle : la colonne doit apparaître seule dans la condition, sans fonction ni calcul autour.
Chaque case est une entrée de l'index, rangée dans l'ordre de la colonne. Jaune : entrée lue. Vert : entrée qui répond à la condition. Contour bleu : endroit où SQL Server saute directement.
Le vérifier soi-même : dans SSMS, activer « Inclure le plan d'exécution réel » (Ctrl+M) puis lancer la requête. Une lecture complète apparaît sous le nom Index Scan (ou Clustered Index Scan, Table Scan), un accès direct sous le nom Index Seek. Un scan n'est pas toujours un problème sur une petite table, mais sur des millions de lignes il se paie à chaque appel.
08 · Mémo
Un statut à 5 valeurs tient dans un tinyint, un compteur de commandes dans un int. Prévoir la croissance, sans réserver « au cas où ».
Elle ne change rien au disque, mais tout aux estimations mémoire, à la taille maximale d'une clé d'index (900 octets pour la clé primaire, 1 700 pour un index secondaire) et à la qualité des données.
Deux octets par caractère. Un email, un code postal ou un numéro de téléphone n'ont pas besoin d'Unicode (voir la section 04).
Comparer la colonne seule, et déplacer le calcul de l'autre côté : bornes de dates plutôt que YEAR(), montant > 90 plutôt que montant + 10 > 100 (voir la section 07).
Une jointure entre un int et un varchar, ou un paramètre nvarchar sur une colonne varchar, force une conversion implicite qui peut empêcher l'usage d'un index.
| Terme | En clair |
|---|---|
| Page | Bloc de 8 Ko dans lequel SQL Server range les lignes. C'est l'unité de lecture et de mise en cache. |
| Index clusterisé | La table elle-même, rangée dans l'ordre de sa clé primaire. Une seule par table. |
| Index non clusterisé | Un index secondaire : une copie triée de quelques colonnes, avec la clé primaire pour retrouver la ligne. |
| Page split | Page pleine coupée en deux pour faire de la place à une insertion au milieu. Typique des clés GUID aléatoires. |
| Reconstruction d'index (rebuild) | Maintenance qui réécrit toutes les pages d'un index, bien rangées et bien remplies. Efface la fragmentation, pour un temps. |
| Fill factor | Taux de remplissage des pages choisi lors d'une reconstruction, pour laisser de la place aux insertions futures. |
| Tas (heap) | Table sans index clusterisé : les lignes sont posées là où il reste de la place, sans ordre. |
| Forwarded record | Dans un tas, ligne déplacée parce qu'elle a grossi, avec une adresse de réexpédition laissée à son ancienne place. |
| Fragmentation | Pages qui ne se suivent plus sur le disque, ou à moitié vides. Plus de pages à lire pour les mêmes données. |
| Index Seek / Index Scan | Accès direct au bon endroit de l'index, ou lecture de l'index en entier. Visible dans le plan d'exécution. |
| Sargable | Condition écrite de façon à permettre un Index Seek : la colonne seule, sans fonction ni calcul autour. |
| Conversion implicite | Conversion de type ajoutée par SQL Server quand deux valeurs comparées n'ont pas le même type. Peut empêcher l'usage d'un index. |
| Memory grant | Mémoire réservée par une requête avant son exécution, pour ses tris et regroupements. |
| tempdb | Base temporaire sur disque où débordent les tris qui ne tiennent pas en mémoire. |
| Collation | Réglage qui fixe l'alphabet d'un varchar et les règles de comparaison (casse, accents). |
| Plan d'exécution | La recette choisie par SQL Server pour exécuter une requête : quels index, dans quel ordre, avec combien de mémoire. |