Enthaltene Datenbanksortierungen

Verschiedene Eigenschaften wirken sich auf die Sortierreihenfolge und Gleichheitsemantik von Textdaten aus, einschließlich Groß-/Kleinschreibung, Akzentempfindlichkeit und der verwendeten Basissprache. Diese Eigenschaften werden SQL Server durch die Auswahl der Kollation für die Daten übermittelt. Eine ausführlichere Erläuterung der Sortierungen selbst finden Sie unter Sortierung und Unicode-Unterstützung.

Sortierungen gelten nicht nur für Daten, die in Benutzertabellen gespeichert sind, sondern für alle von SQL Server verarbeiteten Texte, einschließlich Metadaten, temporärer Objekte, Variablennamen usw. Die Handhabung dieser unterscheidet sich in enthaltenen und nicht enthaltenen Datenbanken. Diese Änderung wirkt sich nicht auf viele Benutzer aus, trägt jedoch dazu bei, Instanzunabhängigkeit und Uniformität bereitzustellen. Dies kann jedoch auch zu Verwirrung führen, sowie zu Problemen für Sitzungen, die sowohl auf enthaltene als auch nicht enthaltene Datenbanken zugreifen.

In diesem Thema wird der Inhalt der Änderung erläutert, und es werden Bereiche untersucht, in denen die Änderung Probleme verursachen kann.

Nicht enthaltene Datenbanken

