janvier
2011
Un article de zinzineti
Pour écconomiser de l’espace de stockage des données des tables, on est souvent confronté au choix du type CHAR ou VARCHAR.
Pour une colonne dont la longueur est fixe, il n’y a pas de doute sur le choix du type de colonne : c’est CHAR qu’il faut utiliser. Mais lorsque la longueur de la colonne est variable, il faut se poser la question du choix du type de colonne. Ce n’est pas parce que les données dans la colonne varie qu’il faut systématiquement choisir le type VARCHAR.
Si la longueur de la colonne varie et est majoritairement supérieure à 4 caractères, il faut choisir le type VARCHAR.
Autrement dit même si la colonne est variable et si la longueur des données est majoritairement inférieure ou égale à 4 caractères il faut choisir le type CHAR.
Démo
———–
Soit les tables suivantes :
T_CHAR (col1 CHAR (X)) et T_VARCHAR (col1 VARCHAR (X))
X est la longueur maximale définit pour chaque type.
Le nombre d’octets pour une colonne T_CHAR (col1) est égal à : X
Le nombre moyen d’octets pour une colonne T_VARCHAR (col1) est égal à : X/2 + 2
Pour quelles valeurs de X la colonne T_CHAR(col1) permet de gagner plus d’espace de stockage que la colonne T_VARCHAR(col1) ?
La traduction mathématique de cette question peut s’écrire de la façon suivante :
X < X/2 + 2
Résolvons cette inéquation
X - X/2 < + 2
X/2 < 2
X < 4
Cela veut dire que si la colonne est variable et que le nombre de caractères est majoritairement inférieur ou égal à 4,
il est plus bénéfique d’utiliser le type CHAR
Ici un autre billet de SQLPro relatif à la question CHAR ou VARCHAR ?
————————————————————————————
— Auteur : Etienne ZINZINDOHOUE
————————————————————————————
J’avais bien compris…
Là où je ne suis pas d’accord avec vous c’est de considérer que la moyenne de la taille des chaînes stockées dans la base de données vaut X/2.
En gros, par rapport à ma démonstration vous avez fixé M = X/2.
Je trouve cette hypothèse trop arbitraire pour être réaliste.
Démonstration avec un contre-exemple :
X vaut 8. Je stocke :
totototo
tatatata
tutututu
La taille moyenne de mes chaînes vaut 8 et non pas 4 !
Dans ce cas la taille prise par un CHAR pour chaque ligne sera 8 octets et la taille prise par un VARCHAR pour chaque ligne sera 10 octets.
Donc, dans ce cas où X vaut 8 il est plus avantageux d’utiliser un CHAR, ce qui contredit la conclusion de votre article où vous indiquez que le CHAR n’est intéressant que si X est strictement inférieur à 4 !
Vous n’avez traité que le cas particulier où M = X/2 et vous en tirez une généralité erronée, d’où mes précédents commentaires.
Ma conclusion était qu’il faut utiliser un VARCHAR dès que M est strictement inférieur à X-2.
Ainsi, si X vaut 8 et que je stocke les chaînes suivantes :
totototo
tatata
titi
La taille moyenne de mes chaînes vaut 6, la taille moyenne prise par un VARCHAR sera 8 octets, comme la taille prise par un CHAR.
On est donc sur le cas limite où CHAR et VARCHAR se valent sur l’espace mémoire.
Dans ce cas il faudra privilégier un CHAR car l’accès est plus rapide.
Ce qu’il faut retenir est ceci :
Le nombre d’octets alloués par SQL SERVER pour une colonne de type CHAR est fixe. Donc pour une colonne CHAR(X) SQL SERVER alloue d’office X octets pour cette colonne même si la longueur de la chaîne de caractère est inférieure à X.
Alors que pour une colonne de type VARCHAR(X) le nombre d’octets alloués par SQL SERVER est variable. Si le nombre de caractères dans la colonne VARCHAR(X) est Y (Y étant inférieur à X) alors SQL SERVER alloue Y +2 octets.
Mise en équation
—————–
¤ Pour une table avec une SEULE colonne VARCHAR(X) et une SEULE ligne on dira que le nombre moyen d’octet alloué par SQL SERVER est :
¤ Pour une table avec une SEULE colonne CHAR(X) et une SEULE ligne on dira que le nombre d’octet alloué par SQL SERVER est : X
Dans quel cas le nombre d’octets de CHAR(X) est plus petit que VARCHAR(X) ?
à condition que
X < X/2 +2 équivaut à X - X/2 < 2 équivaut à X/2 < 2 <br />
<br />
équivaut à X < 4 <br />
CQFD
A+
conclusion X
Il y a gain d’octet si
Pas de possibilité de faire de prévisualisation avant de poster ni de faire d’edit après ? Pas très pratique, ou j’ai loupé un truc.
Remplacer
Par :
Donc pour qu’un CHAR soit plus avantageux qu’un VARCHAR il faut que X <= 2 + M donc que M >= X – 2.
Je ne suis pas d’accord avec la démonstration et la conclusion.
On ne peut pas mettre plus de 4 caractères dans un CHAR(4) donc dire que le nombre de caractères doit être majoritairement inférieur ou égal à 4 n’a pas de sens.
Ma démo :
X contient la longueur maximale des chaînes qui seront stockées dans la colonne de type CHAR(X) ou VARCHAR(X).
M contient la longueur moyenne des chaînes qui seront stockées dans la colonne de type CHAR(X) ou VARCHAR(X).
D’après http://msdn.microsoft.com/fr-fr/library/ms176089.aspx, le nombre moyen d’octets occupés par une cellule est X pour un type CHAR(X) et 2+M pour un type VARCHAR(X).
Donc pour qu’un CHAR soit plus avantageux qu’un VARCHAR il faut que X = X – 2.
Pour conclure : si la moyenne de la longueur des chaînes à stocker n’est pas inférieure de plus de 2 à la longueur maximale il vaut mieux prendre un type CHAR plutôt qu’un VARCHAR. De plus, les performances d’accès n’en seront que meilleures.
Ainsi, si ma chaîne la plus grande fait 30 caractères et que la moyenne des chaînes est de 29 caractères, il vaut mieux utiliser un CHAR(30).
Si la moyenne des chaînes est de 27 caractères et qu’on veut optimiser la place prise en mémoire, il vaut mieux utiliser un VARCHAR(30).