Sortierungsunterstützung¶

Erläuterungen zur Sortierung¶

Textzeichenfolgen in Snowflake werden mit dem Zeichensatz UTF-8 gespeichert. Standardmäßig werden Zeichenfolgen anhand der Unicode-Codes verglichen, die die Zeichen in der Zeichenfolge darstellen.

Das Vergleichen von Zeichenfolgen auf Grundlage ihrer UTF-8-Zeichenrepräsentationen führt jedoch möglicherweise nicht zum gewünschten oder erwarteten Verhalten. Beispiel:

• Wenn Sonderzeichen in einer bestimmten Sprache nicht nach den Sortierungsstandards dieser Sprache sortiert werden, kann das Sortieren zu unerwarteten Ergebnissen führen.

• Möglicherweise wollen Sie, dass die Zeichenfolgen nach anderen Regeln sortiert werden, z. B. indem ignoriert wird, ob Zeichen in Groß- oder Kleinbuchstaben geschrieben sind.

Mithilfe von Sortierung können Sie explizit die Regeln angeben, die zum Vergleichen von Zeichenfolgen verwendet werden sollen, basierend auf:

• Unterschiedlichen Gebietsschemata (d. h. unterschiedliche Zeichensätze für verschiedene Sprachen).

• Unterscheidung nach Groß-/Kleinschreibung (d. h. ob Zeichenfolgenvergleiche verwendet werden sollen, bei denen Groß- und Kleinschreibung berücksichtigt wird oder nicht, ohne dass die Funktionen UPPER oder LOWER zum Konvertieren der Zeichenfolgen explizit aufgerufen werden).

• Unterscheidung nach Akzent (z. B. ob Z, Ź und Ż als gleicher Buchstabe oder als verschiedene Buchstaben betrachtet werden).

• Unterscheidung nach Interpunktion (d. h. ob Vergleiche nur Buchstaben oder alle Zeichen enthalten). Wenn ein Vergleich beispielsweise nicht nach Interpunktion unterscheidet, werden A-B-C und ABC als gleichwertig behandelt.

• Zusätzliche Optionen, z. B. Einstellungen für die Sortierung anhand des ersten Buchstabens in einer Zeichenfolge und das Abschneiden von führenden und/oder nachstehenden Leerzeichen.

Anwendungsgebiete für Sortierung¶

Die Sortierung kann für eine Vielzahl von Vorgängen verwendet werden, einschließlich (aber nicht beschränkt auf):

Sortierungssteuerung¶

Die Sortierungssteuerung ist granular. Sie können die zu verwendende Sortierung explizit angeben für:

• Ein Konto, mithilfe des Kontoebenenparameters DEFAULT_DDL_COLLATION

• Alle Spalten in allen einer Datenbank hinzugefügten Tabellen, mithilfe des Befehls ALTER DATABASE

• Alle Spalten in allen einem Schema hinzugefügten Tabellen, mithilfe des Befehls ALTER SCHEMA

• Alle Spalten, die einer Tabelle hinzugefügt wurden, mithilfe des Befehls ALTER TABLE

• Einzelne Spalten in einer Tabelle mit dem Befehl CREATE TABLE.

• Einen bestimmten Vergleich innerhalb einer SQL-Anweisung (z. B. WHERE col1 = col2). Wenn auf eine Anweisung mehrere Sortierungen angewendet werden, wählt Snowflake die zu verwendende Sortierung anhand der Priorität aus. Weitere Details zur Priorität finden Sie unter Sortierungsrangfolge bei Operationen mit mehreren Zeichenfolgen.

COLLATE-Klausel für Tabellenspaltendefinitionen¶

Das Hinzufügen der optionalen COLLATE-Klausel zur Definition einer Tabellenspalte gibt an, dass die angegebene Sortierung für Vergleiche und andere verwandte Vorgänge verwendet wird, die für die Daten in der Spalte ausgeführt werden:

CREATE TABLE <table_name> ( <col_name> <col_type> COLLATE '<collation_specification>'
                            [ , <col_name> <col_type> COLLATE '<collation_specification>' ... ]
                            [ , ... ]
                          )

Wenn für eine Spalte keine COLLATE-Klausel angegeben ist, verwendet Snowflake die Standardklausel, bei der Zeichenfolgen anhand ihrer UTF-8-Zeichenrepräsentationen verglichen werden.

Außerdem unterstützt Snowflake die Angabe einer leeren Zeichenfolge für die Sortierungsspezifikation (z. B. COLLATE ''), was einer Angabe ohne Sortierung für die Spalte entspricht.

Die Angabe von COLLATE '' hat jedoch für eine Spalte aus Gründen der Priorität nicht die gleiche Wirkung wie die explizite Angabe von COLLATE 'utf8'. Weitere Details dazu finden Sie unter Sortierungsrangfolge bei Operationen mit mehreren Zeichenfolgen.

Verwenden Sie DESCRIBE TABLE, um festzustellen, ob für die Spalten in einer Tabelle eine Sortierung angegeben wurde (oder nutzen Sie die Funktion COLLATION, um gegebenenfalls die Sortierung für eine bestimmte Spalte anzuzeigen).

Funktion COLLATE¶

Die Funktion COLLATE verwendet die angegebene Sortierung für den eingegebenen Zeichenfolgenausdruck:

COLLATE( <expression> , '<collation_specification>' )

Diese Funktion kann auch mit der infix-Notation aufgerufen werden:

<expression> COLLATE '<collation_specification>'

Diese Funktion ist besonders nützlich, um explizit eine bestimmte Sortierung für eine bestimmte Operation anzugeben (z. B. Sortieren). Sie kann jedoch auch verwendet werden, um:

• Eine Sortierung in der SELECT-Klausel einer Unterabfrage zu erlauben, sodass alle Operationen in der angegebenen Spalte in der äußeren Abfrage die Sortierung nutzen.

