Und welchen Unterschied wählt Collation?

Dieser Artikel wurde für Studenten des Kurses "MS SQL Server Developer" erstellt.
Ich möchte eine Geschichte aus einem der vorherigen Projekte teilen, die zeigt, dass Collation sehr sorgfältig ausgewählt werden muss. Und was passiert, wenn dieser Parameter trotzdem falsch gewählt wird und welche Möglichkeiten zur Lösung des Problems bestehen.

Zunächst eine kleine Einführung zu Collation. In SQL Server teilt der Parameter Collation dem Server mit, wie Zeilen sortiert und verglichen werden sollen. Zum Beispiel die Zeilen "Apple" und "Apple". Sind sie anders oder nicht? Dies hängt von der angegebenen Sortierung ab. Wenn das Register immer weniger klar wird, was tun mit dem Beispiel „Weihnachtsbaum“ und „Weihnachtsbaum“? Zählen Sie sie als gleich oder als verschieden? Dies ist auch alles in Collation.

Die Geschichte ereignete sich in einem Projekt, dessen Funktionalität DropBox oder Google Drive sehr ähnlich ist. Es bietet die Möglichkeit, ihre synchronisierten Ordner und Dateien auf verschiedenen Computern zu verwalten sowie anderen Benutzern den Zugriff auf diesen synchronisierten Ordner zu ermöglichen.

Die Geschichte begann also mit der Tatsache, dass auf Prod-Servern 75-90% der Fehler in den Protokollen vorhanden waren (siehe Abbildung unten), und es ist nicht klar, woher sie kamen und was ihr Grund war. Der Fehler war: "ReadWrtLst ist nicht vollständig". Als nächstes kamen die Benutzerdetails und ihre Ordner.

Der Code fand schnell einen Ort, der einen Fehler verursachte, aber wir konnten nicht verstehen, warum er auftrat und wie er reproduziert werden konnte. Es war nur klar, dass der Fehler irgendwie mit der Tatsache zusammenhängt, dass der Benutzer es irgendwie geschafft hat Erstellen Sie einen anderen Ordner mit demselben Namen in Ihrem Betriebssystem.

Wir haben Informationen zu Benutzern gesammelt, für die dieser Fehler ausgegeben wird. Und hier standen wir vor der ersten Überraschung: Von Millionen Benutzern des Systems hatten nur 50 diesen Fehler. Und diese 50 Benutzer generieren 90% der Fehlerprotokolle. Da die Situation nicht reproduziert werden konnte, haben wir uns entschlossen, einen der Benutzer zu kontaktieren und herauszufinden, warum einer der Ordner nicht mit ihm synchronisiert wurde. Der Ordner sah für uns genauso aus wie für die anderen. Der einzige Unterschied bestand darin, dass er in der Sprache des Benutzers mithilfe von Hieroglyphen aufgerufen wurde. Und der Benutzer war Japaner. Unter diesen 50 Nutzern waren übrigens die Japaner die Mehrheit.

Dank eines Entwicklers des Teams konnten wir den Fehler reproduzieren. Der Fehler bestand darin, dass das Betriebssystem die Ordnernamen als unterschiedlich betrachtete und SQL Server sie aufgrund der ausgewählten Sortierung als gleich betrachtete.

Im Projekt verwendete Sortierung:
SQL_Latin1_General_CP1_CI_AS

Ein kleiner Exkurs zum Lesen von Collation. (Wenn Sie damit vertraut sind, können Sie es gerne überspringen.)

Die Sortierung besteht also aus mehreren Teilen:

• SQL - Sortieroptionen nach SQL Server (SQL zu Beginn der Sortierung) oder Windows (dann wäre es nur Latin1_ ...);
• Latin1_General - Gebietsschema oder verwendete Sprache;
• CP1 - Codepage - Codepage;
• CI - Groß- und Kleinschreibung nicht berücksichtigen - Groß- und Kleinschreibung nicht berücksichtigen;
• AS - Accent Sensitive - unter Berücksichtigung von Axonen oder Diakritika, mit anderen Worten, wird 'a' nicht als gleich 'ấ' angesehen.

Diese Sortierung war einst die Standardkollatierung bei der Installation von SQL Server.

Welche Möglichkeiten gibt es?

• _KS - Unter Berücksichtigung der japanischen Zeichen von Hiragana und Katakana interpretiert SQL Server die Hieroglyphen von Hiragana und Katakana als gleich, wenn die Option nicht ausgewählt ist.
• _WS - Wenn der Parameter unter Berücksichtigung der Zeichenbreite nicht ausgewählt ist, werden "Text" und "T ext" als dieselben Zeilen betrachtet.
• _VSS - unter Berücksichtigung der Anzeichen für die Wahl der Rechtschreiboption auf Japanisch, erschien ab Version 2017.
• _UTF8 - Ermöglicht das Speichern von Daten in UTF8.