Alle Datenbanken verfügen über eine Standardsortierung (die beim Erstellen oder Ändern einer Datenbank festgelegt werden kann. Diese Sortierung wird für alle Metadaten in der Datenbank verwendet und dient als Standardeinstellung für alle Textspalten innerhalb der Datenbank. Benutzer können eine andere Sortierung für eine bestimmte Spalte mithilfe der COLLATE Klausel auswählen.

Beispiel 1

Wenn wir beispielsweise in Peking arbeiten, könnten wir eine chinesische Sortierung verwenden:

ALTER DATABASE MyDB COLLATE Chinese_Simplified_Pinyin_100_CI_AS;  

Wenn wir nun eine Spalte erstellen, wird diese chinesische Sortierung als Standard eingestellt, aber wir können eine andere auswählen, wenn wir möchten.

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  

Dies ist die Ergebnismenge.

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

Dies erscheint relativ einfach, aber es treten mehrere Probleme auf. Da die Sortierung für eine Spalte von der Datenbank abhängig ist, in der die Tabelle erstellt wird, treten Probleme bei der Verwendung temporärer Tabellen auf, die in tempdb gespeichert sind. Die Sortierung von tempdb entspricht in der Regel der Sortierung der Instanz, die jedoch nicht mit der Datenbanksortierung übereinstimmen muss.

Beispiel 2

Betrachten Sie z. B. die oben genannte (chinesische) Datenbank, wenn sie für eine Instanz mit einer Latin1_General Sortierung verwendet wird:

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

Auf den ersten Blick sehen diese beiden Tabellen wie dasselbe Schema aus, aber da sich die Sortierungen der Datenbanken unterscheiden, sind die Werte tatsächlich inkompatibel:

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

Dies ist die Ergebnismenge.

Msg 468, Level 16, State 9, Zeile 2

Der Sortierungskonflikt zwischen "Latin1_General_100_CI_AS_KS_WS_SC" und "Chinese_Simplified_Pinyin_100_CI_AS" kann bei der Gleichheitsoperation nicht aufgelöst werden.

Wir können dies beheben, indem wir die temporäre Tabelle explizit anordnen. SQL Server erleichtert dies etwas, indem das DATABASE_DEFAULT Schlüsselwort für die COLLATE Klausel bereitgestellt wird.

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 ;  

Dies wird jetzt ohne Fehler ausgeführt.

Wir können auch sortierabhängiges Verhalten bei Variablen beobachten. Berücksichtigen Sie die folgende Funktion:

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

Dies ist eine ziemlich eigenartige Funktion. Bei einer Sortierung mit Groß-/Kleinschreibung kann das @i in der Rückgabeklausel nicht an @I oder @?? gebunden werden. In einer nicht case-sensitiven Latin1_General Sortierung wird @i an @I gebunden, und die Funktion gibt 1 zurück. Bei einer nicht auf Groß-/Kleinschreibung achtenden türkischen Sortierung wird @i jedoch an "@??" gebunden, und die Funktion gibt 2 zurück. Dies kann verheerende Auswirkungen auf eine Datenbank haben, die zwischen Instanzen mit unterschiedlichen Sortierungen wechselt.

Eigenständige Datenbanken

Da ein Entwurfsziel der enthaltenen Datenbanken darin besteht, sie eigenständig zu machen, muss die Abhängigkeit von Instanz und tempdb Sortierung abgetrennt werden. Dazu führen enthaltene Datenbanken das Konzept der Katalogsortierung ein. Die Katalogsortierung wird für Systemmetadaten und vorübergehende Objekte verwendet. Details finden Sie unten.

In einer enthaltenen Datenbank wird die Katalogsortierung Latin1_General_100_CI_AS_WS_KS_SC verwendet. Diese Sortierung ist für alle enthaltenen Datenbanken in allen Instanzen von SQL Server identisch und kann nicht geändert werden.

Die Datenbanksortierung wird beibehalten, wird jedoch nur als Standardsortierung für Benutzerdaten verwendet. Standardmäßig ist die Datenbanksortierung gleich der Modelldatenbanksortierung, kann jedoch vom Benutzer über einen CREATE oder ALTER DATABASE Befehl wie bei nicht enthaltenen Datenbanken geändert werden.

Ein neues Schlüsselwort, CATALOG_DEFAULT, ist in der COLLATE-Klausel verfügbar. Dies wird als Abkürzung zur aktuellen Sortierreihenfolge von Metadaten in enthaltenen und nicht-enthaltenen Datenbanken verwendet. Das heißt, in einer nicht enthaltenen Datenbank CATALOG_DEFAULT gibt die aktuelle Datenbanksortierung zurück, da Metadaten in der Datenbanksortierung sortiert werden. In einer enthaltenen Datenbank können diese beiden Werte unterschiedlich sein, da der Benutzer die Datenbanksortierung ändern kann, sodass sie nicht mit der Katalogsortierung übereinstimmt.

Das Verhalten verschiedener Objekte in nicht enthaltenen und enthaltenen Datenbanken wird in dieser Tabelle zusammengefasst:

Wenn wir das zuvor beschriebene Beispiel für eine temporäre Tabelle betrachten, können wir sehen, dass dieses Sortierungsverhalten die Notwendigkeit einer expliziten COLLATE Klausel bei den meisten Verwendungen von temporären Tabellen eliminiert. In einer enthaltenen Datenbank wird dieser Code jetzt ohne Fehler ausgeführt, auch wenn sich die Datenbank- und Instanzsortierungen unterscheiden:

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 ;  

Dies funktioniert, da beide T1_txt und T2_txt in der Datenbanksortierung der enthaltenen Datenbank sortiert werden.

Übergang zwischen eingeschlossenen und nicht eingeschlossenen Kontexten

Solange eine Sitzung in einer enthaltenen Datenbank enthalten bleibt, muss sie innerhalb der Datenbank verbleiben, mit der sie verbunden ist. In diesem Fall ist das Verhalten sehr einfach. Wenn eine Sitzung jedoch zwischen eingeschlossenen und nicht eingeschlossenen Kontexten wechselt, wird das Verhalten komplexer, da die beiden Regelsätze überbrückt werden müssen. Dies kann in einer teilweise geschlossenen Datenbank geschehen, da ein Benutzer zu einer anderen Datenbank wechseln kann USE. In diesem Fall wird der Unterschied bei Sortierungsregeln durch das folgende Prinzip behandelt.

• Das Sortierungsverhalten für einen Batch wird durch die Datenbank bestimmt, in der der Batch beginnt.

Beachten Sie, dass diese Entscheidung getroffen wird, bevor irgendwelche Befehle ausgegeben werden, einschließlich eines anfänglichen USE. Das heißt, wenn ein Batch in einer enthaltenen Datenbank beginnt, aber der erste Befehl an eine nicht enthaltene Datenbank geht, wird weiterhin das Sortierungsverhalten der enthaltenen Datenbank verwendet. In diesem Fall kann ein Verweis auf eine Variable mehrere mögliche Ergebnisse haben:

• Die Referenz kann genau eine Übereinstimmung ergeben. In diesem Fall funktioniert der Verweis ohne Fehler.

• Der Verweis findet möglicherweise keine Übereinstimmung in der aktuellen Sortierung, in der zuvor eine Übereinstimmung vorhanden war. Dadurch wird ein Fehler ausgelöst, der angibt, dass die Variable nicht vorhanden ist, obwohl sie anscheinend erstellt wurde.

• Der Verweis kann mehrere Übereinstimmungen finden, die ursprünglich unterschiedlich waren. Dadurch wird auch ein Fehler ausgelöst.

Wir veranschaulichen dies mit einigen Beispielen. Für diese gehen wir davon aus, dass eine teilweise eingebundene Datenbank namens MyCDB ihre Datenbanksortierung auf die Standardsortierung Latin1_General_100_CI_AS_WS_KS_SC festgelegt hat. Es wird davon ausgegangen, dass die Instanzsortierung lautet Latin1_General_100_CS_AS_WS_KS_SC. Die beiden Sortierungen unterscheiden sich nur bei der Groß-/Kleinschreibung.

Beispiel 1

Das folgende Beispiel veranschaulicht den Fall, in dem der Verweis genau eine Übereinstimmung findet.

USE MyCDB;  
GO  
  
CREATE TABLE #a(x int);  
INSERT INTO #a VALUES(1);  
GO  
  
USE master;  
GO  
  
SELECT * FROM #a;  
GO  
  
Results:  
  

Dies ist die Ergebnismenge.

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

In diesem Fall wird das identifizierte #a sowohl bei der nicht auf Groß-/Kleinschreibung achtenden Katalog-Sortierung als auch bei der auf Groß-/Kleinschreibung achtenden Instanz-Sortierung gebunden, und der Code funktioniert.

Beispiel 2

Im folgenden Beispiel wird der Fall veranschaulicht, in dem der Verweis keine Übereinstimmung in der aktuellen Kollationierung findet, obwohl zuvor eine vorhanden war.

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

Hier bindet die #A an #a in der Standardsortierung ohne Groß-/Kleinschreibung, und das Einfügen funktioniert,

Dies ist die Ergebnismenge.

(1 row(s) affected)  

Aber wenn wir das Skript fortsetzen...

USE master;  
GO  
  
SELECT * FROM #A;  
GO  

Beim Versuch, eine Bindung an #A in der groß- und kleinschreibungssensitiven, instanzspezifischen Sortierung zu erstellen, wird ein Fehler angezeigt.

Dies ist die Ergebnismenge.

Msg 208, Ebene 16, Bundesland 0, Zeile 2

Ungültiger Objektname '#A'.

Beispiel 3

Das folgende Beispiel veranschaulicht den Fall, in dem der Verweis mehrere ursprünglich unterschiedliche Übereinstimmungen findet. Zunächst beginnen wir in tempdb (bei der die gleiche Groß- und Kleinschreibungssortierung wie bei unserer Instanz vorliegt) und führen die folgenden Anweisungen aus.

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  

Dies ist erfolgreich, da sich die Tabellen in dieser Sortierung unterscheiden:

Dies ist die Ergebnismenge.

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

Wenn wir jedoch in unsere enthaltene Datenbank wechseln, stellen wir fest, dass wir keine Bindung mehr an diese Tabellen herstellen können.

USE MyCDB;  
GO  
SELECT * FROM #a;  
GO  

Dies ist die Ergebnismenge.

Msg 12800, Ebene 16, Zustand 1, Zeile 2

Der Verweis auf den Temporären Tabellennamen "#a" ist mehrdeutig und kann nicht aufgelöst werden. Mögliche Kandidaten sind "#a" und "#A".

Schlussfolgerung

Das Sortierungsverhalten der enthaltenen Datenbanken unterscheidet sich subtil davon in nicht enthaltenen Datenbanken. Dieses Verhalten ist im Allgemeinen von Vorteil und bietet Instanzunabhängigkeit und Einfachheit. Einige Benutzer haben möglicherweise Probleme, insbesondere wenn eine Sitzung sowohl auf enthaltene als auch nicht enthaltene Datenbanken zugreift.

Siehe auch

Eigenständige Datenbanken