• Erstellen Sie unter Verwendung von CTAS eine Tabelle mit einer angegebenen Sortierung.

In diesem Beispiel wird die englische Sortierung ohne Berücksichtigung der Groß- und Kleinschreibung verwendet:

SELECT * FROM t1 WHERE COLLATE(col1 , 'en-ci') = 'Tango';

In diesem Beispiel werden die Ergebnisse nach der deutschen Sortierung (Deutsch) sortiert.

SELECT * FROM t1 ORDER BY COLLATE(col1 , 'de');

In diesem Beispiel wird eine Tabelle mit einer Spalte nach der französischen Sortierung erstellt:

CREATE TABLE t2 AS SELECT COLLATE(col1, 'fr') AS col1 FROM t1;

In diesem Beispiel wird die Infix-Notation verwendet, um eine Tabelle mit einer Spalte mit französischer Sortierung zu erstellen:

CREATE TABLE t2 AS SELECT col1 COLLATE 'fr' AS col1 FROM t1;

Funktion COLLATION¶

Die Funktion COLLATION gibt die Sortierungsspezifikation zurück, die von einem Ausdruck, einschließlich einer Tabellenspalte, verwendet wird:

COLLATION( <expression> )

Wenn für den Ausdruck keine Sortierung angegeben wurde, gibt die Funktion NULL zurück.

Wenn Sie diese Funktion auf einen Spaltennamen anwenden, verwenden Sie am besten DISTINCT, um zu vermeiden, dass Sie für jede Zeile in der Tabelle eine Zeile der Ausgabe erhalten. Beispiel:

SELECT DISTINCT COLLATION(column1) FROM table1;

Spezifikationsbeispiele¶

Einige Beispiele für Sortierungsspezifikationszeichenfolgen umfassen:

• 'de': Deutsches Gebietsschema.

• 'de-ci-pi': Deutsches Gebietsschema mit Vergleichen, bei denen Groß-/Kleinschreibung sowie Interpunktion nicht unterschieden werden.

• 'fr_CA-ai': Gebietsschema für kanadisches Französisch mit Vergleichen ohne Unterscheidung nach Akzent.

• 'en_US-trim': Gebietsschema für US-Englisch, wobei führende und nachstehende Leerzeichen vor dem Vergleich abgeschnitten werden.

Sie können auch eine leere Zeichenfolge für eine Sortierungsspezifikation festlegen (z. B. COLLATE '' oder COLLATE(col1, '')), die angibt, dass keine Sortierung verwendet werden soll.

Unterstützte Spezifizierer¶

Gebietsschema:

Gibt die anzuwendenden sprach- und länderspezifischen Regeln an.

Unterstützt gültige Gebietsschema-Zeichenfolgen, die aus einem Sprachcode (erforderlich) und einem Ländercode (optional) in der Form language_country bestehen. Zu Beispielen gehören:

• en – Englisch.

• en_US – Amerikanisches Englisch.

• fr – Französisch

• fr_CA – Kanadisches Französisch.

Darüber hinaus gibt das Pseudo-Gebietsschema utf8 die Unicode-Reihenfolge an, die der Standard ist. Weitere Details dazu finden Sie unter Unterschiede in der Sortierung bei Verwendung von UTF-8 oder lokaler Sortierung (unter diesem Thema).

Der Gebietsschemaspezifizierer ist optional, muss jedoch, falls verwendet, der erste Spezifizierer in der Zeichenfolge sein.

Eine vollständige Liste der von Snowflake unterstützten Gebietsschemata finden Sie unter Sortierung n Snowflake unterstützte Sortierungs-Gebietsschemas.

Unterscheidung nach Groß-/Kleinschreibung:

Legt fest, ob die Groß-/Kleinschreibung beim Vergleich von Werten berücksichtigt wird. Mögliche Werte:

• cs – Zwischen Groß- und Kleinschreibung wird unterschieden (Standard).

• ci – Zwischen Groß- und Kleinschreibung wird nicht unterschieden.

Beispiel:

Sortierungsspezifikation	Wert	Ergebnis
'en-ci'	Abc = abc	True
'en-cs' / en	Abc = abc	False

Unterscheidung nach Akzent:

Legt fest, ob Zeichen mit Akzenten als gleichwertig oder verschieden von ihren Basiszeichen betrachtet werden. Mögliche Werte:

• as – Es wird nach Akzent unterschieden (Standardeinstellung).

• ai – Es wird nicht nach Akzent unterschieden.

Beispiel:

Sortierungsspezifikation	Wert	Ergebnis	Anmerkungen
'fr-ai'	E = É	True
'fr-as' / 'fr'	E = É	False
'en-ai'	a = ą	True	Im Englischen werden diese Buchstaben so behandelt, als ob sie nur Akzentunterschiede aufweisen würden. Wenn Sie also keine Unterscheidung nach Akzent angeben, werden die Werte bei Vergleichen als gleich betrachtet.
'pl-ai'	a = ą	False	In der polnischen Sprache werden diese Buchstaben als separate Grundbuchstaben behandelt, sodass sie unabhängig von der Angabe der Unterscheidung nach Akzent immer als ungleich gelten.
'pl-as' / 'pl'	a = ą	False

Die Regeln für die Unterscheidung nach Akzent und die Sortierung sind von Sprache zu Sprache verschieden sind. In einigen Sprachen wird bei der Sortierung beispielsweise immer nach Akzenten unterschieden; in dem Fall können Sie die Unterscheidung nicht deaktivieren, selbst wenn Sie eine akzentunabhängige Sortierung auswählen.

Unterscheidung nach Interpunktion:

Legt fest, ob Nichtbuchstaben eine Rolle spielen. Mögliche Werte:

• ps – Interpunktionsempfindlich.

• pi – Interpunktionsunempfindlich.

