64
Chapitre 4. Optimisation des objets et de la structure de la base de données
qui vous donne le remplissage moyen de la colonne, contre la taille moyenne
réelle, et la différence en octets totaux.
Attention, cette requête n’est valable que pour les colonnes ANSI. Divisez DATALENGTH() par deux, dans le cas de colonnes UNICODE.
Comment connaître les colonnes de taille variable dans votre base de données ?
Une requête de ce type va vous donner la réponse :
SELECT
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) as tbl,
QUOTENAME(COLUMN_NAME) as col,
DATA_TYPE + ' (' +
CASE CHARACTER_MAXIMUM_LENGTH
WHEN -1 THEN 'MAX'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH as VARCHAR(20))
END + ')' as type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE 'var%' OR DATA_TYPE LIKE 'nvar%'
ORDER BY tbl, ORDINAL_POSITION;
Vous pouvez aussi utiliser la fonction COLUMNPROPERTY().
En conclusion, la règle de base est assez simple : utilisez le plus petit type de données possible. Si votre colonne n’a pas besoin de stocker des chaînes dans des langues nécessitant l’UNICODE (chinois, japonais, etc.) créez des colonnes ANSI.
Elles seront raisonnablement valables pour toutes les langues utilisant un alphabet
latin. Elles vous feront économiser 50 % d’espace. Certains outils Microsoft ont tendance à créer automatiquement des colonnes UNICODE. N’hésitez pas à changer
cela à l’aide d’un ALTER TABLE par exemple. Il n’y a pratiquement aucun risque d’effet
de bord dans votre code SQL, à part l’utilisation de DATALENGTH ou de LIKE, comme
nous l’avons vu précédemment. La différence de temps d’accès par le moteur de stockage entre une colonne fixe ou variable, en termes de recherche de position de la
chaîne, est négligeable. En revanche, la différence de volume de données à transmettre lors d’un SELECT, peut peser sur les performances. Comme règle de base, n’utilisez des CHAR que pour des longueurs inférieures à 10 octets, et de préférence sur des
colonnes dans lesquelles vous êtes sûrs de stocker toujours la même taille de chaîne
(par exemple, pour une colonne de codes ISO de pays, ne créez pas un VARCHAR(2),
mais un CHAR(2), car le VARCHAR crée deux octets supplémentaires pour le pointeur
qui permet d’indiquer sa position dans la ligne). Évitez les CHAR de grande taille :
vous risquez de remplir inutilement votre fichier de données. Imaginons que vous
créiez une table ayant un CHAR(5000) ou un NCHAR(2500), vous auriez ainsi la garantie
que chaque page de données ne peut contenir qu’une ligne. Si vous ne stockez que
dix caractères dans cette colonne, vous avec besoin, pour dix octets, d’occuper en
réalité 8 060 octets…
Chapitre 4. Optimisation des objets et de la structure de la base de données
qui vous donne le remplissage moyen de la colonne, contre la taille moyenne
réelle, et la différence en octets totaux.
Attention, cette requête n’est valable que pour les colonnes ANSI. Divisez DATALENGTH() par deux, dans le cas de colonnes UNICODE.
Comment connaître les colonnes de taille variable dans votre base de données ?
Une requête de ce type va vous donner la réponse :
SELECT
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) as tbl,
QUOTENAME(COLUMN_NAME) as col,
DATA_TYPE + ' (' +
CASE CHARACTER_MAXIMUM_LENGTH
WHEN -1 THEN 'MAX'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH as VARCHAR(20))
END + ')' as type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE 'var%' OR DATA_TYPE LIKE 'nvar%'
ORDER BY tbl, ORDINAL_POSITION;
Vous pouvez aussi utiliser la fonction COLUMNPROPERTY().
En conclusion, la règle de base est assez simple : utilisez le plus petit type de données possible. Si votre colonne n’a pas besoin de stocker des chaînes dans des langues nécessitant l’UNICODE (chinois, japonais, etc.) créez des colonnes ANSI.
Elles seront raisonnablement valables pour toutes les langues utilisant un alphabet
latin. Elles vous feront économiser 50 % d’espace. Certains outils Microsoft ont tendance à créer automatiquement des colonnes UNICODE. N’hésitez pas à changer
cela à l’aide d’un ALTER TABLE par exemple. Il n’y a pratiquement aucun risque d’effet
de bord dans votre code SQL, à part l’utilisation de DATALENGTH ou de LIKE, comme
nous l’avons vu précédemment. La différence de temps d’accès par le moteur de stockage entre une colonne fixe ou variable, en termes de recherche de position de la
chaîne, est négligeable. En revanche, la différence de volume de données à transmettre lors d’un SELECT, peut peser sur les performances. Comme règle de base, n’utilisez des CHAR que pour des longueurs inférieures à 10 octets, et de préférence sur des
colonnes dans lesquelles vous êtes sûrs de stocker toujours la même taille de chaîne
(par exemple, pour une colonne de codes ISO de pays, ne créez pas un VARCHAR(2),
mais un CHAR(2), car le VARCHAR crée deux octets supplémentaires pour le pointeur
qui permet d’indiquer sa position dans la ligne). Évitez les CHAR de grande taille :
vous risquez de remplir inutilement votre fichier de données. Imaginons que vous
créiez une table ayant un CHAR(5000) ou un NCHAR(2500), vous auriez ainsi la garantie
que chaque page de données ne peut contenir qu’une ligne. Si vous ne stockez que
dix caractères dans cette colonne, vous avec besoin, pour dix octets, d’occuper en
réalité 8 060 octets…