Commençons avec un cas concret :

create table cities (name varchar(50));
insert into cities values ('Geneve');
insert into cities values ('Genève');
insert into cities values ('genève');
insert into cities values ('geneve');
insert into cities values ('Zürich');
insert into cities values ('zurich');

La recherche suivante ne va pas renvoyer les mêmes résultats selon les paramétrages des SGBD :

select name from cities where name='geneve';

Le cas SQL Server

Sur ce SGBD, lors de la création d'une instance l'assistant va vous proposer différentes "collations". La "collation" choisie sera celle utilisée pour les bases systèmes et celle par défaut celle pour toutes les futures bases créées.
La "collation" regroupe plusieurs concepts :

  • la façon dont les caractères sont encodés dans les fichiers (à un certain niveau, il faut bien transformer des caractères en binaire) ; c'est le code page (note : les textes Unicode ont un code page qui n'est pas paramétrable)
  • les règles de tris (plus de détails ci-dessous)
  • la "sentibilité" de 4 types : CaseSensitivity ; AccentSensitivity ; KanatypeSensitive ; WidthSensitivity

Vous trouverez souvent dans nos contrées le Latin1_General_AS_CI : code page Latin1, tris en mode "général", sensible aux accents et insensible à la casse. Les 2 autres sensibilités (Kana & With) ne sont pas actives si non précisées ; donc les sensibilités sont AS_CI_KI_WI.

Collation par défaut et ses impacts

Bien entendu, la collation peut être définie à la création d'une base. On peut donc avoir une instance qui est en collation par défaut Latin1_General_AS_CI qui contient une base en collation Bengali_AI_CI. Par contre, le fait de changer la collation par défaut de l'instance n'est pas simple car il faut transformer les bases systèmes (master, msdb, model). C'est donc un paramétrage à ne pas prendre à la légère.

Changer de collation pour certaines requêtes

Changer la collation d'une base de données une fois qu'elle est créée est lourd mais nous pouvons changer la collation pour une requête !
Dans ce cas, le moteur va fonctionner comme si la base avait une autre collation et vous pouvez donc faire des SELECT sans être sensibles à la casse alors que normalement la base l'est.

Et Oracle ?

Sur ce SGBD, 2 éléments de la collation Microsoft sont bien distinct : l'encodage (Charset) et les règles de tris et sensibilités. L'encodage sur disque est choisi lors de la création de la base. Comme sur SQL Server, changer d'encodage après création de la base est une opération lourde (obligation de reécrire toute la base). L'encodage est généralement WE8ISO8859P15 (Western European 8-bit ISO 8859 Part 15) ou WE8MSWIN1252 (Characterset Windows) ; et l'UTF8 arrive de plus en plus (conseillé par Oracle depuis la 11g).
Ce paramètre est défini à la création de la base de données et peut être trouvé dans le paramètres NLS : select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

Les paramètres NLS

Sous la dénomination National Language Support, Oracle propose différents moyen de présenter les données telles qu'elles sont attendues par les utilisateurs de tel ou tel pays. Un exemple simple : dans la base de donnée, les dates sont stockées en nombres (ex : 09/06/2014) mais si vous affichez la date avec le mois écrit en lettre (ex : 09 Juin 2014) selon que vous soyez en Suisse ou en Italie, l'affichage sera différent (les mois sont traduits). Le paramètre NLS_LANG permet de spécifier la façon dont vous attendez les réponses.

Point spécifique à Oracle : ce paramètre NLS_LANG peut être positionné au niveau de l'instance, de la base de données et de la session cliente. Pour la même requête (select birthday from employee) le résultat sera affiché de façon différente pour 2 sessions ayant un NLS_LANG différent.
La sélection du NLS utile se fait par ordre de priorité : niveau session ; si non précisé, niveau instance; si non précisé, niveau database.

Quel rapport avec la sensibilité aux tris ?

Les paramètres NLS ou la sensibilité SQL Server (CaseSensitivity ; AccentSensitivity etc...) ont plus d'impact que simplement traduire les mois des dates. Ils définissent les tris : en allemand, le ß est considéré comme un ss lors d'un tri alphabétique ; en français, les é sont triés comme des e. Cela partait évident mais si on pense à l'encodage des caractères ça ne l'est plus du tout : en WE8MSWIN1252, le e est codé 0065 alors que le é 00E9.
De plus, si cela impacte les tris (ORDER BY), c'est aussi le cas pour un ensemble de commandes SQL : UNION, MIN, MAX, DISTINCT etc...

Passons aux exemples avec le DISTINCT

Nous allons utiliser la table créée en début de cet article et voir différents cas.

SQL Server

Pour ce test, j'ai utilisé un base avec une collation : SQL_Latin1_General_CP1_CI_AS

select distinct name from cities;
01.png

Utilisation d'une collation non sensible aux accents :

select distinct name COLLATE Latin1_General_CI_AI from cities;
02.png

Utilisation d'une collation sensible à la casse et aux accents :

select distinct name COLLATE Latin1_General_CS_AS as name from cities;
03.png

Oracle

Pour cet exemple, voici mes paramètres NLS (instance, database et session).

select * from
(select 'SESSION' SCOPE,nsp.* from nls_session_parameters nsp
union
select 'DATABASE' SCOPE,ndp.* from nls_database_parameters ndp
union
select 'INSTANCE' SCOPE,nip.* from nls_instance_parameters nip
) a
pivot (LISTAGG(VALUE) WITHIN GROUP (ORDER BY SCOPE)
FOR SCOPE in ('SESSION' as "SESSION",'DATABASE' as DATABASE,'INSTANCE' as INSTANCE));

10.png
Je suis donc selon l'ordre de priorité des NLS en NLS_SORT = FRENCH et NLS_COMP = BINARY ce qui signifie que les tris se feront selon les règles françaises et les comparaisons selon le données binaires (où un accent é est bien différent d'un e).

select distinct name from cities;
11.png

Modification de la session pour ne plus être sensible à la casse ni aux accents :

alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT='FRENCH_AI';

On modifie le NLS_COMP pour faire les comparaisons en fonction de la langue et on demande à ce que les tris ne soient pas sensibles aux accents. A noter, sur Oracle, si vous n'êtes pas sensible aux accents, vous ne l'êtes pas non plus à la casse. Le mode AI_CS n'est pas possible chez Oracle.

select distinct name from cities;
12.png

Utilisation d'une collation uniquement sensible aux accents :

alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT='FRENCH_CI';
select distinct name from cities;
13.png

Conclusion

Vous connaissez maintenant les principes de fonctionnement des collations et de certains paramètres NLS chez Oracle. Sachez que sur Oracle, les paramètres NLS amènent souvent des interrogations car les DBA ne reproduisent pas les cas puisque cela dépend des paramètres NLS côté client.