Beachten Sie, dass die Standardeinstellung vom Gebietsschema abhängt (d. h., wenn die Unterscheidung nach Interpunktion nicht festgelegt ist, werden Regeln je nach dem Gebietsschema verwendet). In den meisten Fällen entsprechen die Regeln ps.

Beispiel:

Sortierungsspezifikation	Wert	Ergebnis	Anmerkungen
'en-pi'	A-B-C = ABC	True
'en-ps'	A-B-C = ABC	False

Präferenz für den ersten Buchstaben:

Legt fest, ob bei der Sortierung Groß- oder Kleinbuchstaben an erster Stelle stehen. Mögliche Werte:

• fl – Kleinbuchstaben zuerst sortiert.

• fu – Großbuchstaben zuerst sortiert.

Die Standardeinstellung hängt vom Gebietsschema ab (d. h., wenn kein Wert angegeben ist, wird eine Sortierung auf Basis des Gebietsschemas verwendet). In den meisten Fällen entspricht die Sortierung fl.

Außerdem hat dieser Spezifizierer keinen Einfluss auf Gleichheitsvergleiche.

Konvertierung der Groß-/Kleinschreibung:

Führt dazu, dass Zeichenfolgen vor Vergleichen in Klein- oder Großbuchstaben konvertiert werden. In einigen Fällen ist dies schneller als eine vollständige Sortierung anhand des Gebietsschemas. Mögliche Werte:

• upper – Konvertieren der Zeichenfolge vor Vergleichen in Großbuchstaben.

• lower – Konvertieren der Zeichenfolge vor Vergleichen in Kleinbuchstaben.

Dieser Spezifizierer hat keinen Standardwert (d. h. wenn kein Wert angegeben ist, findet keine der Konvertierungen statt).

Platzsparend:

Entfernt vor Vergleichen führende/nachstehende Leerzeichen aus Zeichenfolgen. Diese Funktion kann nützlich sein, um Vergleiche durchzuführen, die (außer in äußerst seltenen Fällen) der Semantik des Datentyps SQL CHAR entsprechen.

Mögliche Werte:

• trim – Entfernen sowohl führender als auch nachstehender Leerzeichen vor Vergleichen.

• ltrim – Entfernen nur führender Leerzeichen vor Vergleichen.

• rtrim – Entfernen nur nachstehender Leerzeichen vor Vergleichen.

Dieser Spezifizierer hat keinen Standardwert (d. h. wenn kein Wert angegeben ist, wird keine Kürzung vorgenommen).

Beispiel:

Sortierungsspezifikation	Wert	Ergebnis	Anmerkungen
'en-trim'	__ABC_ = ABC	True	In diesen Beispielen stehen Unterstriche für Leerzeichen.
'en-ltrim'	__ABC_ = ABC	False
'en-rtrim'	__ABC_ = ABC	False
'en'	__ABC_ = ABC	False

Vergleiche ohne Berücksichtigung der Groß-/Kleinschreibung¶

In den folgenden Abschnitten werden Vergleiche ohne Berücksichtigung der Groß-/Kleinschreibung beschrieben:

• Unterschiede beim Vergleichen von Zeichenfolgen mit Großbuchstaben und ursprünglichen Zeichenfolgen

• Zeichengewichtungen

CREATE OR REPLACE TABLE test_table (col1 VARCHAR, col2 VARCHAR);
INSERT INTO test_table VALUES ('ı', 'i');

SELECT col1 = col2,
       COLLATE(col1, 'lower') = COLLATE(col2, 'lower'),
       COLLATE(col1, 'upper') = COLLATE(col2, 'upper')
  FROM test_table;

+-------------+-------------------------------------------------+-------------------------------------------------+
| COL1 = COL2 | COLLATE(COL1, 'LOWER') = COLLATE(COL2, 'LOWER') | COLLATE(COL1, 'UPPER') = COLLATE(COL2, 'UPPER') |
|-------------+-------------------------------------------------+-------------------------------------------------|
| False       | False                                           | True                                            |
+-------------+-------------------------------------------------+-------------------------------------------------+

Unterschiede in der Sortierung bei Verwendung von UTF-8 oder lokaler Sortierung¶

Zeichenfolgen werden in Snowflake immer in UTF-8 gespeichert und können alle Zeichen in allen Sprachen darstellen, die von UTF-8 unterstützt werden. Daher ist die Standardsortierung UTF-8 (d. h. 'utf8').

UTF-8-Sortierung basiert auf der numerischen Darstellung des Zeichens und nicht auf der alphabetischen Rangfolge des Zeichens.

Dies ist analog zur Sortierung nach dem Ordnungswert einzelner ASCII-Zeichen. Dies ist wichtig, da Großbuchstaben Ordnungswerte haben, die kleiner sind als bei Kleinbuchstaben:

Infolgedessen:

• Wenn Sie in der UTF-8-Reihenfolge sortieren, werden alle Großbuchstaben vor allen Kleinbuchstaben zurückgegeben:

  A, B, …, Y, Z, …, a, b, …, y, z

• Im Gegensatz dazu wird die Sortierungsspezifikation 'en' alphabetisch sortiert (anstatt die interne UTF-8-Darstellung zu verwenden), sodass sowohl A als auch a vor B und b zurückgegeben werden:

  a, A, b, B, …

Darüber hinaus wirken sich die Unterschiede zwischen den cs- und ci-Spezifizierern zur Unterscheidung nach Groß-/Kleinschreibung auf die Sortierung aus:

• cs (Unterscheidung nach Groß-/Kleinschreibung) gibt immer die Kleinbuchstabenversion eines Buchstabens vor der Großbuchstabenversion desselben Buchstabens zurück. Beispiel mit 'en-cs':

  a, A, b, B, …

  Die Unterscheidung nach Groß-/Kleinschreibung ist die Standardeinstellung ist, daher sind 'en-cs' und 'en' gleichwertig.

