La modification de la longueur d’une colonne de table est une opération plutôt courante dans la vie d’un administrateur de bases de données mais qu’en est il du stockage interne ? Beaucoup de gens pensent par exemple que diminuer la longueur d’une colonne permet de récupérer de l’espace de stockage ou que d’augmenter la longueur d’une colonne n’a que très peu d’impact. Rien de ceci n’est vrai et nous le verrons dans la suite de ce billet.

Commençons par créer une table T_TEST :

CREATE TABLE T_TEST
(
id SMALLINT NOT NULL,
texte1 CHAR(50) NOT NULL
);

Insérons ensuite une ligne de données :

INSERT T_TEST VALUES (1, REPLICATE(‘T’, 25));

Dans un premier temps nous utiliserons une vue système sys.system_internals_partition_columns. Cette vue permet de visualiser les métas données de bas niveau du moteur SQL Server. Cette vue est bien entendu réservée à un usage interne et leur compatibilité n’est pas garantie pour les versions futures de SQL Server. Vous trouverez une liste des vues ici.

Le script suivant permet de visualiser les informations d’offsets des colonnes de la table T_TEST :

SELECT
    C.name AS column_name,
    C.column_id,
    PC.max_inrow_length,
    PC.system_type_id,
    PC.leaf_offset
FROM sys.system_internals_partition_columns PC
INNER JOIN sys.partitions AS P
ON P.partition_id = PC.partition_id
INNER JOIN sys.columns AS C
ON C.column_id = PC.partition_column_id
  AND C.object_id = P.object_id
WHERE P.object_id = OBJECT_ID(‘T_TEST’);

Le résultat est le suivant :

La colonne id est stocké sur 2 octets (SMALLINT = 2 octets) et commence au 4ème octet. La colonne texte1, quant à elle, est stocké sur 50 octets (CHAR(50) = 50 octets) et commence au 6ème octet (longueur de la colonne id + offset de départ). La longueur totale d’une ligne fait donc 56 octets pour le moment.

Augmentation de la longueur d’une colonne

Réduisons la longueur de la colonne id en changeant son type en INT. Le stockage d’une colonne de type INT prend 4 octets.

ALTER TABLE T_TEST
ALTER COLUMN id INT;
GO

Regardons l’impact au niveau interne à l’aide de notre script :

On constate tout d’abord que la colonne id ne commence plus à l’offset 4 mais à l’offset 56. Ensuite, on remarque que la longueur de la colonne a bien changé (mx_inrow_length = 4). Que sont devenus les données présentes tout à l’heure à l’offset 4 ? Vérifions le à l’aide des commandes DBCC IND et DBCC PAGE :

DBCC IND (‘Internales’, ‘T_TEST’, -1);
GO

C’est la page 153 qui nous intéresse ici.

DBCC TRACEON(3604);
GO
DBCC PAGE(‘Internales’, 1, 153, 3);

01 : status bits A
00 : status bits B
00 3C : Longueur de la portion fixe de la ligne de données. (003C correspond à 60 en décimal)
00 01 : Valeur de la colonne id avant changement (correspond à 1 en décimal sur 2 octets qui correspondent au type SMALLINT)
20 20 20 20 … 54 54 54 54 : Valeur de la colonne text1 (en prenant les valeurs ASCII on retrouve bien la chaîne : TTTTTTTTTTTTTTTTTTTTTTTTT)
00 00 00 01 : Valeur de la colonne id après changement (correspond à 1 en décimal sur 4 octets qui correspondent bien au type INTEGER)
00 03 : Nombre de colonnes (3 colonnes : id (avant modification) + texte1 + id (après modification))
00 : Matrice des NULL (Dans ce cas pas de colonnes NULL)

>> Lorsqu’une colonne voit sa longueur augmentée, SQL Server ne remplace pas cette colonne avec la nouvelle longueur mais crée une nouvelle colonne avec la nouvelle longueur. L’ancienne colonne est toujours présente mais n’est plus visible. Par conséquent, la longueur de la ligne de données augmente. On passe dans notre cas d’une longueur de 56 octets à une longueur de 60 octets.

Réduction de la longueur d’une colonne

Prenons maintenant le cas inverse et réduisons la longueur de la colonne id en changeant le type de données à SMALLINT.

ALTER TABLE T_TEST
ALTER COLUMN texte1 CHAR(25);
GO

Regardons l’impact au niveau interne à l’aide de notre script :

La longueur de la colonne texte1 a bien changé (max_inrow_length = 25) mais l’offset de la colonne id reste inchangé. Celui-ci est toujours égale à 56. Le stockage de la colonne texte1 n’a en fait pas changé. Il est toujours de 50 octets (leaf offset colonne texte1 = 56 –  leaf offset colonne id = 6).

>> Lorsque la longueur d’une colonne est diminuée, SQL Server ne réduit pas le stockage en réalité. Un contrôle est simplement réalisé sur la longueur des données (colonne max_inrow_length).

Comme vous pouvez le constater, l’impact sur le stockage interne est tout autre de ce que l’on pense à priori. Il est important de savoir que l’augmentation de la longueur d’une colonne a un fort impact sur le stockage interne et qu’à l’inverse aucun espace de stockage n’est récupéré. La seule façon de remettre tout ça en ordre est de créer ou reconstruire l’index cluster de la table.

Bonne modification de colonnes !!!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM
MVP SQL Server