Classements contenus de base de données

Diverses propriétés affectent l'ordre de tri et la sémantique d'égalité des données textuelles, notamment la sensibilité à la casse, la sensibilité aux 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 discussion plus approfondie sur les classements eux-mêmes, consultez Prise en charge des classements et Unicode.

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’affectera 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, ainsi que des problèmes pour les sessions qui accèdent à des bases de données contenues et non contenues.

Cette rubrique précise le contenu de la modification et examine les domaines où le changement peut entraîner des problèmes.

Bases de données non contenues

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, ainsi que par défaut pour toutes les colonnes de texte dans la base de données. Les utilisateurs peuvent choisir un classement différent pour une colonne particulière à l’aide de la COLLATE clause.

Exemple 1

Par exemple, si nous travaillions à Pékin, nous pourrions utiliser un ordre de tri chinois.

ALTER DATABASE MyDB COLLATE Chinese_Simplified_Pinyin_100_CI_AS;  

Maintenant, 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 surviennent. É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 à celui de l'instance, qui n'a pas besoin de correspondre au classement de la base de données.

Exemple 2

Par exemple, considérez la base de données (chinoise) ci-dessus lorsqu’elle est 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 ressemblent au même schéma, mais étant donné que les classements des bases de données diffèrent, les valeurs sont réellement 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, Level 16, State 9, Line 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 cela en organisant explicitement la table temporaire. 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 ;  

Cela s’exécute maintenant sans erreur.

Nous pouvons également voir le comportement dépendant du classement avec des variables. Considérez 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 plutôt particulière. Dans un classement respectant la casse, dans la clause de retour, la @i ne peut pas être liée à @I ni à @??. Dans un classement Latin1_General insensible à la casse, @i est lié à @I, et la fonction retourne 1. Mais dans un classement turc insensible à la casse, @i se lie à @??, et la fonction retourne 2. Cela peut provoquer des ravages sur une base de données qui se déplace entre les instances avec différents classements.

Bases de données autonomes

Étant donné qu’un objectif de conception des bases de données contenues est de les rendre indépendantes, il faut rompre la dépendance vis-à-vis de l’instance et des classements tempdb. Pour ce faire, les bases de données contenues introduisent le concept du classement du catalogue. Le classement du catalogue est utilisé pour les métadonnées système et les objets temporaires. Les détails sont fournis ci-dessous.

Dans une base de données autonome, l'ordre de classement du catalogue 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 il est utilisé uniquement comme classement par défaut pour les données utilisateur. Par défaut, l’interclassement de la base de données est identique à celui de la base de données modèle, mais peut être modifié par l'utilisateur via une commande CREATE ou ALTER DATABASE, comme avec des bases de données non autonomes.

Un nouveau mot clé CATALOG_DEFAULT est disponible dans la COLLATEclause. Il s’agit d’un raccourci vers le classement actuel des métadonnées dans les bases de données contenues et non contenues. 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 la 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 divers éléments dans les bases de données non contenues et contenues est résumé dans ce tableau :

Si nous avons décrit un exemple de table temporaire 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 de base de données et d’instance 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 regroupés dans le classement des bases de données contenues.

Passage entre contextes contenus et non contenus

Tant qu’une session dans une base de données contenant reste contenue, elle doit rester dans la base de données à laquelle elle est connectée. Dans ce cas, le comportement est très simple. Mais si une session traverse des contextes confinés et non-confinés, le comportement devient plus complexe, car il faut faire le lien entre les deux ensembles de règles. Cela peut se produire dans une base de données partiellement autonome, car un utilisateur peut USE se rendre à une autre base de données. Dans ce cas, la différence dans les règles de classement est gérée par le principe suivant.

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

Notez que cette décision est prise avant qu'aucune commande ne soit émise, y compris une commande initiale USE. Autrement dit, si un lot commence dans une base de données autonome, mais que la première commande est un USE vers une base de données non autonome, le comportement de collation autonome sera toujours utilisé pour le lot. Étant donné cela, une référence à une variable, par exemple, peut avoir plusieurs résultats possibles :

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

• La référence peut ne pas trouver de correspondance dans le tri actuel alors qu'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 allons illustrer cela avec quelques exemples. Pour ceux-ci, nous supposons 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 par leur sensibilité aux majuscules et minuscules.

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é se lie à la fois dans l'interclassement insensible à la casse du catalogue et celui sensible à la casse de l'instance, et 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 correspond à #a dans l'ordre de tri par défaut insensible à 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 liaison à #A dans le paramètre de tri sensible à la casse de l’instance ;

Voici l'ensemble des résultats.

Msg 208, Level 16, State 0, Line 2

Nom d’objet non valide « #A ».

Exemple 3

L’exemple suivant illustre le cas où la référence recherche plusieurs correspondances qui étaient initialement distinctes. Premièrement, nous commençons par tempdb (qui a le même interclassement sensible à 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  

Cela réussit, car les tables sont distinctes dans ce classement :

Voici l'ensemble des résultats.

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

Toutefois, si nous passons à notre base de données autonome, nous constatons que nous ne pouvons plus établir de liaison à ces tables.

USE MyCDB;  
GO  
SELECT * FROM #a;  
GO  

Voici l'ensemble des résultats.

Msg 12800, Level 16, State 1, Line 2

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

Conclusion

Le comportement de classement des bases de données contenues diffère subtilement de celui des bases de données non contenues. Ce comportement est généralement bénéfique, offrant une indépendance d’instance et une simplicité. Certains utilisateurs peuvent rencontrer des problèmes, notamment lorsqu'une session accède à des bases de données contenues et non contenues.

Voir aussi

Bases de données autonomes