Classements de base de données à relation contenant-contenu

Diverses propriétés affectent l'ordre de tri et la sémantique d'égalité des données textuelles, notamment le respect de la casse, le respect des accents et la langue de base utilisée. Ces qualités sont exprimées à SQL Server par le choix du classement des données. Pour une explication plus approfondie des classements eux-mêmes, consultez Prise en charge d'Unicode et du classement.

Les classements ne s'appliquent pas seulement aux données stockées dans les tables utilisateur, mais à tout le texte géré par SQL Server, notamment les métadonnées, les objets temporaires, les noms de variable, etc. Leur gestion est différente dans une base de données à relation contenant-contenu et dans une base de données sans relation contenant-contenu. Cette modification n'affecte pas de nombreux utilisateurs, mais permet l'indépendance et l'uniformité des instances. Cependant, elle peut provoquer également quelques confusions, ainsi que des problèmes pour les sessions qui accèdent à la fois à des bases de données à relation contenant-contenu et à des bases de données sans relation contenant-contenu.

Cette rubrique apporte des éclaircissements sur le contenu de la modification et indique où celle-ci peut entraîner des problèmes.

Bases de données sans relation contenant-contenu

Toutes les bases de données ont un classement par défaut (qui peut être défini lors de la création ou de la modification d'une base de données). Ce classement est utilisé pour toutes les métadonnées de la base de données, et comme valeur par défaut de toutes les colonnes de chaîne dans la base de données. Les utilisateurs peuvent choisir un classement différent pour une colonne particulière à l'aide de la clause COLLATE.

Exemple 1

Par exemple, supposons que nous travaillons à Beijing et que nous utilisons un classement chinois :

ALTER DATABASE MyDB COLLATE Chinese_Simplified_Pinyin_100_CI_AS;

Si nous créons une colonne, son classement par défaut sera ce classement chinois, mais nous pouvons en choisir un autre si nous le voulons :

CREATE TABLE MyTable
      (mycolumn1 nvarchar,
      mycolumn2 nvarchar COLLATE Frisian_100_CS_AS);
GO
SELECT name, collation_name
FROM sys.columns
WHERE name LIKE 'mycolumn%' ;
GO

Voici l'ensemble des résultats.

name            collation_name
--------------- ----------------------------------
mycolumn1       Chinese_Simplified_Pinyin_100_CI_AS
mycolumn2       Frisian_100_CS_AS

Cela semble relativement simple, mais plusieurs problèmes se posent. Étant donné que le classement d'une colonne dépend de la base de données dans laquelle la table est créée, des problèmes se posent pour l'utilisation des tables temporaires stockées dans tempdb. Le classement de tempdb correspond généralement au classement de l'instance, qui n'est pas obligatoirement celui de la base de données.

Exemple 2

Par exemple, examinez la base de données (chinoise) ci-dessus utilisée sur une instance avec un classement Latin1_General :

CREATE TABLE T1 (T1_txt nvarchar(max)) ;
GO
CREATE TABLE #T2 (T2_txt nvarchar(max)) ;
GO

À première vue, ces deux tables semblent avoir le même schéma, mais comme les classements des bases de données diffèrent, les valeurs sont en fait incompatibles :

SELECT T1_txt, T2_txt
FROM T1 
JOIN #T2 
    ON T1.T1_txt = #T2.T2_txt

Voici l'ensemble des résultats.

Msg 468, Niveau 16, État 9, Ligne 2

Impossible de résoudre le conflit de classement entre « Latin1_General_100_CI_AS_KS_WS_SC » et « Chinese_Simplified_Pinyin_100_CI_AS » dans l'opération égal à.

Nous pouvons résoudre ce problème en classant la table temporaire de façon explicite. SQL Server simplifie quelque peu cette opération en fournissant le mot clé DATABASE_DEFAULT pour la clause COLLATE.

CREATE TABLE T1 (T1_txt nvarchar(max)) ;
GO
CREATE TABLE #T2 (T2_txt nvarchar(max) COLLATE DATABASE_DEFAULT);
GO
SELECT T1_txt, T2_txt
FROM T1 
JOIN #T2 
    ON T1.T1_txt = #T2.T2_txt ;

Ce code s'exécute maintenant sans erreur.

Nous pouvons également consulter le comportement dépendant du classement avec des variables. Observez la fonction suivante :

CREATE FUNCTION f(@x INT) RETURNS INT
AS BEGIN 
      DECLARE @I INT = 1
      DECLARE @İ INT = 2
      RETURN @x * @i
END;

C'est une fonction assez particulière. Dans un classement respectant la casse, le @i dans la clause return ne peut pas être lié à @I ou @İ. Dans un classement Latin1_General sans respect de la casse, @i est lié à @I, et la fonction retourne 1. Mais dans un classement turc sans respect de la casse, @i est lié à @İ et la fonction retourne 2. Cela peut causer des dégâts dans une base de données qui se déplace entre des instances aux classements différents.

Bases de données à relation contenant-contenu

Comme un objectif de la conception de bases de données à relation contenant-contenu est de les rendre autonomes, la dépendance de l'instance et des classements de tempdb doit être supprimée. Pour cela, les bases de données à relation contenant-contenu présentent le concept de classement de catalogue. Le classement de catalogue est utilisé pour les métadonnées système et les objets transitoires. Des informations complémentaires sont fournies ci-dessous.

Dans une base de données à relation contenant-contenu, le classement de catalogue est Latin1_General_100_CI_AS_WS_KS_SC. Ce classement est le même pour toutes les bases de données à relation contenant-contenu sur toutes les instances de SQL Server et ne peut pas être changé.

Le classement de base de données est conservé, mais est utilisé uniquement comme classement par défaut des données utilisateur. Par défaut, le classement de base de données est égal au classement de base de données model, mais peut être modifié par l'utilisateur via une commande CREATE ou ALTER DATABASE comme avec les bases de données sans relation contenant-contenu.

Un nouveau mot clé, CATALOG_DEFAULT, est disponible dans la clause COLLATE. Il est utilisé comme un raccourci du classement actuel de métadonnées à la fois dans les bases de données avec et sans relation contenant-contenu. Autrement dit, dans une base de données sans relation contenant-contenu, CATALOG_DEFAULT retourne le classement de base de données actuel, puisque les métadonnées sont assemblées dans le classement de base de données. Dans une base de données à relation contenant-contenu, ces deux valeurs peuvent être différentes, puisque l'utilisateur peut modifier le classement de base de données afin qu'il ne corresponde pas au classement de catalogue.

Le comportement de différents objets dans les bases de données avec ou sans relation contenant-contenu est résumé dans ce tableau :

Si nous examinons l'exemple de table temp décrit précédemment, nous pouvons voir que ce comportement de classement rend la clause COLLATE explicite inutile dans la plupart des utilisations de table temp. Dans une base de données à relation contenant-contenu, ce code s'exécute désormais sans erreur, même si les classements d'instance et de base de données diffèrent :

CREATE TABLE T1 (T1_txt nvarchar(max)) ;
GO
CREATE TABLE #T2 (T2_txt nvarchar(max));
GO
SELECT T1_txt, T2_txt
FROM T1 
JOIN #T2 
    ON T1.T1_txt = #T2.T2_txt ;

Cela fonctionne parce que T1_txt et T2_txt sont assemblés dans le classement de base de données de la base de données à relation contenant-contenu.

Passer d'un contexte à relation contenant-contenu à un contexte sans relation contenant-contenu

Tant qu'une session dans une base de données à relation contenant-contenu reste contenue, elle doit rester dans la base de données à laquelle elle s'est connectée. Dans ce cas, le comportement est très simple. Mais si une session passe d'un contexte à relation contenant-contenu à un contexte sans relation contenant-contenu, le comportement devient plus complexe, puisque deux ensembles de règles doivent être liés. Cela peut arriver dans une base de données partiellement à relation contenant-contenu, puisqu'un utilisateur peut exécuter une opération USE sur une autre base de données. Dans ce cas, la différence des règles de classement est gérée selon le principe suivant.

• Le comportement du classement d'un lot est déterminé par la base de données dans laquelle commence le lot.

Notez que cette décision est prise avant l'émission d'une commande, notamment la commande USEinitiale. Autrement dit, si un lot commence dans une base de données à relation contenant-contenu, mais que la première commande est un USE sur une base de données sans relation contenant-contenu, le comportement du classement à relation contenant-contenu sera toujours utilisé pour le lot. En conséquence, une référence à une variable, par exemple, peut donner plusieurs résultats possibles :

• La référence peut trouver exactement une correspondance. Dans ce cas, la référence fonctionnera sans erreur.

• La référence peut pas trouver de correspondance dans le classement actuel alors qu'il en existait une auparavant. Cela génère une erreur indiquant que la variable n'existe pas, bien qu'elle ait été apparemment créée.

• La référence peut trouver plusieurs correspondances qui étaient distinctes à l'origine. Cela génère également une erreur.

Illustrons ceci par quelques exemples. Pour ces exemples, nous supposons une base de données partiellement à relation contenant-contenu, nommée MyCDB, dont le classement de base de données est défini sur le classement par défaut, Latin1_General_100_CI_AS_WS_KS_SC. Nous supposons que le classement d'instance est Latin1_General_100_CS_AS_WS_KS_SC. Les deux classements diffèrent uniquement en fonction du respect de la casse.

Exemple 1

L'exemple suivant illustre le cas où la référence trouve exactement une correspondance.

USE MyCDB;
GO

CREATE TABLE #a(x int);
INSERT INTO #a VALUES(1);
GO

USE master;
GO

SELECT * FROM #a;
GO

Results:

Voici l'ensemble des résultats.

x
-----------
1

Dans ce cas, le #a identifié est lié à la fois au classement de catalogue qui ne respecte pas la casse et au classement d'instance qui respecte la casse ; donc le code fonctionne.

Exemple 2

L'exemple suivant illustre le cas où la référence ne trouve pas de correspondance dans le classement actuel alors qu'il en existait une auparavant.

USE MyCDB;
GO

CREATE TABLE #a(x int);
INSERT INTO #A VALUES(1);
GO

Ici, le #A est lié à #a dans le classement par défaut qui ne respecte pas la casse, et l'insertion fonctionne,

Voici l'ensemble des résultats.

(1 row(s) affected)

mais si nous continuons le script...

USE master;
GO

SELECT * FROM #A;
GO

Nous obtenons une erreur lors de la tentative de lier #A dans le classement d'instance qui respecte la casse ;

Voici l'ensemble des résultats.

Msg 208, Niveau 16, État 0, Ligne 2

Nom d'objet '#A' non valide.

Exemple 3

L'exemple suivant illustre le cas où la référence trouve plusieurs correspondances qui étaient distinctes à l'origine. D'abord, nous démarrons dans tempdb (dont le classement qui respecte la casse est le même que celui de notre instance) et nous exécutons les instructions suivantes.

USE tempdb;
GO

CREATE TABLE #a(x int);
GO
CREATE TABLE #A(x int);
GO
INSERT INTO #a VALUES(1);
GO
INSERT INTO #A VALUES(2);
GO

Ce code réussit, puisque les tables sont distinctes dans ce classement :

Voici l'ensemble des résultats.

(1 row(s) affected)
(1 row(s) affected)

Si nous nous déplaçons dans notre base de données à relation contenant-contenu, toutefois, nous constatons que nous ne pouvons plus créer de liaison avec ces tables.

USE MyCDB;
GO
SELECT * FROM #a;
GO

Voici l'ensemble des résultats.

Msg 12800, Niveau 16, État 1, Ligne 2

La référence au nom de table temporaire '#a' est ambiguë et ne peut pas être résolue. Les candidats possibles sont '#a' et '#A.'

Conclusion

Le comportement du classement des bases de données à relation contenant-contenu diffère légèrement de celui des bases de données sans relation contenant-contenu. Ce comportement est généralement bénéfique, car il apporte une indépendance par rapport à l'instance et de la simplicité. Certains utilisateurs peuvent avoir des problèmes, en particulier lorsqu'une session accède à la fois à des bases de données avec et sans relation contenant-contenu.

Voir aussi

Concepts

Bases de données à relation contenant-contenu