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 biais du choix de classement pour les données. Pour une explication plus approfondie des classements eux-mêmes, consultez Prise en charge d’Unicode et du classement.

Les classements s’appliquent non seulement aux données stockées dans les tables utilisateur, mais à tout le texte géré par SQL Server, y compris les métadonnées, les objets temporaires, les noms de variables, etc. La gestion de ces éléments diffère dans les bases de données autonomes et non autonomes. 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 autonomes et à des bases de données non autonomes.

Le comportement du classement des bases de données autonomes diffère légèrement de celui des bases de données non autonomes. 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 autonomes et non autonomes.

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 autonomes

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

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. Le classement correspond tempdb généralement au classement de l’instance, qui n’a pas besoin de correspondre au classement de 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 le jeu de résultats obtenu.

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 égale à l’opération.

Nous pouvons résoudre ce problème en classant la table temporaire de façon explicite. SQL Server facilite ce processus 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   
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, la @i clause de retour ne peut pas être liée à l’une @I ou l’autre @İ. Dans un classement Latin1_General sans respect de la casse, @i lie à @I, et la fonction retourne 1. Mais dans un classement turc qui ne respecte pas la casse, @i se lie à @İ, 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 autonomes

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. Des informations complémentaires sont fournies ci-dessous.

Dans une base de données autonome, 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 model classement de base de données, mais peut être modifié par l’utilisateur par le biais d’une ou ALTER DATABASE d’une CREATE commande comme avec des 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, puisque les métadonnées sont assemblées dans le classement de base de données. Dans une base de données autonome, 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 autonomes ou non autonomes est résumé dans ce tableau :

Dans l’exemple de table temporaire décrit précédemment, nous pouvons voir que ce comportement de classement élimine la nécessité d’une clause explicite COLLATE dans la plupart des utilisations de la table temporaire. 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   
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 autonome.

Cross between contained and un-contained contexts

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 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 autonome, 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 autonome, mais que la première commande est USE à une base de données non autonome, le comportement de classement autonome 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 autonome, 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 le jeu de résultats obtenu.

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, les #A liaisons dans #a le classement par défaut ne respectant pas la casse, et l’insertion fonctionne,

Voici le jeu de résultats obtenu.

(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 liaison #A dans le classement d’instance sensible à la casse ;

Voici le jeu de résultats obtenu.

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. Tout d’abord, nous commençons par tempdb (qui a le même classement respectant la casse que notre instance) et 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 le jeu de résultats obtenu.

(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 obtenu.

 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.  

Étapes suivantes

• Bases de données autonomes