• ci (ohne Unterscheidung der Groß-/Kleinschreibung) gibt Versionen der Groß-/Kleinschreibung von Buchstaben in zufälliger Relation zueinander zurück, jedoch noch vor der Groß-/Kleinschreibung von späteren Buchstaben. Beispiel mit 'en-ci':

  A, a, b, B, …

Auch einige nicht alphabetische Zeichen können je nach Sortiereinstellung unterschiedlich sortiert werden. Das folgende Beispiel zeigt, dass das Pluszeichen (+) und das Minuszeichen (-) bei verschiedenen Sortiereinstellungen unterschiedlich sortiert werden:

Erstellen Sie die Tabelle:

CREATE OR REPLACE TABLE demo (
    no_explicit_collation VARCHAR,
    en_ci VARCHAR COLLATE 'en-ci',
    en VARCHAR COLLATE 'en',
    utf_8 VARCHAR collate 'utf8');
INSERT INTO demo (no_explicit_collation) VALUES
    ('-'),
    ('+');
UPDATE demo SET
    en_ci = no_explicit_collation,
    en = no_explicit_collation,
    utf_8 = no_explicit_collation;

Führen Sie Abfragen auf den Daten aus:

SELECT MAX(no_explicit_collation), MAX(en_ci), MAX(en), MAX(utf_8)
  FROM demo;

+----------------------------+------------+---------+------------+
| MAX(NO_EXPLICIT_COLLATION) | MAX(EN_CI) | MAX(EN) | MAX(UTF_8) |
|----------------------------+------------+---------+------------|
| -                          | +          | +       | -          |
+----------------------------+------------+---------+------------+

Sortierungsrangfolge bei Operationen mit mehreren Zeichenfolgen¶

Wenn Sie eine Operation für zwei (oder mehr) Zeichenfolgen ausführen, können unterschiedliche Sortierungen für unterschiedliche Zeichenfolgen angegeben werden. Das Festlegen der anzuwendenden Sortierung hängt davon ab, wie die Sortierung für einzelne Eingaben und die Rangfolge der einzelnen Spezifizierer angegeben wurden.

Es gibt drei Prioritätsstufen (von der höchsten zur niedrigsten):

Funktion:

Die Sortierung wird mit der Funktion COLLATE in einer SQL-Anweisung angegeben.

Spalte:

Die Sortierung wurde in der Spaltendefinition angegeben.

Keine:

Für einen bestimmten Ausdruck/eine bestimmte Spalte ist/wurde keine Sortierung angegeben, oder es wird/wurde eine Sortierung mit einer leeren Spezifizierung verwendet (z. B. COLLATE(col1, '') oder col1 STRING COLLATE '').

Bei der Bestimmung der zu verwendenden Sortierung wird die Sortierungsspezifikation mit der höchsten Priorität verwendet. Wenn verschiedene Sortierungen mit der gleichen Prioritätsstufe angegeben sind, werden ihre Werte verglichen, und wenn sie nicht gleich sind, wird ein Fehler zurückgegeben.

Betrachten Sie beispielsweise eine Tabelle mit den folgenden Sortierungsspezifikationen auf Spaltenebene:

CREATE OR REPLACE TABLE collation_precedence_example(
  col1    VARCHAR,               -- equivalent to COLLATE ''
  col2_fr VARCHAR COLLATE 'fr',  -- French locale
  col3_de VARCHAR COLLATE 'de'   -- German locale
);

Wenn die Tabelle in einer Anweisung verwendet wird, die zwei Zeichenfolgen miteinander vergleicht, wird die Sortierung wie folgt angewendet:

• Bei diesem Vergleich wird die Sortierung 'fr' verwendet, da die Rangfolge für col2_fr höher ist als die für col1:

  ... WHERE col1 = col2_fr ...
  

  Copy

• Bei diesem Vergleich wird die Sortierung 'en' verwendet, da sie in der Anweisung explizit angegeben ist und deshalb Vorrang vor der Sortierung col2_fr hat:

  ... WHERE col1 COLLATE 'en' = col2_fr ...
  

  Copy

• Dieser Vergleich gibt einen Fehler zurück, da die Ausdrücke unterschiedliche Sortierungen auf der gleichen Rangstufe haben:

  ... WHERE col2_fr = col3_de ...
  

  Copy

• Dieser Vergleich verwendet die Sortierung 'de', da die Sortierung für col2_fr entfernt wurde:

  ... WHERE col2_fr COLLATE '' = col3_de ...
  

  Copy

• Dieser Vergleich gibt einen Fehler zurück, da die Ausdrücke unterschiedliche Sortierungen auf der gleichen Rangstufe haben:

  ... WHERE col2_fr COLLATE 'en' = col3_de COLLATE 'de' ...
  

  Copy

Auch wenn die Standardsortierung von Snowflake 'utf8' ist, unterscheidet sich die Angabe einer leeren Zeichenfolge (oder die Angabe keiner Sortierung) von der expliziten Angabe von 'utf8', da die explizite Sortierung eine höhere Priorität hat als keine Sortierung. Die letzten beiden Anweisungen in den folgenden Codebeispielen machen den Unterschied deutlich:

Betrachten Sie beispielsweise eine Tabelle mit den folgenden Sortierungsspezifikationen auf Spaltenebene:

CREATE OR REPLACE TABLE collation_precedence_example2(
  s1 STRING COLLATE '',
  s2 STRING COLLATE 'utf8',
  s3 STRING COLLATE 'fr'
);

Wenn die Tabelle in einer Anweisung verwendet wird, die zwei Zeichenfolgen miteinander vergleicht, wird die Sortierung wie folgt angewendet:

• Dieser Vergleich verwendet 'utf8', da s1 keine Sortierung hat und 'utf8' die Standardeinstellung ist:

  ... WHERE s1 = 'a' ...
  

  Copy

• Dieser Vergleich verwendet 'utf8', da s1 keine Sortierung hat und s2 die explizite Sortierung 'utf8' hat

  ... WHERE s1 = s2 ...
  

  Copy

