Classements de base de données autonome

S’applique à :SQL ServerAzure SQL Managed Instance

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 discussion plus approfondie sur les classements eux-mêmes, consultez Classement et prise en charge d'Unicode.

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 autonome et dans une base de données non autonome. Cette modification n’affecte pas beaucoup d’utilisateurs, mais permet de fournir l’indépendance et l’uniformité de l’instance. Toutefois, cela peut également entraîner une certaine confusion et des problèmes pour les sessions qui accèdent à des bases de données contenues et non contenues.

Le comportement de classement des bases de données contenues diffère subtilement du comportement dans les bases de données non contenues. 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 à des bases de données contenues et non contenues.

Cet article précise le contenu du changement et examine les domaines où le changement peut entraîner des problèmes.

Bases de données non autonome

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 la valeur par défaut pour toutes les colonnes de chaîne de 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;

Maintenant, si nous créons une colonne, son classement par défaut est 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 le jeu de 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 surviennent avec l’utilisation de tables temporaires stockées dans tempdb. L’interclassement de tempdb correspond généralement à l’interclassement de l’instance, qui n’a pas besoin de correspondre à l’interclassement de la base de données.

Exemple 2

Par exemple, considérez la base de données (chinoise) affichée précédemment, lorsqu’elle est utilisée sur une instance avec un Latin1_General classement :

CREATE TABLE T1 (T1_txt NVARCHAR (MAX));
GO

CREATE TABLE #T2 (T2_txt NVARCHAR (MAX));
GO

À première vue, ces deux tables ressemblent au même schéma, mais étant donné que les classements des bases de données diffèrent, les valeurs sont incompatibles :

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

Voici le jeu de 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 d'égalité.

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

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
     INNER JOIN #T2
         ON T1.T1_txt = #T2.T2_txt;

Cette requête 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 AS INT = 1;
    DECLARE @İ AS INT = 2;
    RETURN @x * @i;
END

C'est une fonction assez particulière. Dans un classement respectant la casse, @i dans la clause de retour ne peut pas être lié ni à @I ni à @İ. 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 confinées

Comme un objectif de la conception de bases de données autonomes 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 autonomes 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. Les détails sont fournis comme suit.

Dans une base de données contenue, le classement du catalogue est Latin1_General_100_CI_AS_WS_KS_SC. Ce classement est le même pour toutes les bases de données contenues sur toutes les instances de SQL Server et ne peut pas être modifié.

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 non autonomes.

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 autonomes et non autonomes. Autrement dit, dans une base de données non autonome, CATALOG_DEFAULT retourne le classement de base de données actuel, car les métadonnées sont compilées dans le classement de base de données. Dans une base de données autonome, ces deux valeurs peuvent être différentes, car l’utilisateur peut modifier le classement de la base de données afin qu’il ne corresponde pas au classement du catalogue.

Le comportement de différents objets dans les bases de données autonomes ou non autonomes est résumé dans ce tableau :

Dans 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 la table temp. Dans une base de données autonome, 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
     INNER JOIN #T2
         ON T1.T1_txt = #T2.T2_txt;

Cette requête fonctionne, car les deux T1_txt et T2_txt sont triées dans la collation de la base de données contenue.

Croisement entre contextes contenus et non contenus

Tant qu'une session dans une base de données autonome reste contenue, elle doit rester dans la base de données à laquelle elle s'est connectée. Dans ce cas, le comportement est 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 se produire dans une base de données partiellement contenue, car un utilisateur peut USE à 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.

Cette décision est prise avant que toute commande soit émise, y compris une initiale USE. Autrement dit, si un lot commence dans une base de données contenue, mais que la première commande est USE à une base de données non contenue, le comportement de collation contenue est toujours utilisé pour le lot. Étant donné ce scénario, une référence à une variable, par exemple, peut avoir plusieurs résultats possibles :

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

• La référence peut ne pas trouver de correspondance dans l'ordre de tri actuel où il y en avait une auparavant. Cela génère une erreur indiquant que la variable n’existe pas, même si elle a été apparemment créée.

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

Nous l’avons illustré avec quelques exemples. Pour cela, nous partons du principe qu’il existe une base de données partiellement autonome nommée MyCDB avec son classement de base de données défini sur le classement par défaut. Latin1_General_100_CI_AS_WS_KS_SC Nous partons du principe que le classement de l’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 le jeu de 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 où il y en a eu un avant.

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 le jeu de 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 le jeu de résultats.

Msg 208, Level 16, State 0, Line 2
Invalid object name '#A'.

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

Cette requête réussit, car les tables sont distinctes dans ce classement :

Voici le jeu de résultats.

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

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

USE MyCDB;
GO

SELECT * FROM #a;
GO

Voici le jeu de résultats.

Msg 12800, Level 16, State 1, Line 2
The reference to temp table name #a is ambiguous and cannot be resolved. Possible candidates are #a and #A.

Contenu connexe

• Bases de données autonomes