À quoi servent les collations

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.

I-A. Caractères accentués et diacritiques▲

parle
parleur
parloir
parlé

I-B. Les ligatures▲

I-C. Jeux et codes▲

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 octets 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.

Exemple :

SQL_AltDiction_Cp850_CS_AS
SQL_Latin1_General_Cp850_CI_AI

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 majuscules aussi bien qu'en minuscules (ACZKE0;C9 par exemple)… Or la plupart des références de mot de passe sont sensibles aux accents, majuscules 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 majuscules/minuscules + confusion diacritique et lettres simples + insensibilité à la largeur) induit un coût de traduction non négligeable. À l'échelle d'une base de données où 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, 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 (tables, vues, colonnes, procédures, triggers, fonctions, types, règles, utilisateurs…) deviennent eux aussi sensibles à la casse parce que contenus dans les tables système !
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()

Pour comprendre les différences de traitement, nous avons utilisé le jeu d'essai suivant :

CREATE TABLE T_TEST_COLLATIONS_1_TC1
( COL_French_BIN                      VARCHAR(8)
      COLLATE  French_BIN
, COL_SQL_Latin1_General_CP1250_CI_AS VARCHAR(8)
      COLLATE  SQL_Latin1_General_CP1250_CI_AS
, COL_SQL_Latin1_General_CP1251_CI_AS VARCHAR(8)
      COLLATE  SQL_Latin1_General_CP1251_CI_AS
, COLSQL_Latin1_General_CP1253_CI_AI  VARCHAR(8)
      COLLATE  SQL_Latin1_General_CP1253_CI_AI)

INSERT INTO T_TEST_COLLATIONS_1_TC1
       VALUES ('épitète', 'épitète', 'épitète', 'épitète')
INSERT INTO T_TEST_COLLATIONS_1_TC1
       VALUES ('écœuré',  'écœuré',  'écœuré',  'écœuré' )
INSERT INTO T_TEST_COLLATIONS_1_TC1
       VALUES ('ÉCŒURÉ',  'ÉCŒURÉ',  'ÉCŒURÉ',  'ÉCŒURÉ' )
INSERT INTO T_TEST_COLLATIONS_1_TC1
       VALUES ('HÔPITAL', 'HÔPITAL', 'HÔPITAL', 'HÔPITAL')

Lors des insertions, suivant la collation utilisée, les caractères peuvent être stockés tels quels, transformés ou absents…
Exemple :

1         2         3         4
-------   -------   -------   -------
épitète   épitete   epitete   epitete
écœuré    écouré    ec?ure    ec?ure
ÉCŒURÉ    ÉCOURÉ    EC?URE    EC?URE
HÔPITAL   HÔPITAL   HOPITAL   HOPITAL

1) French_BIN
2) SQL_Latin1_General_CP1250_CI_AS
3) SQL_Latin1_General_CP1251_CI_AS
4) SQL_Latin1_General_CP1253_CI_AI

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

NumCol      Trouve
----------- -----------
1           0
2           0
3           1
4           1

La clause ORDER BY est bien entendu la plus sensible à la collation.
Exemple :

SELECT TOP 1 1 as NumCol, COL_French_BIN
FROM   T_TEST_COLLATIONS_1_TC1
ORDER BY 2

SELECT TOP 1 2 as NumCol, COL_SQL_Latin1_General_CP1250_CI_AS
FROM   T_TEST_COLLATIONS_1_TC1
ORDER BY 2

NumCol      COL_French_BIN
----------- --------------
1           HÔPITAL

NumCol      COL_SQL_Latin1_General_CP1250_CI_AS
----------- -----------------------------------
2           écouré

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

CATALOG_NAME             
---------------- --------
master           écœuré
tempdb           écœuré
model            écœuré
msdb             écœuré
pubs             écœuré
Northwind        écœuré

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.

Dans 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 AS VARCHAR(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és ! Pour ma part, j'utilise une UDF (fonction) FN_TRANSLATE comme ceci :

FN_TRANSLATE(MaColonne, 'àâäçéèêëîïôöùûüÿ',
                        'aaaceeeeiioouuuy')

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 !

É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'utilisent que les 36 caractères alphanumériques (26 lettres en majuscules) et le blanc souligné.
Écrivez votre code Transact SQL et vos requêtes toujours en majuscules ce qui permet de distinguer aisément dans un code d'application cliente ce qui relève d'une exécution locale (code en minuscules) de ce qui s'exécute sur le serveur (code en majuscules des requêtes et procédures stockées).

Peter Gulutzan, collations (dbAzine)
Ligature, typographie: http://fr.wikipedia.org/wiki/Ligature_(typographie)
Articles Microsoft :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_6ttf.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_1pwz.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_8uer.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_collation_6gfn.asp
Frédéric Brouard, SQL jeux de caractères et collations (point n°4)

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

VIII-A. 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),
                              @FROM VARCHAR(256), @TO VARCHAR(256)) 
