Discussion :

[cmako]

Bonjour,

Je viens de me rendre compte qu'on a un problème de collation.
1. Le serveur est paramétré avec la collation French_CI_AS tandis que les bases sont SQL_Latin1_General_CP1_CI_AS.
Le problème est que lorsque je crée une table temporaire (avec CREATE TABLE) elle se crée avec la collation du serveur (French_CI_AS).
D'où ma première question : comment fait-on pour créer une table temporaire avec la collation de la base et non du serveur?

2. Lorsque j'essaye de comparer les champs avec les différentes collations j'ai une erreur:

Code :

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

1
2
Msg 468, Level 16, State 9, Line 77
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "French_CI_AS" in the equal to operation.

D'où ma question : comment fait-on pour comparer les tables (les champs) avec les différentes collations?

3. Lorsque je clique-droit dans les propriétés des utilisateurs (logins) et lorsque je clique sur Securable j'obtiens le message d'erreur cf. image. D'où viens le problème? Est ce que les utilisateurs ont aussi une collation par défaut ou bien l'IHM a une collation differente?
Y a-t-il moyen de résoudre ce problème?

[elsuket]

Bonjour,

Dans tous les cas retenez que les comparaisons de chaînes sont déjà lourdes quand elles sont réalisées sur des valeurs qui ont la même collation : imaginez le surplus de calcul que cela génère lorsque les collations sont différentes ...

comment fait-on pour créer une table temporaire avec la collation de la base et non du serveur?

Il vous "suffit" d'ajouter le nom de la collation à utiliser avec la clause COLLATE :

Code :

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

1
2
3
4
CREATE TABLE #TOTO
(
	nomToto VARCHAR(50) COLLATE maCollation
)

comment fait-on pour comparer les tables (les champs) avec les différentes collations?

Supposons que nous avons une table A avec une colonne nomA dont la collation est collationA, et une table B vec une colonne nomB dont la collation est collationB.

Nous souhaitons réaliser une comparaison :

Code :

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

1
2
3
SELECT A.nomA
FROM dbo.A AS A
JOIN dbo.B AS B ON A.nomA = B.nomB COLLATE collationA

Pour votre question 3, à part résoudre cela dans du code T-SQL, je ne vois pas comment faire

@++

[SQLpro]

Lisez l'article que j'ai écrit à ce sujet. http://sqlpro.developpez.com/cours/s...er/collations/

1)

comment fait-on pour créer une table temporaire avec la collation de la base et non du serveur?

Il suffit de préciser la collation au niveau du CREATE TABLE pour chaque colonne (COLLATE)

2)

comment fait-on pour comparer les tables (les champs) avec les différentes collations?

Il suffit de préciser la collation sur laquelle vous voulez que la comparaison opère (COLLATE)

3)

Lorsque je clique-droit dans les propriétés des utilisateurs (logins) et lorsque je clique sur Securable j'obtiens le message d'erreur cf. image. D'où viens le problème?

Les requêtes sous-jacentes à l'IHM n'ayant pas prévu ce cas de figure, il faut que vous passiez par des requêtes SQL et non par l'IHM. L'IHM n'a pas de collation...

A +

[cmako]

Merci pour votre réponse.

Bonjour,
Il vous "suffit" d'ajouter le nom de la collation à utiliser avec la clause COLLATE :

Code :

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

1
2
3
4
CREATE TABLE #TOTO
(
	nomToto VARCHAR(50) COLLATE maCollation
)

J'ai lu qu'il faut ajouter COLLATE pour chaque colonne caractère. N'y a-t-il pas moyen de définir une collation pour toute la table?

Supposons que nous avons une table A avec une colonne nomA dont la collation est collationA, et une table B vec une colonne nomB dont la collation est collationB.

Nous souhaitons réaliser une comparaison :

Code :

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

1
2
3
SELECT A.nomA
FROM dbo.A AS A
JOIN dbo.B AS B ON A.nomA = B.nomB COLLATE collationA

Et est ce qu'on peut également faire ça?

Code :

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

JOIN dbo.B AS B ON A.nomA = B.nomB COLLATE collationC

Je pense à ça par exemple si on veut comparer certaines chaines avec une collation binaire (égalité stricte). Cela n'améliorerait-il pas les perfs?

Pour votre question 3, à part résoudre cela dans du code T-SQL, je ne vois pas comment faire

@++

Je vais regarder dans le profiler le code T-SQL qui est généré. Mais je ne comprends pas du tout pourquoi cette erreur est survenue puisque les tables interrogées sont système et donc la collation doit être la même pour toutes ?!

Ensuite comment connaitre la collations de la table (des collonnes)? Le script de création qui est généré par le Management Studio ne précise pas les collations.

[SQLpro]

J'ai lu qu'il faut ajouter COLLATE pour chaque colonne caractère. N'y a-t-il pas moyen de définir une collation pour toute la table?

NON

Et est ce qu'on peut également faire ça?

OUI

Cela n'améliorerait-il pas les perfs?

Non, il aurait fallut que ce soit encodé au départ.

