Souvent ignorées par architectes, développeurs et chefs de projets, les collations sont des outils uniques et indispensables au traitement des données alpha numériques. Leur usage entraîne une différence de comportement des requêtes, des performances très diverses et se répercute sur la rigueur de l’écriture du code. Cet article fait le point sur ce concept et détaille l’art et la manière de s’en servir avec MS SQL Server 2000.
Pour le commun des mortels, les collations consisteraient en quelques « encas » et relèveraient du domaine alimentaire… Mais nous sommes dans un contexte informatique et le sens de ce terme a été repris de manière plus ancienne : une collation consiste en une énumération de données et leurs équivalences afin d’en déduire le comportement de traitement des chaînes de caractères notamment dans les opérations de comparaison et de tri.
1. La problématique
1.1. Caractères accentués et diacritiques
La langue anglaise possède ceci de particulier, c’est que l’on y trouve rarement des caractères accentués qui font tout le charme de certaines langues latines (le français par exemple) comme de certaines langues d’Europe du centre (tchèque, danois…). Or dans les différents jeux de caractères des systèmes informatiques les caractères accentués sont systématiquement codés après les 26 lettres non accentuées de l’alphabet, si bien qu’un tri binaire portant sur des mots contenant ou non des accents placerait les mots accentués dans un ordre ne respectant pas celui habituel du dictionnaire... Exemple, tri binaire de chaînes de caractères :
parle
parleur
parloir
parlé
Naturellement on s’attendrait à trouver le mot « parlé » en seconde position, mais le code ASCII du « é » (e accent aigu) est supérieur à celui du « z »... Les collations permettent de corriger ce problème.
Une autre caractéristique de l’usage des collations est de permettre des opérations de comparaison en s’affranchissant de certaines caractéristiques propres aux chaînes de caractères. Parmi les particularités des chaînes de caractères, on en trouve deux qui sont du domaine linguistique, une troisième à cheval sur la langue et l’édition et une quatrième de l’informatique elle-même. Pour la langue, les caractères d’une chaîne peuvent être majuscules ou minuscules, c’est ce que l’on appelle la « casse » . Outre la casse, les caractères peuvent être diacritiques ou non. Un caractère diacritique diffère du caractère de base, par l’adjonction d’un élément comme l’accent, la cédille, le tilde : éclaté, maçon, mañana (matin, lendemain en espagnol)...
1.2. Les ligatures
Enfin, un mot ou une expression peut contenir des lettres liées ensemble, ce qu‘on appelle ligature. La plus connue est l’e dans l’o que l’on trouve dans cœur ou œuf. Mais il en existe bien d’autres comme l’e dans l‘a spécifique au prénom « Lætitia » que l’on retrouve dans des expressions latines comme « ex æquo ».D’autres ligatures bien plus courantes ont été un peu oubliées. Il en est ainsi du « et » commercial qui se note &, comme de l’éperluète (arobas en anglais) noté @ pour « at » et qui signifie « chez » dans le cadre d’une indication d’adresse. Enfin notons qu’il existe des ligatures spécifiques à certaines langues, par exemple le double « s » de l’allemand, qui se note : ß. Pour couronner le tout, les typographes ont inventé toute une nouvelle série de ligature propre à présenter plus proprement les textes de manière à éviter des imbrications malhabiles de caractères qui, sans ces règles d’impression, donnerait aux caractères l’impression de se télescoper. Il en est ainsi des combinaisons de caractères « ff », « fi », « ft », « ffi », « fl », « ffl »...
fi fl
ligature des caractères fi et fl
En UNICODE par exemple la ligature œ à pour code 339 en minuscule et 338 en majuscule, tandis que la ligature ffi à pour UNICODE 64259 (décimal).
1.3. Jeux et codes
D’un point de vue informatique, les caractères ont été encodés sous différentes formes, appelés « jeux de caractères ». L’un des premiers fut celui de l’American Standard Code for Information Interchange (ASCII) et a été inventé en 1963 pour l’utilisation des Télétypes et par conséquent la communication écrite par télex et normalisé en 1968. Ce jeu de 128 caractères comprend un nombre de pseudo-caractères non imprimables permettant de « piloter » la machine (retour à la ligne ou tabulation par exemple). Mais d’autres codes ont vu le jour, comme l’EBCDIC spécifique à IBM. Puis comme le codage ASCII sur 7 bits (donc 128 combinaisons) s’avérant un juste on l’a étendu à 256 (donc deux octets). Enfin, comme il était impossible de représenter tous les alphabets, il a fallu ajouter une notion complémentaire, la page de code, de manière à spécifier quel sous-ensemble de caractères on utilise pour encoder une chaîne de caractères. Tout cela s’avérant un peu confus et peu pratique, la communauté des informaticiens décida d’inventer un nouvel encodage permettant de représenter tous les alphabets des langues parlées et écrites sur la planète, y compris celles à base d’idéogrammes comme c’est le cas de la plupart des langues asiatiques. C’est dans ce contexte qu’UNICODE vit le jour en proposant de codifier tout caractère ou idéogramme d’un quelconque alphabet sur 4 octets. Mais la problématique est qu’il est devenu impossible de comparer directement une chaîne de caractères codée en ASCII d’une autre codée en UNICODE, même si elles contiennent orthographiquement les mêmes éléments. En effet, la largeur de l'encodage de l'UNICODE est double de celui de l'ASCII. Là encore, les collations vont permettre de résoudre ce problème...
2. Choix d’une collation
Les collations permettent donc de choisir si la casse, comme les caractères diacritiques et l'encodage auront une influence sur les tris et les comparaisons. MS SQL Server 2000 permet de choisir parmi 753 collations différentes, dont les caractéristiques sont :
L'ordre induit par la langue ou le code de caractères
L’influence de la casse (noté sous SQL Server CS => Case Sensitive, CI => Case Insensitive)
L’influence des caractères diacritique (noté sous SQL Server AS => Accent Sensitive, AI => Accent Insensitive)
La sensibilité à la largeur de stockage : CII / EBCDIC sur 2 octets d'un côté et UNICODE sur 4 coctets de l'autre (noté sous SQL Server WS => Wide Sensitive)
Le respect des caractères Kana, c'est à dire la distinction phonétique des syllabes du katakana et du hiragana (langue japonaise) dans les comparaisons
Enfin, les collations binaires sont suffixées BIN.
Par exemple la collation :
French_CI_AI_KS_WS
se base sur l'ordre du dictionnaire français n'est ni sensible à la casse ni aux caractères diacritiques, mais est sensible aux caractères Kana et à la largeur du code de représentation des caractères.
Il est à noter que MS SQL Server 2000, fait la distinction entre les collations relevant de l'OS Microsoft et celles relevant de la norme SQL. Ces dernières étant la plupart du temps préfixées par le trigramme SQL.
Le piège courant serait de tomber dans la facilité et de considérer qu’il vaut mieux utiliser une collation insensible aux accents et aux caractères diacritiques. Mais cette manière de procéder possède deux inconvénients majeurs : un filtre sur une référence de mot de passe comme "AçzKé0;c9" remonterait aussi bien la donnée exactement telle que frappée, mais aussi toute combinaison avec ou sans cédille et accent, en majuscule aussi bien qu'en minuscule (ACZKE0;C9 par exemple)... Or la plupart des références de mot de passe sont sensible aux accents, majuscule et autres caractères diacritiques et cela afin d'élargir les combinaisons possibles. Second inconvénient, la performance... En effet, l'application d'une collation aux règles complexes (confusion majuscule/minuscules + confusion diacritique et lettres simple + insensibilité à la largeur) induit un coût de traduction non négligeable. A l'échelle d'une base de données ou les opérations peuvent se répéter quelques centaines de milliers de fois en fonction du nombre de lignes à comparer, les temps de réponse peuvent devenir vite importants, même lors de l'application d'une comparaison par égalité. C'est pourquoi, la plupart des grands éditeurs de progiciels, comme par exemple les ERP, imposent au serveur une collation strictement binaire afin de booster les performances.
Le pire peut même s'avérer lorsque le chef de projet ne prend pas garde à la collation qu'il choisit pour un développement client. En effet, par défaut SQL Server s'installe avec une collation lâche c'est à dire sans distinction de casse voire de caractères diacritiques. Si le serveur de destination a été installé avec une collation forte alors il se peut que certaines requêtes ne fonctionnent plus, en particulier, celles qui attendent un seul résultat (sous requêtes). Mais il y a plus grave : tous les noms d'objet (table, vues, colonnes, procédures, triggers, fonctions, types, regles, utilisateur...) deviennent eux aussi sensible à la casse parce que contenus dans les tables systèmes ! Par conséquent, une bonne pratique est de toujours développer une application avec un serveur dont la collation est la plus forte.
Avec SQL Server 2000, le choix de la collation se fait :
lors de l'installation, pour le serveur (tables master, tempdb, msdb...) et sans spécification contraire pour toutes les tables créées par la suite
lors de la création d'une base de données, pour tous les objets de la base
lors de la création d'une colonne d'une table
Là encore, pour des raisons de performance, on spécifiera au niveau du serveur, la collation la plus adaptée pour le développement. Par exception on précisera quelles colonnes de telle ou telle table aura une collation différente en fonction de besoins de traitements particuliers. Le traitement au niveau de ces colonnes sera moins performant du fait de la superposition des collations. Enfin, on évitera de spécifier une collation au niveau de la base de données.
NOTA : La liste des collations de SQL Server, est donnée à l'aide de la requête sur fonction suivante :
SELECT * FROM ::fn_helpcollations()
3. Conséquence sur les traitements
Pour comprendre les différences de traitement nous avons utilisé le jeu d'essai suivant :
Dans les filtres WHERE et HAVING, les comparaisons =, >, <, <> IN et BETWEEN, de même que le prédicat LIKE, opèrent en fonction de la collation. Exemple :
SELECT 1 as RESULT
FROM T_TEST_COLLATIONS_1_TC1
WHERE COL_French_BIN IN ('epitete', 'Hôpital')
UNION
SELECT 2
FROM T_TEST_COLLATIONS_1_TC1
WHERE COL_SQL_Latin1_General_CP1250_CI_AS IN ('epitete', 'Hôpital')
UNION
SELECT 3
FROM T_TEST_COLLATIONS_1_TC1
WHERE COL_SQL_Latin1_General_CP1251_CI_AS IN ('epitete', 'Hôpital')
UNION
SELECT 4
FROM T_TEST_COLLATIONS_1_TC1
WHERE COL_SQL_Latin1_General_CP1253_CI_AI IN ('epitete', 'Hôpital')
RESULT
------
2 (Hôpital)
3 (epitete et Hôpital)
4 (epitete et Hôpital)
Les fonctions de manipulation des chaînes de caractères comme PATINDEX ou REPLACE, tiennent compte de la collation. Exemple, recherche de la chaîne 'ho' (HÔPITAL ayant été stocké) :
SELECT 1 as NumCol, MAX(PATINDEX ('%ho%', COL_French_BIN)) as Trouve
FROM T_TEST_COLLATIONS_1_TC1
UNION
SELECT 2, MAX(PATINDEX ('%ho%', COL_SQL_Latin1_General_CP1250_CI_AS))
FROM T_TEST_COLLATIONS_1_TC1
UNION
SELECT 3, MAX(PATINDEX ('%ho%', COL_SQL_Latin1_General_CP1251_CI_AS))
FROM T_TEST_COLLATIONS_1_TC1
UNION
SELECT 4, MAX(PATINDEX ('%ho%', COL_SQL_Latin1_General_CP1253_CI_AI) )
FROM T_TEST_COLLATIONS_1_TC1
Attention aux sous requêtes ne devant ramener en principe qu'un seul résultat. La différence de collation peut en ramener plusieurs et la requête échouer. Exemple :
SELECT CATALOG_NAME,
(SELECT COL_French_BIN
FROM T_TEST_COLLATIONS_1_TC1
WHERE COL_French_BIN = 'écœuré')
FROM master.INFORMATION_SCHEMA.SCHEMATA
SELECT CATALOG_NAME,
(SELECT COL_SQL_Latin1_General_CP1250_CI_AS
FROM T_TEST_COLLATIONS_1_TC1
WHERE COL_SQL_Latin1_General_CP1250_CI_AS
= 'écœuré')
FROM master.INFORMATION_SCHEMA.SCHEMATA
Serveur : Msg 512, Niveau 16, État 1, Ligne 1
La sous-requête a retourné plusieurs valeurs. Cela n'est pas
autorisé quand la sous-requête suit =, !=, <, <= , >,
>= ou quand elle est utilisée en tant qu'expression.
4. Résoudre les problèmes...
Dand le cas d'une collation sensible à la largeur il suffit d'utiliser l'opérateur CAST pour comparer deux chaînes de caractères :
SELECT *
FROM maTable
WHERE CAST(ColUnicode ASVARCHAR(32)) = ColASCII
Dans le cas d'une collation forte, pour s'affranchir de la casse on peut faire :
SELECT *
FROM maTable
WHERE UPPER(maColonne) = 'TOTO'
Pour comparer deux colonnes dotées d'une collation différente, il faut imposer quelle collation doit l'emporter sur l'autre, à l'aide d'une spécification de recollement COLLATE :
SELECT *
FROM MaTable
WHERE GreekCol = LatinCol COLLATE greek_ci_as
Dans le cas d'une collation forte, il n'y a pas de moyen simple de s'affranchir des caractères diacritiques. En revanche une bonne méthode consiste à se fabriquer une UDF (fonction utilisateur) afin de remplacer les caractères diacrités en caractères simples, bien que la norme SQL 1992 ait prévu une spécification de "translation" d'une collation envers une autre, peu de SGBDR en sont doté ! Pour ma part, j'utilise une UDF (fonction) FN_TRANSLATE comme ceci :
Chacune des lettres spécifiées dans le deuxième argument est remplacée par une lettre de même position dans le troisième argument (voir code en fin de document).
Pour comparer des colonnes dotées d'une collation faible et afin de distinguer "maçoné" de "Macone" il faut effectuer la comparaison après un transtypage en binaire :
...
WHERE CAST('maçoné' AS VARBINARY(16)) = CAST('macone' AS VARBINARY(16))
mais ce double transtypage s'avère coûteux !
5. Conclusion
Évitez les collations faibles. Installez SQL Server avec une collation forte, voire binaire. N'utilisez des collations faibles que de manière exceptionnelle pour des colonnes relevant de traitements particuliers. Utiliser une norme interne de développement pour la formation de vos noms d'objet. Une bonne habitude est de respecter les standards de fait et la norme SQL afin que vos noms n'utilise que les 36 caractères alphanumériques (26 lettres en majuscule) et le blanc souligné. Écrivez votre code Transact SQL et vos requêtes toujours en majuscule ce qui permet de distingué aisément dans un code d'application cliente ce qui relève d'une exécution locale (code en minuscule) de ce qui s'exécute sur le serveur (code en majuscule des requêtes et procédures stockées).
Jim Melton, Alan Simon : Undestanding the New SQL - Morgan Kaufmann 1993 pages 362 à 369 Chris Date, Hugh Darwen : A guide to the SQL Standard - Addison Wesley 1999 pages 288 à 307 Peter Gulutzan, Trudy Pelzer : SQL-99 Complete, Really - R&D Books 1999 pages 95 à 162, 291 à 316, 449 à 462 Joe Celko : Data & DataBases : Concepts in Practice - Morgan Kaufmann 1999 pages 101 à 106
8. ANNEXE : codes
8.1. Le code Transact SQL de l'UDF FN_TRANSLATE
/*************************************************************************/
-- remplacement par substitution de caractères-- Frédéric BROUARD - 2002-09-11
/*************************************************************************/
-- exemple : FN_TRANSLATE('à Paris...', 'àP.', 'ap') => 'a paris'CREATE FUNCTION FN_TRANSLATE (@VALIN VARCHAR (8000),
@FROMVARCHAR(256), @TO VARCHAR(256))
RETURNSVARCHAR (8000)
ASBEGIN-- effets de bordIF @VALIN ISNULL
RETURN NULLIF @FROMISNULLOR @TO ISNULL
RETURN NULLIF LEN(@VALIN) = 0
RETURN @VALIN
-- initialisationDECLARE @I INTEGERDECLARE @OUT VARCHAR(8000)
SET @OUT = ''
-- lecture caractère par caractèreSET @I =1
WHILE @I <= LEN(@VALIN)
BEGINIF PATINDEX('%' + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM) > 0
BEGINIF LEN(@TO) >= PATINDEX('%'
+ SUBSTRING(@VALIN, @I, 1)
+ '%', @FROM)
SET @OUT = @OUT
+ SUBSTRING(@TO, PATINDEX('%'
+ SUBSTRING(@VALIN, @I, 1)+ '%', @FROM), 1)
ENDELSESET @OUT = @OUT + SUBSTRING(@VALIN, @I, 1)
SET @I = @I + 1
END
RETURN @OUT
END
8.2. Une table pour comparer les principales collations d'usage en français et leurs effets
