Discussion :

[OrthosGroupe]

Bonjour, je viens de tomber sur un cas que je ne m'explique pas, peut-être quelqu'un pourrait expliquer le phénomène suivant sur un serveur SQL 2008R2 sp2 enterprise:

Dans une table "Occupant"
j'ai une colonne "NomPrenom" varchar(80) NOT NULL COLLATE French_CS_AS
donc sensible à la casse.

J'ai 39 lignes qui commencent par un "d" minuscule
J'ai 17 751 lignes qui commencent par un "D" majuscule

La requête suivante qui utilise la fonction LIKE retourne 17751, ce qui est normal

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select COUNT(*)
from sImmo.Occupant
where NomPrenom like 'D%'

La requête suivante qui utilise les opérateurs de comparaison retourne 17 790, soit majuscules et minuscules confondues. Pourquoi perd-on la sensibilité à la casse avec ces opérateurs?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
select COUNT(*)
from sImmo.Occupant
where NomPrenom>='D'
AND NomPrenom< 'E'

[rudib]

Effectivement l'ordre n'est pas le même selon la chaîne. Par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH cte AS (
	SELECT * FROM (VALUES ('D'), ('d'), ('E')) as t(n)
)
SELECT *
FROM cte
ORDER BY n COLLATE French_CS_AS;
 
WITH cte AS (
	SELECT * FROM (VALUES ('Damien'), ('dominique'), ('Ernest')) as t(n)
)
SELECT *
FROM cte
ORDER BY n COLLATE French_CS_AS;

Cela doit venir du fait que French_CS_AS a un ordre de type dictionnaire. La comparaison doit être ok, mais ton >= et < se base sur l'ordre, qui dépend de la façon dont la collation gère l'ordre des chaînes. Cela va marcher si tu utilises une collation binaire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH cte AS (
	SELECT * FROM (VALUES ('D'), ('d'), ('E')) as t(n)
)
SELECT *
FROM cte
ORDER BY n COLLATE French_BIN;
 
WITH cte AS (
	SELECT * FROM (VALUES ('Damien'), ('dominique'), ('Ernest')) as t(n)
)
SELECT *
FROM cte
ORDER BY n COLLATE French_BIN;

[SQLpro]

Si vous vouliez conserver la sensibilité au majuscules, il faut mettre une collation binaire et non pas CS.
En effet, dans l'ordre de tri du dictionnaire les lettres sont classées dans l'ordre alphabétique quelque soit la casse... Autrement dit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
WHERE NomPrenom>='D'
AND NomPrenom< 'E'

vous renverra les noms commençant par D, puis d, puis E....

Donc :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT COUNT(*)
FROM sImmo.Occupant
WHERE NomPrenom COLLATE French_BIN >='D'
AND NomPrenom COLLATE French_BIN < 'E'

A +

[aieeeuuuuu]

Bonjour,

Cela provient de l’algorithme de comparaison unicode, qui est également appliqué pour les colonnes non unicode.

Cet algo ne fonctionne pas caractère par caractère, mais compare les chaines dans leur ensemble, (afin entre autre de prendre en compte la longueur de la chaine), en résumé de la façon suivante

1/ comparaison insensible à la casse et aux accents (CI AI)
2/ en cas d'égalité, comparaison sensible à la casse et insensible aux accents (CS AI)
3/ en cas d'égalité, comparaison sensible à la casse et aux accents (CS AS)
4/ si toujours aucune différence, alors les chaines sont égales.

Ceci explique pourquoi vous avez l'impression de "perdre la sensibilité à la casse"

Ceci explique aussi le résultat "surprenant" a première vue de la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT 
	CASE WHEN 'a' > 'A'  COLLATE FRENCH_CS_AS THEN 1 ELSE 0 END AS a_gt_A,
	CASE WHEN 'aa' > 'A' COLLATE FRENCH_CS_AS THEN 1 ELSE 0 END AS aa_gt_A,
	CASE WHEN 'bb' > 'BA' COLLATE FRENCH_CS_AS THEN 1 ELSE 0 END AS bb_gt_BA,
	CASE WHEN 'bb' > 'BB' COLLATE FRENCH_CS_AS THEN 1 ELSE 0 END AS bb_gt_BB,
	CASE WHEN 'a' > 'E'  COLLATE FRENCH_CS_AS THEN 1 ELSE 0 END AS a_gt_E,
	CASE WHEN 'aa' > 'E' COLLATE FRENCH_CS_AS THEN 1 ELSE 0 END AS aa_gt_E,
	CASE WHEN 'a' < 'E'  COLLATE FRENCH_CS_AS THEN 1 ELSE 0 END AS a_lt_E,
	CASE WHEN 'aa' < 'E' COLLATE FRENCH_CS_AS THEN 1 ELSE 0 END AS a_lt_E
 
--Resultat :
a_gt_A	aa_gt_A	bb_gt_BA	bb_gt_BB	a_gt_E	aa_gt_E	a_lt_E	a_lt_E
0	1	1		0		0	0	1	1

En conclusion, vous pouvez effectivement passer par une collation binaire.
Pour ma part, je préfère m'en tenir au bon vieux LIKE, qui est extrêmement puissant, et parfaitement optimisé sur SQL Server. En sus, je le trouve bien plus lisible, avec des résultats plus conformes à ceux qu'on attend : moins de surprises !

[OrthosGroupe]

Bonjour et merci à tous pour ces explications, je dormirai un peu moins bête ce soir.