Mais je ne comprends pas du tout pourquoi cette erreur est survenue puisque les tables interrogées sont système et donc la collation doit être la même pour toutes ?!

Les compte de connexion sont dans master et les utilisateurs dans chaque base de prod, d'ou le mismatch de collation ! Que l'IHM ne sait pas résoudre.
Cela n'est pas grave, car au niveau système SQL, ce sont des ID par lesquelles ces informations sont recoupées.

A +

[cmako]

Merci pour votre réponse.

Je vais regarder dans le profiler le code T-SQL qui est généré. Mais je ne comprends pas du tout pourquoi cette erreur est survenue puisque les tables interrogées sont système et donc la collation doit être la même pour toutes ?!

Ensuite comment connaitre la collations de la table (des colonnes)? Le script de création qui est généré par le Management Studio ne précise pas les collations.

Voilà la requête que ça genère:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
SELECT
	sp.name AS [ObjectName],
	NULL AS [ObjectSchema],
	CASE WHEN (prmssn.class=4 or prmssn.class=100 )THEN 
		CASE (
				SELECT oc.type 
				FROM sys.server_principals AS oc 
				WHERE oc.principal_id = prmssn.major_id) 
			WHEN 'R' THEN 201 
			WHEN 'A' THEN 202 
		ELSE 200 END 
		+ CASE prmssn.class WHEN 4 THEN 0 ELSE 100 END 
	ELSE prmssn.class END AS [ObjectClass],
	NULL AS [ObjectType]
FROM sys.server_permissions AS prmssn
INNER JOIN sys.server_principals AS sp ON sp.principal_id = prmssn.major_id and prmssn.class = 101
INNER JOIN sys.server_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE (grantee_principal.name=N'DW_manager')
 
UNION
SELECT
	c.name AS [ObjectName],
	NULL AS [ObjectSchema],
	CASE WHEN (prmssn.class=4 or prmssn.class=100 )THEN 
		CASE (
				SELECT oc.type 
				FROM sys.server_principals AS oc 
				WHERE oc.principal_id = prmssn.major_id) 
			WHEN 'R' THEN 201 
			WHEN 'A' THEN 202 
		ELSE 200 END 
		+ CASE prmssn.class WHEN 4 THEN 0 ELSE 100 END 
	ELSE prmssn.class END AS [ObjectClass],
	NULL AS [ObjectType]
FROM sys.server_permissions AS prmssn
INNER JOIN sys.certificates AS c ON c.certificate_id = prmssn.major_id and prmssn.class = 106
INNER JOIN sys.server_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE (grantee_principal.name=N'DW_manager')
 
UNION
SELECT
	e.name AS [ObjectName],
	NULL AS [ObjectSchema],
	CASE WHEN (prmssn.class=4 or prmssn.class=100 )THEN 
		CASE (
				SELECT oc.type 
				FROM sys.server_principals AS oc 
				WHERE oc.principal_id = prmssn.major_id) 
			WHEN 'R' THEN 201 
			WHEN 'A' THEN 202 
		ELSE 200 END 
		+ CASE prmssn.class WHEN 4 THEN 0 
	ELSE 100 END ELSE prmssn.class END AS [ObjectClass],
	NULL AS [ObjectType]
FROM sys.server_permissions AS prmssn
INNER JOIN sys.endpoints AS e ON e.endpoint_id = prmssn.major_id and prmssn.class = 105
INNER JOIN sys.server_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE (grantee_principal.name=N'DW_manager')

Comme on peut voir les on fait des selects uniquement sur les tables sys.

Ce qui pose problème c'est la partie 2 (après le 1er union) elle n'a pas la même collation que les parties 1 et 3.
En clair ,
Select partie 1 Union Select partie 2 => problème
Select partie 2 Union Select partie 3 => problème
Select partie 1 Union Select partie 3 => pas de problème

[sacha1979]

Voici un exemple concret pour une recherche qui n'est ni sensible à la casse, ni sensible aux accents :

http://www.xoowiki.com/Article/SQL/c...actere-76.aspx

[cmako]

Encore quelques questions:

- Quelle est la meilleure collation pour pour faire une recherche binaire (exacte)?

- J'aimerais avoir plus d'explications sur les différentes collations, par exemple :
Quelle est la différence (à part le nom) entre :
French_BIN
French_BIN2
Latin1_General_BIN
Latin1_General_BIN2
SQL_Latin1_General_CP1_CS_AS
Latin1_General_CS_AS_KS_WS et
SQL_Latin1_General_CP1_CS_AS

- Existerait-il un tableau qui distingue toutes ces collations?

[SQLpro]

BIN = binaire sans alignement des codes hexadécimaux.
BIN2 = BIN avec alignement. Cela va plus vite en comparaison hétérogènes.
http://msdn.microsoft.com/en-us/library/ms143350.aspx

Latin1_general est en fait une page de code ancienne assez large faites pour les bases genre US

KS (Kana Sensitive) différencie les deux types de caractères Kana japonais : Hiragana et Katakana

WS (Wide Sensitive) permet de différencier la largeur d'encodage (ASCII => 1 octets par caractère, UNICODE : 2 octets par caractères)

A +