SQL Server Standard-Zeichenkodierung

Question

Welche Zeichenkodierung ist standardmäßig für eine Datenbank in Microsoft SQL Server eingestellt?

Wie kann ich die aktuelle Zeichenkodierung in SQL Server sehen?

Answer 1

Kodierungen

In den meisten Fällen speichert SQL Server Unicode-Daten (d. h. Daten, die in der XML y N -vordefinierten Typen) in UCS-2 / UTF-16 (die Speicherung ist die gleiche, UTF-16 behandelt lediglich die Zusatzzeichen korrekt). Dies ist nicht konfigurierbar: Es gibt keine Option zur Verwendung von entweder UTF-8 oder UTF-32 (siehe UPDATE Abschnitt am Ende: UTF-8 ab SQL Server 2019) . Ob die eingebauten Funktionen Zusatzzeichen richtig verarbeiten können und ob diese richtig sortiert und verglichen werden, hängt von der verwendeten Kollation ab. Die älteren Collations - Namen, die mit SQL_ (z.B.. SQL_Latin1_General_CP1_CI_AS ) xoder keine Versionsnummer im Namen (z.B. Latin1_General_CI_AS ) - alle Zusatzzeichen einander gleichsetzen (da sie kein Sortiergewicht haben). Ab SQL Server 2005 wurde die Funktion 90 Serie Collations (die mit _90_ im Namen), die zumindest einen binären Vergleich für Zusatzzeichen durchführen kann, so dass man zwischen ihnen unterscheiden kann, auch wenn sie nicht in der gewünschten Reihenfolge sortiert sind. Das gilt auch für die 100 Serie Collations, eingeführt in SQL Server 2008. SQL Server 2012 führte Collations mit Namen ein, die auf _SC die nicht nur Ergänzungszeichen richtig sortieren, sondern es auch den eingebauten Funktionen ermöglichen, sie wie erwartet zu interpretieren (d. h. das Surrogatpaar als eine einzige Einheit zu behandeln). Ab SQL Server 2017 werden alle neuen Collations (die 140 Serien) implizite Unterstützung von ergänzenden Zeichen Daher gibt es keine neuen Zusammenstellungen mit Namen, die auf _SC .

Ab SQL Server 2019 wird UTF-8 eine unterstützte Kodierung für CHAR y VARCHAR Daten (Spalten, Variablen und Literale), aber nicht TEXT (siehe UPDATE Abschnitt am Ende: UTF-8 ab SQL Server 2019) .

Nicht-Unicode-Daten (d. h. Daten, die in der CHAR , VARCHAR y TEXT Typen - aber verwenden Sie nicht TEXT verwenden VARCHAR(MAX) stattdessen) verwendet eine 8-Bit-Kodierung (Extended ASCII, DBCS oder EBCDIC). Der spezifische Zeichensatz / die Kodierung basiert auf der Code Page, die wiederum auf der Kollation einer Spalte oder der Kollation der aktuellen Datenbank für Literale und Variablen oder der Kollation der Instanz für Variablen-/Cursornamen und GOTO Etiketten, oder was in einem COLLATE Klausel, wenn eine solche verwendet wird.

Um zu sehen, wie Gebietsschemata mit Kollationen übereinstimmen, sehen Sie sich das an:

• Windows Kollationierungsname
• SQL Server Kollationierungsname

Um die mit einer bestimmten Sortierung verbundene Codepage zu sehen (dies ist der Zeichensatz und betrifft nur CHAR / VARCHAR / TEXT Daten), führen Sie Folgendes aus:

SELECT COLLATIONPROPERTY( 'Latin1_General_100_CI_AS' , 'CodePage' ) AS [CodePage];

Um die LCID (d.h. das Gebietsschema) zu sehen, die mit einer bestimmten Kollation verbunden ist (dies wirkt sich auf die Sortier- und Vergleichsregeln aus), führen Sie Folgendes aus:

SELECT COLLATIONPROPERTY( 'Latin1_General_100_CI_AS' , 'LCID' ) AS [LCID];

Führen Sie den Befehl aus, um die Liste der verfügbaren Zusammenstellungen mit den zugehörigen LCIDs und Codepages anzuzeigen:

SELECT [name],
       COLLATIONPROPERTY( [name], 'LCID' ) AS [LCID],
       COLLATIONPROPERTY( [name], 'CodePage' ) AS [CodePage]
FROM sys.fn_helpcollations()
ORDER BY [name];

Standardeinstellungen

Bevor man sich mit den Server- und Datenbank-Standard-Collations befasst, sollte man die relative Bedeutung dieser Standardeinstellungen verstehen.

Die Standard-Kollation des Servers (eigentlich der Instanz) wird als Standard für neu erstellte Datenbanken (einschließlich der Systemdatenbanken) verwendet: master , model , msdb y tempdb ). Dies bedeutet jedoch nicht, dass irgendeine Datenbank (außer den 4 System-DBs) diese Sortierung verwendet. Die Standard-Collation der Datenbank kann jederzeit geändert werden (obwohl es Abhängigkeiten gibt, die verhindern können, dass die Collation einer Datenbank geändert wird). Die Standard-Kollation des Servers ist jedoch nicht so einfach zu ändern. Einzelheiten zum Ändern aller Sortierreihenfolgen finden Sie unter: Ändern der Sortierung der Instanz, der Datenbanken und aller Spalten in allen Benutzerdatenbanken: Was kann schon schiefgehen?

Die Server/Instanz Kollation steuert:

• lokale Variable Namen
• CURSOR Namen
• GOTO Etiketten
• Metadaten auf Instanzebene

Die Standard-Sortierung der Datenbank wird auf drei Arten verwendet:

• als Standard für neu erstellte String-Spalten. Dies bedeutet jedoch nicht, dass jede Zeichenkettenspalte diese Sortierreihenfolge verwendet. Die Sortierung einer Spalte kann jederzeit geändert werden. In diesem Fall ist es wichtig, den Datenbankstandard zu kennen, da er einen Hinweis darauf gibt, auf was die Zeichenkettenspalten höchstwahrscheinlich eingestellt sind.
• als Kollation für Operationen mit String-Literalen, Variablen und eingebauten Funktionen, die keine String-Eingaben akzeptieren, aber eine String-Ausgabe erzeugen (d.h. IF (@InputParam = 'something') ). Hier ist es wichtig, die Datenbankvorgaben zu kennen, da sie das Verhalten dieser Operationen bestimmen.
• Metadaten auf Datenbankebene

Die Spalte Collation wird entweder im Feld COLLATE Klausel zum Zeitpunkt der CREATE TABLE oder ein ALTER TABLE {table_name} ALTER COLUMN oder, falls nicht angegeben, aus der Datenbankvorgabe übernommen.

Da es hier mehrere Ebenen gibt, auf denen eine Sortierung angegeben werden kann (Datenbankstandard / Spalten / Literale & Variablen), wird die resultierende Sortierung bestimmt durch Sortierreihenfolge Vorrang .

Die folgende Abfrage zeigt die Standard- bzw. aktuellen Einstellungen für das Betriebssystem, die SQL Server-Instanz und die angegebene Datenbank an:

SELECT os_language_version,
       ---
       SERVERPROPERTY('LCID') AS 'Instance-LCID',
       SERVERPROPERTY('Collation') AS 'Instance-Collation',
       SERVERPROPERTY('ComparisonStyle') AS 'Instance-ComparisonStyle',
       SERVERPROPERTY('SqlSortOrder') AS 'Instance-SqlSortOrder',
       SERVERPROPERTY('SqlSortOrderName') AS 'Instance-SqlSortOrderName',
       SERVERPROPERTY('SqlCharSet') AS 'Instance-SqlCharSet',
       SERVERPROPERTY('SqlCharSetName') AS 'Instance-SqlCharSetName',
       ---
       DATABASEPROPERTYEX(N'{database_name}', 'LCID') AS 'Database-LCID',
       DATABASEPROPERTYEX(N'{database_name}', 'Collation') AS 'Database-Collation',
  DATABASEPROPERTYEX(N'{database_name}', 'ComparisonStyle') AS 'Database-ComparisonStyle',
       DATABASEPROPERTYEX(N'{database_name}', 'SQLSortOrder') AS 'Database-SQLSortOrder'
FROM   sys.dm_os_windows_info;

Installation Standard