RETURNS VARCHAR (8000)
AS 
BEGIN 
-- effets de bord
   IF @VALIN IS NULL
      RETURN NULL
   IF @FROM IS NULL OR @TO IS NULL
      RETURN NULL
   IF LEN(@VALIN) = 0
      RETURN @VALIN
-- initialisation
   DECLARE @I INTEGER
   DECLARE @OUT VARCHAR(8000)
   SET @OUT = ''
-- lecture caractère par caractère
   SET @I =1
   WHILE @I <= LEN(@VALIN)
   BEGIN
      IF PATINDEX('%' + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM) > 0
      BEGIN
         IF LEN(@TO) >= PATINDEX('%'
                        + SUBSTRING(@VALIN, @I, 1)
                        + '%', @FROM)
            SET @OUT = @OUT
                       + SUBSTRING(@TO, PATINDEX('%'
                       + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM), 1)
      END
      ELSE
         SET @OUT = @OUT + SUBSTRING(@VALIN, @I, 1)
      SET @I = @I + 1
   END
   RETURN @OUT
END

VIII-B. Une table pour comparer les principales collations d'usage en français et leurs effets▲

CREATE TABLE T_TEST_COLLATIONS_TCL
( COL001 VARCHAR(8) COLLATE  French_BIN
, COL002 VARCHAR(8) COLLATE  French_CI_AI
, COL003 VARCHAR(8) COLLATE  French_CI_AI_WS
, COL004 VARCHAR(8) COLLATE  French_CI_AI_KS
, COL005 VARCHAR(8) COLLATE  French_CI_AI_KS_WS
, COL006 VARCHAR(8) COLLATE  French_CI_AS
, COL007 VARCHAR(8) COLLATE  French_CI_AS_WS
, COL008 VARCHAR(8) COLLATE  French_CI_AS_KS
, COL009 VARCHAR(8) COLLATE  French_CI_AS_KS_WS
, COL010 VARCHAR(8) COLLATE  French_CS_AI
, COL011 VARCHAR(8) COLLATE  French_CS_AI_WS
, COL012 VARCHAR(8) COLLATE  French_CS_AI_KS
, COL013 VARCHAR(8) COLLATE  French_CS_AI_KS_WS
, COL014 VARCHAR(8) COLLATE  French_CS_AS
, COL015 VARCHAR(8) COLLATE  French_CS_AS_WS
, COL016 VARCHAR(8) COLLATE  French_CS_AS_KS
, COL017 VARCHAR(8) COLLATE  French_CS_AS_KS_WS
, COL018 VARCHAR(8) COLLATE  Latin1_General_BIN
, COL019 VARCHAR(8) COLLATE  Latin1_General_CI_AI
, COL020 VARCHAR(8) COLLATE  Latin1_General_CI_AI_WS
, COL021 VARCHAR(8) COLLATE  Latin1_General_CI_AI_KS
, COL022 VARCHAR(8) COLLATE  Latin1_General_CI_AI_KS_WS
, COL023 VARCHAR(8) COLLATE  Latin1_General_CI_AS
, COL024 VARCHAR(8) COLLATE  Latin1_General_CI_AS_WS
, COL025 VARCHAR(8) COLLATE  Latin1_General_CI_AS_KS
, COL026 VARCHAR(8) COLLATE  Latin1_General_CI_AS_KS_WS
, COL027 VARCHAR(8) COLLATE  Latin1_General_CS_AI
, COL028 VARCHAR(8) COLLATE  Latin1_General_CS_AI_WS
, COL029 VARCHAR(8) COLLATE  Latin1_General_CS_AI_KS
, COL030 VARCHAR(8) COLLATE  Latin1_General_CS_AI_KS_WS
, COL031 VARCHAR(8) COLLATE  Latin1_General_CS_AS
, COL032 VARCHAR(8) COLLATE  Latin1_General_CS_AS_WS
, COL033 VARCHAR(8) COLLATE  Latin1_General_CS_AS_KS
, COL034 VARCHAR(8) COLLATE  Latin1_General_CS_AS_KS_WS
, COL035 VARCHAR(8) COLLATE  SQL_1xCompat_CP850_CI_AS
, COL036 VARCHAR(8) COLLATE  SQL_AltDiction_CP1253_CS_AS
, COL037 VARCHAR(8) COLLATE  SQL_AltDiction_CP850_CI_AI
, COL038 VARCHAR(8) COLLATE  SQL_AltDiction_CP850_CI_AS
, COL039 VARCHAR(8) COLLATE  SQL_AltDiction_CP850_CS_AS
, COL040 VARCHAR(8) COLLATE  SQL_AltDiction_Pref_CP850_CI_AS
, COL041 VARCHAR(8) COLLATE  SQL_EBCDIC037_CP1_CS_AS
, COL042 VARCHAR(8) COLLATE  SQL_EBCDIC297_CP1_CS_AS
, COL043 VARCHAR(8) COLLATE  SQL_EBCDIC273_CP1_CS_AS
, COL044 VARCHAR(8) COLLATE  SQL_EBCDIC277_CP1_CS_AS
, COL045 VARCHAR(8) COLLATE  SQL_EBCDIC278_CP1_CS_AS
, COL046 VARCHAR(8) COLLATE  SQL_EBCDIC280_CP1_CS_AS
, COL047 VARCHAR(8) COLLATE  SQL_EBCDIC284_CP1_CS_AS
, COL048 VARCHAR(8) COLLATE  SQL_EBCDIC285_CP1_CS_AS
, COL049 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1_CI_AI
, COL050 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1_CI_AS
, COL051 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1_CS_AS
, COL052 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1250_CI_AS
, COL053 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1250_CS_AS
, COL054 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1251_CI_AS
, COL055 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1251_CS_AS
, COL056 VARCHAR(8) COLLATE  SQL_Polish_CP1250_CS_AS
, COL057 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1253_CI_AI
, COL058 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1253_CI_AS
, COL059 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1253_CS_AS
, COL060 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1254_CI_AS
, COL061 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1254_CS_AS
, COL062 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1255_CI_AS
, COL063 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1255_CS_AS
, COL064 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1256_CI_AS
, COL065 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1256_CS_AS
, COL066 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1257_CI_AS
, COL067 VARCHAR(8) COLLATE  SQL_Latin1_General_CP1257_CS_AS
, COL068 VARCHAR(8) COLLATE  SQL_Latin1_General_CP437_BIN
, COL069 VARCHAR(8) COLLATE  SQL_Latin1_General_CP437_CI_AI
, COL070 VARCHAR(8) COLLATE  SQL_Latin1_General_CP437_CI_AS
, COL071 VARCHAR(8) COLLATE  SQL_Latin1_General_CP437_CS_AS
, COL072 VARCHAR(8) COLLATE  SQL_Latin1_General_CP850_BIN
, COL073 VARCHAR(8) COLLATE  SQL_Latin1_General_CP850_CI_AI
, COL074 VARCHAR(8) COLLATE  SQL_Latin1_General_CP850_CI_AS
, COL075 VARCHAR(8) COLLATE  SQL_Latin1_General_CP850_CS_AS
, COL076 VARCHAR(8) COLLATE  SQL_Latin1_General_Pref_CP1_CI_AS
, COL077 VARCHAR(8) COLLATE  SQL_Latin1_General_Pref_CP437_CI_AS
, COL078 VARCHAR(8) COLLATE  SQL_Latin1_General_Pref_CP850_CI_AS
, COL079 VARCHAR(8) COLLATE  SQL_MixDiction_CP1253_CS_AS)

VIII-C. La procédure d'insertion dans la table de comparaison▲

CREATE PROCEDURE SP_INSERT_TABLE_COL @VAL VARCHAR(8)
AS

DECLARE @SQL VARCHAR(8000)
SET @SQL = 'INSERT INTO T_TEST_COLLATIONS_TCL VALUES ('
DECLARE @I INT
SET @I = 1
WHILE @I < 80
BEGIN
   SET @SQL = @SQL + ''''+@VAL+''','
   SET @I = @I + 1
END

SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) -1)+')'

EXEC(@SQL)

GO

SP_INSERT_TABLE_COL 'épitète'
SP_INSERT_TABLE_COL 'epitete'
SP_INSERT_TABLE_COL 'ÉPITÈTE'
SP_INSERT_TABLE_COL 'EPITETE'
SP_INSERT_TABLE_COL 'écœuré'
SP_INSERT_TABLE_COL 'ÉCŒURÉ'
SP_INSERT_TABLE_COL 'HÔPITAL'