• Dieser Vergleich wird ohne Fehler ausgeführt, da s1 keine Sortierung hat und s3 die explizite Sortierung fr, sodass die explizite Sortierung Vorrang hat:

  ... WHERE s1 = s3 ...
  

  Copy

• Dieser Vergleich führt zu einem Fehler, da s2 und s3 unterschiedliche Sortierungen auf der gleichen Rangstufe angegeben haben:

  ... WHERE s2 = s3 ...
  

  Copy

  002322 (42846): SQL compilation error: Incompatible collations: 'fr' and 'utf8'
  

Eingeschränkte Unterstützung bei Sortierung in integrierten Funktionen¶

Sortierung wird nur in einer Teilmenge von Zeichenfolgenfunktionen unterstützt. Funktionen, bei denen eine Implementierung der Sortierung vernünftigerweise erwartet werden kann, die jedoch keine Sortierung unterstützen, geben bei Verwendung der Sortierung einen Fehler zurück. Diese Fehlermeldungen werden nicht nur beim Aufrufen von COLLATE angezeigt, sondern auch beim Aufrufen einer Zeichenfolgenfunktion für eine Spalte, die in der Anweisung CREATETABLE oder ALTERTABLE, mit der diese Spalte erstellt wurde, als sortiert definiert wurde.

Gegenwärtig beeinflusst die Sortierung nur einfache Vergleichsoperationen. Beispiel: POSITION('abc' in COLLATE('ABC', 'en-ci')) findet abc nicht in ABC, obwohl Sortierung ohne Unterscheidung nach Groß-/Kleinschreibung festgelegt ist.

Leistungsauswirkungen bei Verwendung der Sortierung¶

Eine Nutzung von Sortierung kann sich auf die Leistung verschiedener Datenbankvorgänge auswirken:

• Vorgänge mit Vergleichen werden möglicherweise langsamer.

  Dies kann Auswirkungen auf einfache WHERE-Klauseln sowie Verknüpfungen, Sortierungen, GROUPBY-Operationen usw. haben.

• Bei Verwendung mit einigen Funktionen in WHERE-Prädikaten kann das Pruning von Mikropartitionen weniger effizient ausfallen.

• Eine Verwendung der Sortierung in einem WHERE-Prädikat, das sich von der für die Spalte angegebenen Sortierung unterscheidet, kann zu einer verringerten Pruning-Effizienz führen oder das Pruning vollständig verhindern.

Weitere Hinweise zur Verwendung der Sortierung¶

• Beachten Sie, dass die folgenden Sortierungsfunktionen trotz der Ähnlichkeit ihrer Namen unterschiedliche Ergebnisse liefern:

  • COLLATE gibt explizit an, welche Sortierung verwendet werden soll.

  • COLLATION zeigt an, welche Sortierung verwendet wird, wenn keine explizit angegeben ist.

• In einer Spalte mit einer Sortierungsspezifikation können Zeichen verwendet werden, die nicht zum Gebietsschema der Sortierung gehören. Dies kann sich auf die Sortierung auswirken.

  Wenn beispielsweise eine Spalte mit einer COLLATE 'en'-Klausel erstellt wird, können die Daten in der Spalte das nicht englische Zeichen É enthalten. In diesem Fall wird das Zeichen É in der Nähe von E sortiert.

• Sie können Sortiervorgänge angeben, die nicht unbedingt von Bedeutung sind.

  Sie können beispielsweise festlegen, dass polnische Daten mit französischen Daten anhand einer deutschen Sortierung verglichen werden:

  SELECT ... WHERE COLLATE(French_column, 'de') = Polish_column;
  

  Copy

  Snowflake empfiehlt jedoch, das Feature nicht auf diese Weise zu verwenden, da möglicherweise unerwartete oder unbeabsichtigte Ergebnisse zurückgegeben werden.

• Nachdem eine Tabellenspalte definiert wurde, können Sie die Sortierung für diese Spalte nicht mehr ändern. Mit anderen Worten: Nachdem eine Spalte mit einer bestimmten Sortierung unter Verwendung einer CREATE TABLE-Anweisung erstellt wurde, können Sie die Sortierung mit ALTER TABLE nicht mehr ändern.

  Sie können jedoch in einer DML-Anweisung eine andere Sortierung angeben, z. B. eine SELECT-Anweisung, die auf die Spalte verweist.

Unterschiede beim Vergleich von Breiten, Abständen und Schriften¶

Bei Zeichenfolgevergleichen erkennt die ci-Sortierungsspezifikation, dass verschiedene visuelle Darstellungen eines Zeichens sich auf dasselbe Zeichen beziehen können und behandelt sie entsprechend. Um leistungsfähigere Vergleiche zu ermöglichen, erkennen die upper/lower-Sortierungsspezifikationen diese unterschiedlichen visuellen Darstellungen eines Zeichens nicht als dasselbe Zeichen.

Insbesondere ignoriert die ci-Sortierungsspezifikation einige Unterschiede in den folgenden Kategorien, während die upper/lower-Sortierungsspezifikationen diese nicht ignorieren:

• Zeichenbreiten

• Typen von Leerzeichen

• Schriftzeichen

Die folgenden Abschnitte enthalten Beispiele, die diese Unterschiede verdeutlichen.

CREATE OR REPLACE TABLE different_widths(codepoint STRING, description STRING);

INSERT INTO different_widths VALUES
  ('a', 'ASCII a'),
  ('A', 'ASCII A'),
  ('ａ', 'Full-width a'),
  ('Ａ', 'Full-width A');

SELECT codepoint VISUAL_CHAR,
       'U+'  || TO_CHAR(UNICODE(codepoint), '0XXX') codepoint_representation,
       description
  FROM different_widths;