Alle verwendeten Textfelder in der Datenbank geben NVARCHAR ein.
Es stellt sich heraus, dass SQL Server Zeichenfolgen nicht auf die gleiche Weise wie das Betriebssystem verglich, da das aktuelle Sortieren den Unterschied in der Schreibweise japanischer Zeichen und den Unterschied in der Zeichenbreite ignorierte, was das Problem verursachte, d. H. Der Benutzer konnte Ordner erstellen und diese nicht zur Synchronisierung zum System hinzufügen. Das gleiche passiert später beim Vergleichen von Dateinamen.

Wir begannen darüber nachzudenken, wie wir dieses Problem lösen und die Sortierung ändern können.

Die Sortierung kann auf mehreren Ebenen eingestellt werden:

• SQL Server-Instanz
• Datenbank
• Tabelle
• Das Feld

Gleichzeitig wird nicht empfohlen, unterschiedliche Sortierungen in der Datenbank zu haben, da Sie jedes Mal, wenn Sie Zeilen mit unterschiedlichen Sortierungen vergleichen, die Konvertierung mit COLLATE durchführen müssen, um dem Server anzugeben, welche Vergleichsreihenfolge verwendet werden soll.

Welche Optionen gibt es in einer Situation, in der klar ist, dass die Sortierung nicht richtig ausgewählt ist?

1. Sortierung auf DB-Ebene ändern;
2. Sortierung auf Feldebene ändern (in unserem Fall war es sinnlos, die gesamte Tabelle zu ändern);
3. Fügen Sie das Varbinary-Feld hinzu, in das ein Duplikat aus dem Feld mit dem Namen des Ordners geschrieben werden soll, und verwenden Sie es zum Vergleich.
4. Teilen Sie den Benutzern mit, dass die Unterstützung von Zeichen in Verzeichnisnamen eingeschränkt ist.

Die erste Option - Ändern der Sortierung auf Datenbankebene - ist die schwierigste. Im Fall der Datenbank müsste die Datenbank neu erstellt und die Daten dort neu geladen werden. Da das System rund um die Uhr funktionierte, wurde diese Option sofort abgelehnt.

Die zweite Option zum Ändern des Felds: Die einfachste Option zum Implementieren besteht darin, ein Feld mit der gewünschten Sortierung hinzuzufügen und die Daten dorthin zu übertragen. Dann muss jedoch der Code in der Datenbank geändert werden, der mit diesem Feld funktioniert, und die Datenbank enthält viel Code.

Die dritte Option hat uns am besten gefallen, da sie theoretisch die geringsten Änderungen vorgenommen hat, da das Hauptfeld mit der aktuellen Sortierung weiterhin bestehen würde und wir keine Probleme mit ihrer Konvertierung hätten, während alle erforderlichen Funktionen in Form der Berücksichtigung des japanischen Alphabets oder der Breite vorhanden sind Zeichen würden funktionieren. Der Nachteil war, dass Änderungen am Software-Teil vorgenommen werden mussten, aber da dieser Server-Teil möglich war, konnte dies durchgeführt werden.

Die vierte Option war in diesem Fall die einfachste, da die Gesamtzahl der Benutzer mehrere Millionen betrug und nur 50 ein Problem hatten. Wenn die Anwendung jedoch in Japan aktiv verwendet würde, wäre diese Lösung von geringem Nutzen.

Nachdem die Daten der Verwaltung vorgelegt wurden, wurde beschlossen, die Benutzer darüber zu informieren, dass die Software keine Anzahl von Zeichen unterstützt. Wenn sie im Namen synchronisierter Dateien und Ordner verwendet wird, funktioniert die Software möglicherweise nicht ordnungsgemäß. Dies ist eine vorübergehende Lösung, da mit der weiteren Verteilung die Anzahl der Benutzer, die mit einem ähnlichen Problem konfrontiert sind, zunimmt und es erforderlich sein wird, mithilfe der ersten drei Optionen etwas zu ändern.

Die beste Option für die Auswahl der Sortierung basiert auf Ihren Anwendungsanforderungen. Wenn SQL Server Zeichenfolgen auf dieselbe Weise wie das Betriebssystem vergleichen soll, ist die Sortierung standardmäßig definitiv falsch. Leider sind solche Nuancen zu Beginn eines Projekts beim Entwerfen eines Systems selten sichtbar, aber hoffentlich erinnern Sie sich nach dem Lesen des Artikels an die beschriebene Situation und treten nicht selbst auf einen solchen Rechen.

Nützliche Sortierressourcen:
https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017
https://www.red-gate.com/simple-talk/sql/sql-development/questions-sql-server-collations-shy-ask/
https://www.virtual-dba.com/sql-server-collation/