Eine andere Interpretation von "Standard" könnte bedeuten, welche Standard-Kollation für die Instanz-Ebene Zusammenstellung bei der Installation. Das hängt von der Sprache des Betriebssystems ab, aber die (schreckliche, schreckliche) Standardeinstellung für Systeme mit "US-Englisch" ist SQL_Latin1_General_CP1_CI_AS . In diesem Fall ist die "Standard"-Kodierung Windows Code Page 1252 für VARCHAR Daten und, wie immer, UTF-16 für NVARCHAR Daten. Eine Liste der Betriebssystemsprachen, die mit der Standard-SQL-Server-Kollationierung übereinstimmen, finden Sie hier: Kollationierung und Unicode-Unterstützung: Sortierungen auf Serverebene . Denken Sie daran, dass diese Standardeinstellungen überschrieben werden können; diese Liste ist lediglich das, was die Instanz verwenden wird, wenn sie nicht während der Installation überschrieben wird.

---

UPDATE 2018-10-02

SQL Server 2019 führt die native Unterstützung für UTF-8 in VARCHAR / CHAR Datentypen (nicht TEXT !). Dies wird durch eine Reihe neuer Kollationen erreicht, deren Namen alle auf _UTF8 . Dies ist eine interessante Funktion, die sicherlich einigen Leuten helfen wird, aber es gibt einige "Macken" damit, besonders wenn UTF-8 nicht für alle Spalten verwendet wird et die Standardcollation der Datenbank, verwenden Sie sie also nicht, nur weil Sie gehört haben, dass UTF-8 auf magische Weise besser ist. UTF-8 wurde entwickelt ausschließlich für die ASCII-Kompatibilität: um es reinen ASCII-Systemen (d.h. früher UNIX) zu ermöglichen, Unicode zu unterstützen, ohne bestehenden Code oder Dateien zu ändern. Die Platzersparnis für Daten, die hauptsächlich (oder nur) US-englische Zeichen (und einige Satzzeichen) verwenden, ist ein Nebeneffekt. Wenn nicht überwiegend (oder nur) US-englische Zeichen verwendet werden, können die Daten die gleiche Größe wie UTF-16 haben oder sogar größer sein, je nachdem, welche Zeichen verwendet werden. Und in Fällen, in denen Platz gespart wird, kann sich die Leistung verbessern, aber auch verschlechtern.

Eine ausführliche Analyse dieser neuen Funktion finden Sie in meinem Beitrag, " Native UTF-8-Unterstützung in SQL Server 2019: Retter oder falscher Prophet? ".

Answer 2

Wenn Sie die Standardsortierreihenfolge für eine neu erstellte Datenbank wissen möchten, verwenden Sie diese:

SELECT SERVERPROPERTY('Collation')

Dies ist die Serversortierung für die von Ihnen verwendete SQL Server-Instanz.

Answer 3

Die Standard-Zeichencodierung für eine SQL Server-Datenbank ist iso_1, d. h. ISO 8859-1. Beachten Sie, dass die Zeichenkodierung vom Datentyp einer Spalte abhängt. Mit Hilfe dieses SQL können Sie sich ein Bild davon machen, welche Zeichenkodierungen für die Spalten in einer Datenbank verwendet werden, sowie von den Kollationen:

select data_type, character_set_catalog, character_set_schema, character_set_name, collation_catalog, collation_schema, collation_name, count(*) count
from information_schema.columns
group by data_type, character_set_catalog, character_set_schema, character_set_name, collation_catalog, collation_schema, collation_name;

Wenn der Standardwert verwendet wird, sollte der character_set_name iso_1 für die Datentypen char und varchar sein. Da nchar und nvarchar Unicode-Daten im UCS-2-Format speichern, ist der character_set_name für diese Datentypen UNICODE.

Answer 4

SELECT DATABASEPROPERTYEX('DBName', 'Collation') SQLCollation;

Dabei ist DBName der Name Ihrer Datenbank.

Answer 5

Ich denke, dies ist eine separate Antwort wert: obwohl intern Unicode-Daten als UTF-16 in Sql Server gespeichert wird, ist dies die Little Endian Geschmack, so dass, wenn Sie die Datenbank von einem externen System aufrufen, müssen Sie wahrscheinlich UTF-16LE angeben.