+-------------+--------------------------+--------------+
| VISUAL_CHAR | CODEPOINT_REPRESENTATION | DESCRIPTION  |
|-------------+--------------------------+--------------|
| a           | U+0061                   | ASCII a      |
| A           | U+0041                   | ASCII A      |
| ａ          | U+FF41                   | Full-width a |
| Ａ          | U+FF21                   | Full-width A |
+-------------+--------------------------+--------------+

SELECT COUNT(*) NumRows,
       COUNT(DISTINCT UNICODE(codepoint)) DistinctCodepoints,
       COUNT(DISTINCT codepoint COLLATE 'en-ci') DistinctCodepoints_EnCi,
       COUNT(DISTINCT codepoint COLLATE 'upper') DistinctCodepoints_Upper,
       COUNT(DISTINCT codepoint COLLATE 'lower') DistinctCodepoints_Lower
  FROM different_widths;

+---------+--------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | DISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+--------------------+-------------------------+--------------------------+--------------------------|
|       4 |                  4 |                       1 |                        2 |                        2 |
+---------+--------------------+-------------------------+--------------------------+--------------------------+

CREATE OR REPLACE TABLE different_whitespaces(codepoint STRING, description STRING);

INSERT INTO different_whitespaces VALUES
  (' ', 'ASCII space'),
  ('\u00A0', 'Non-breaking space'),
  (' ', 'Ogham space mark'),
  (' ', 'en space'),
  (' ', 'em space');

SELECT codepoint visual_char,
       'U+'  || TO_CHAR(unicode(codepoint), '0XXX')
       codepoint_representation, description
  FROM different_whitespaces;

+-------------+--------------------------+--------------------+
| VISUAL_CHAR | CODEPOINT_REPRESENTATION | DESCRIPTION        |
|-------------+--------------------------+--------------------|
|             | U+0020                   | ASCII space        |
|             | U+00A0                   | Non-breaking space |
|             | U+1680                   | Ogham space mark   |
|             | U+2002                   | en space           |
|             | U+2003                   | em space           |
+-------------+--------------------------+--------------------+

SELECT COUNT(*) NumRows,
       COUNT(DISTINCT UNICODE(codepoint)) NumDistinctCodepoints,
       COUNT(DISTINCT codepoint COLLATE 'en-ci') DistinctCodepoints_EnCi,
       COUNT(DISTINCT codepoint COLLATE 'upper') DistinctCodepoints_Upper,
       COUNT(DISTINCT codepoint COLLATE 'lower') DistinctCodepoints_Lower
  FROM different_whitespaces;

+---------+-----------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | NUMDISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+-----------------------+-------------------------+--------------------------+--------------------------|
|       5 |                     5 |                       1 |                        5 |                        5 |
+---------+-----------------------+-------------------------+--------------------------+--------------------------+

CREATE OR REPLACE TABLE different_scripts(codepoint STRING, description STRING);

INSERT INTO different_scripts VALUES
  ('1', 'ASCII digit 1'),
  ('¹', 'Superscript 1'),
  ('₁', 'Subscript 1'),
  ('①', 'Circled digit 1'),
  ('੧', 'Gurmukhi digit 1'),
  ('௧', 'Tamil digit 1');

SELECT codepoint VISUAL_CHAR,
       'U+'  || TO_CHAR(UNICODE(codepoint), '0XXX') codepoint_representation,
       description
  FROM different_scripts;

+-------------+--------------------------+------------------+
| VISUAL_CHAR | CODEPOINT_REPRESENTATION | DESCRIPTION      |
|-------------+--------------------------+------------------|
| 1           | U+0031                   | ASCII digit 1    |
| ¹           | U+00B9                   | Superscript 1    |
| ₁           | U+2081                   | Subscript 1      |
| ①           | U+2460                   | Circled digit 1  |
| ੧           | U+0A67                   | Gurmukhi digit 1 |
| ௧           | U+0BE7                   | Tamil digit 1    |
+-------------+--------------------------+------------------+

SELECT COUNT(*) NumRows,
       COUNT(DISTINCT UNICODE(codepoint)) DistinctCodepoints,
       COUNT(DISTINCT codepoint COLLATE 'en-ci') DistinctCodepoints_EnCi,
       COUNT(DISTINCT codepoint COLLATE 'upper') DistinctCodepoints_Upper,
       COUNT(DISTINCT codepoint COLLATE 'lower') DistinctCodepoints_Lower
  FROM different_scripts;

+---------+--------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | DISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+--------------------+-------------------------+--------------------------+--------------------------|
|       6 |                  6 |                       1 |                        6 |                        6 |
+---------+--------------------+-------------------------+--------------------------+--------------------------+

Unterschiede beim Verwenden von ignorierbaren Codepunkten¶

Der Unicode-Sortierungsalgorithmus legt fest, dass Sortierungselemente (Codepunkte) ignorierbar sein können, was bedeutet, dass ein Codepunkt beim Zeichenfolgenvergleich und bei der Sortierung nicht berücksichtigt wird.

• Bei der Sortierungsspezifikation ci werden diese Codepunkte ignoriert. Dies kann die Suche nach ignorierbaren Codepunkten oder deren Ersetzung erschweren.

• Bei den Sortierungsspezifikationen upper und lower werden diese Codepunkte nicht ignoriert.

Beispiel: Der Codepunkt U+0001 ist ignorierbar. Wenn Sie diesen Codepunkt mit einer leeren Zeichenfolge unter Verwendung der Sortierungsspezifikation en-ci vergleichen, ist das Ergebnis TRUE, da U+0001 ignoriert wird:

SELECT '\u0001' = '' COLLATE 'en-ci';

+-------------------------------+
| '\U0001' = '' COLLATE 'EN-CI' |
|-------------------------------|
| True                          |
+-------------------------------+

Wenn Sie dagegen die Sortierungsspezifikation upper oder lower verwenden, ist das Ergebnis FALSE, da U+0001 nicht ignoriert wird:

SELECT '\u0001' = '' COLLATE 'upper';

+-------------------------------+
| '\U0001' = '' COLLATE 'UPPER' |
|-------------------------------|
| False                         |
+-------------------------------+

Ähnlich verhält es sich, wenn Sie die Funktion REPLACE aufrufen, um diesen Codepunkt aus einer Zeichenfolge zu entfernen. Wenn Sie die Sortierungsspezifikation en-ci verwenden, entfernt die Funktion den Codepunkt nicht, da U+0001 ignoriert wird.

Wie im folgenden Beispiel gezeigt, hat die von der Funktion REPLACE zurückgegebene Zeichenfolge dieselbe Länge wie die an die Funktion übergebene Zeichenfolge, da die Funktion das Zeichen U+0001 nicht entfernt.

SELECT
  LEN('abc\u0001') AS original_length,
  LEN(REPLACE('abc\u0001' COLLATE 'en-ci', '\u0001')) AS length_after_replacement;

+-----------------+--------------------------+
| ORIGINAL_LENGTH | LENGTH_AFTER_REPLACEMENT |
|-----------------+--------------------------|
|               4 |                        4 |
+-----------------+--------------------------+

Wenn Sie hingegen die Sortierungsspezifikation upper oder lower verwenden, entfernt die Funktion den Codepunkt aus der Zeichenfolge und gibt eine kürzere Zeichenfolge zurück.

SELECT
  LEN('abc\u0001') AS original_length,
  LEN(REPLACE('abc\u0001' COLLATE 'upper', '\u0001')) AS length_after_replacement;

+-----------------+--------------------------+
| ORIGINAL_LENGTH | LENGTH_AFTER_REPLACEMENT |
|-----------------+--------------------------|
|               4 |                        3 |
+-----------------+--------------------------+

Unterschiede bei der Repräsentation von Zeichen durch unterschiedliche Codepunkte¶

In Unicode können unterschiedliche Sequenzen von Codepunkten das gleiche Zeichen repräsentieren. Beispielsweise kann der griechische Kleinbuchstabe Iota mit Dialytika und Tonos durch das zusammengesetzte Zeichen mit dem Codepunkt U+0390, aber durch die Folge der Codepunkte U+03b9 U+0308 U+0301 für die nicht zusammengesetzten Zeichen dargestellt werden.

Wenn Sie die Sortierungsspezifikation ci verwenden, werden die verschiedenen Folgen von Codepunkten für ein Zeichen als dasselbe Zeichen behandelt. Beispielsweise werden der Codepunkt U+0390 und die Folge von Codepunkten U+03b9 U+0308 U+0301 als gleichwertig behandelt:

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'en-ci';

+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'EN-CI' |
|-------------------------------------------------|
| True                                            |
+-------------------------------------------------+

Um die Leistung für die Sortierungsspezifikationen upper und lower zu verbessern, werden die Sequenzen nicht auf die gleiche Weise behandelt. Zwei Sequenzen von Codepunkten gelten nur dann als gleichwertig, wenn sie nach der Umwandlung in Groß- oder Kleinbuchstaben dieselbe binäre Repräsentation ergeben.

Beispielsweise führt die Verwendung der Spezifikation upper mit dem Codepunkt U+0390 und die Sequenz der Codepunkte U+03b9 U+0308 U+0301 zu Zeichen, die als gleichwertig behandelt werden:

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'upper';

+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'UPPER' |
|-------------------------------------------------|
| True                                            |
+-------------------------------------------------+

Die Verwendung der Spezifikation lower führt zu Zeichen, die nicht gleich sind:

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'lower';

+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'LOWER' |
|-------------------------------------------------|
| False                                           |
+-------------------------------------------------+

Diese Unterschiede sind bei der Verwendung von upper (statt lower) weniger wahrscheinlich, da es nur einen zusammengesetzten Großbuchstaben-Codepunkt (U+0130) gibt, im Vergleich zu über 100 zusammengesetzten Kleinbuchstaben-Codepunkten.

Unterschiede bei Sequenzen von Codepunkten, die ein einzelnes Zeichen repräsentieren¶

In Fällen, in denen eine Sequenz von Codepunkten ein einzelnes Zeichen repräsentiert, erkennt die Sortierungsspezifikation ci, dass die Sequenz ein einzelnes Zeichen repräsentiert, und stimmt nicht mit einzelnen Codepunkten in der Sequenz überein.

Beispielsweise steht die Sequenz der Codepunkte U+03b9 U+0308 U+0301 für ein einzelnes Zeichen (den griechischen Kleinbuchstaben Iota mit Dialytika und Tonos). U+0308 und U+0301 repräsentieren Akzente, die auf U+03b9 angewendet werden.

Wenn Sie bei der Sortierungsspezifikation ci die Funktion CONTAINS verwenden, um festzustellen, ob die Sequenz U+03b9 U+0308 den Codepunkt U+03b9 oder U+0308 enthält, gibt die Funktion FALSE zurück, da die Sequenz U+03b9 U+0308 als einzelnes Zeichen behandelt wird:

SELECT CONTAINS('\u03b9\u0308', '\u03b9' COLLATE 'en-ci');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U03B9' COLLATE 'EN-CI') |
|----------------------------------------------------|
| False                                              |
+----------------------------------------------------+

SELECT CONTAINS('\u03b9\u0308', '\u0308' COLLATE 'en-ci');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U0308' COLLATE 'EN-CI') |
|----------------------------------------------------|
| False                                              |
+----------------------------------------------------+

Um die Leistung zu verbessern, behandeln die Spezifikationen upper und lower diese Sequenzen nicht als ein einzelnes Zeichen. Im obigen Beispiel gibt die Funktion CONTAINS den Wert TRUE zurück, da diese Spezifikationen die Sequenz von Codepunkten als separate Zeichen behandeln:

SELECT CONTAINS('\u03b9\u0308', '\u03b9' COLLATE 'upper');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U03B9' COLLATE 'UPPER') |
|----------------------------------------------------|
| True                                               |
+----------------------------------------------------+

SELECT CONTAINS('\u03b9\u0308', '\u0308' COLLATE 'upper');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U0308' COLLATE 'UPPER') |
|----------------------------------------------------|
| True                                               |
+----------------------------------------------------+

Unterschiede, wenn Änderungen an der Groß-/Kleinschreibung zu mehreren Codepunkten führen¶

Bei einigen zusammengesetzten Zeichen wird die Groß-/Kleinbuchstaben-Version des Zeichens durch eine Sequenz von Codepunkten dargestellt. Beispiel: Das Großbuchstabenzeichen für das deutsche Zeichen ß ist zum Beispiel eine Folge von zwei S (SS).

Obwohl ß und SS gleichwertig sind, liefert die Suche nach ß und SS unterschiedliche Ergebnisse, wenn Sie die Sortierungsspezifikation upper verwenden. Die durch die Groß-/Kleinschreibung erzeugten Sequenzen stimmen entweder vollständig oder gar nicht überein.

SELECT CONTAINS('ß' , 's' COLLATE 'upper');

+--------------------------------------+
| CONTAINS('SS' , 'S' COLLATE 'UPPER') |
|--------------------------------------|
| False                                |
+--------------------------------------+

SELECT CONTAINS('ss', 's' COLLATE 'upper');

+-------------------------------------+
| CONTAINS('SS', 'S' COLLATE 'UPPER') |
|-------------------------------------|
| True                                |
+-------------------------------------+

Unterschiede in der Sortierreihenfolge¶

Die Sortierung für die Sortierungsspezifikationen upper und lower funktioniert anders als die Sortierung für die Spezifikation ci:

• Mit der Spezifikation ci werden Zeichenfolgen nach dem Sortierschlüssel sortiert. Im Allgemeinen kann der Sortierschlüssel die Groß-/Kleinschreibung, den Akzent, das Gebietsschema usw. berücksichtigen.

• Bei den Spezifikationen upper und lower werden die Zeichenfolgen nach Codepunkten sortiert, um die Performance zu verbessern.

Beispiel: Einige Zeichen im ASCII-Bereich (z. B. + und -) werden anders sortiert:

SELECT '+' < '-' COLLATE 'en-ci';

+---------------------------+
| '+' < '-' COLLATE 'EN-CI '|
|---------------------------|
| False                     |
+---------------------------+

SELECT '+' < '-' COLLATE 'upper';

+---------------------------+
| '+' < '-' COLLATE 'UPPER' |
|---------------------------|
| True                      |
+---------------------------+

Ein weiteres Beispiel: Zeichenfolgen mit ignorierten Codepunkten werden in einer anderen Reihenfolge sortiert:

SELECT 'a\u0001b' < 'ab' COLLATE 'en-ci';

+-----------------------------------+
| 'A\U0001B' < 'AB' COLLATE 'EN-CI' |
|-----------------------------------|
| False                             |
+-----------------------------------+

SELECT 'a\u0001b' < 'ab' COLLATE 'upper';

+-----------------------------------+
| 'A\U0001B' < 'AB' COLLATE 'UPPER' |
|-----------------------------------|
| True                              |
+-----------------------------------+

Darüber hinaus werden Emojis anders sortiert:

SELECT 'abc' < '❄' COLLATE 'en-ci';

+-----------------------------+
| 'ABC' < '❄' COLLATE 'EN-CI' |
|-----------------------------|
| False                       |
+-----------------------------+

SELECT 'abc' < '❄' COLLATE 'upper';

+-----------------------------+
| 'ABC' < '❄' COLLATE 'UPPER' |
|-----------------------------|
| True                        |
+-----------------------------+

Die Sortierung wird nur für Zeichenfolgen bis zu 8 MB unterstützt.¶

Obwohl der Snowflake-Datentyp VARCHAR Zeichenfolgen bis zu 16 MB unterstützt, unterstützt Snowflake die Sortierung nur, wenn die resultierende Zeichenfolge eine maximale Größe von 8 MB aufweist. (Einige Sortieroperationen können zu einer Verlängerung der Zeichenfolge führen).

Sortierung (Collation) bei UDFs nicht unterstützt¶

Snowflake unterstützt keine Sortierung mit UDFs (benutzerdefinierte Funktionen):

• Sie können von einer UDF keinen sortierten Zeichenfolgenwert zurückgeben. Der Server meldet, dass der tatsächliche Rückgabetyp nicht mit dem deklarierten Rückgabetyp kompatibel ist.

• Wenn Sie einen sortierten Zeichenfolgenwert an eine UDF übergeben, wird die Sortierungsinformation nicht übergeben. Die UDF behandelt die Zeichenfolge wie eine nicht sortierte Zeichenfolge.

Sortierung für Zeichenfolgen in VARIANT-, ARRAY- oder OBJECT-Werten nicht unterstützt¶

Zeichenfolgen, die innerhalb eines VARIANT, OBJECT oder ARRAY-Wertes gespeichert sind, enthalten keine Sortierungsspezifikation. Deshalb gilt:

• SortierungspezifikationBeim Vergleich dieser Werte wird immer die Sortierung 'utf8' verwendet.

• Wenn ein VARCHAR-Wert mit einer Sortierungsspezifikation verwendet wird, um einen ARRAY-, OBJECT- oder VARIANT-Wert zu erstellen, wird die Sortierungsspezifikation nicht beibehalten.

• Sie können dennoch einen in einem ARRAY, OBJECT oder VARIANT gespeicherten Wert vergleichen, indem Sie den Wert extrahieren, in VARCHAR umwandeln und eine Sortierungsspezifikation hinzufügen. Beispiel:

  COLLATE(VARIANT_COL:fld1::VARCHAR, 'en-ci') = VARIANT_COL:fld2::VARCHAR
  

  Copy