Jeder der sich mit dem SQL Server beschäftigt, dem werden mit Sicherheit schon einmal Collations über den Weg gelaufen sein. Collations dienen im allgemeinen
der Sortierung von Daten innerhalb einer Datenbank. Die Standard Collation beim SQL Server ist die Latin1_General_CI_AS, was ausgeschrieben für Latin1-General, case-insensitive, accent-sensitive steht. Diese Collation kann auch mit CS für case-sensitive und mit AI für accent-insensitive oder auch mit BIN (anstatt Cx_Ax) für eine Binäre Sortierung verwendet werden. Alle möglichen Sortierungen im SQL Server erhält man über folgendes SQL-Statement.
SELECT * FROM
::fn_helpcollations()
Relevant ist die Sortierung für die verschiedenen Sprachen und die darin enthaltenen Sonderzeichen. Bei deutschen Umlauten wie Ä, Ö, Ü spielen Unterschiede in den Sortierung, mit Ausnahme der binären Sortierreihenfolge, jedoch keine Rolle.
Wird zusätzlich zur Sortierung noch der Befehel DISTINCT mit verwendet, so ändert sich die Sortierung wie folgt:
Hier sieht man, das sowohl unsortiert wie auch unter Verwendung von Case-Insensitive die Name Mueller und MUELLER sowie Müller und MÜLLER zusammengefasst worden sind. Bei der Verwendung von Case-Sensitive sowie der binären Sortierung stellen diese Datensätze logischerweise jeweils eindeutige Datensätze dar.
Ganz anders sieht dies jedoch aus, wenn anstatt der Umlaute unser beliebtes ß in den Datensätzen vorkommt. Bei der normalen Sortierung liegen zuvor die gleichen Reihenfolgen vor, wie bei den Müller’s.
Wird nun allerdings zusätzlich im SQL Statement das ß verwendet, so ändert sich die Sortierung wie folgt ab.
Hier fällt auf, das bei der Verwendung von Case-Insensitive alle 4 Werte identisch sind, also im Gegensatz zu Mueller und Müller, die Namen Weiß und Weiss identisch sind. Wird auf die Groß-/Kleinschreibung mitgeachtet, also Case-Sensitive verwendet, so sind Weiß und Weiss auf einmal identisch, WEIß und WEISS jedoch nicht, bei der binären Sortierreihenfolge dann aber auf einmal wieder doch.
Diese entsprechenden Ergebnise liegen daran, dass es im Gegensatz zun den Umlauten Ä, Ö, Ü kein Großbuchstaben zum ß existiert. Dadurch wird das ß immer wie ein ss interpretiert. WEIß ist somit identisch zu WEIss aber nicht zu WEISS.
ßIS != SSIS ?
In vielen Fällen spielt die oben aufgezeigten Unterschiede bei der Sortierung innerhalb des SQL Servers keine große Rolle. Zumindest ist mir noch nie ein wirkliches Problem begegnet. Ganz anders sieht das aber aus, wenn man die SQL Server Integration Services im Einsatz hat. Hier muss man zusätzlich zu den Collations noch darauf achten, “wer” gerade etwas bearbeitet und in welchen Kombinationen, da man nämlich zusätzlich noch die Sortierung innerhalb des .NET Frameworks und innerhalb des SQL Servers beachten muss.
Hier nun ein paar Beispiele, wie verschiedene Komponenten und Kombinationen Daten sortieren. Zum besseren Vergleich, habe ich neben den Datensätzen “WEIß” auch wieder die Müller’s mit an Bord.
OLE DB Source
Die OLE DB Source, wie im Übrigen auch ADO NET oder ODBC, verhält sich ganz “normal” und gibt die Daten jeweils so zurück wie oben bei der Standard Collation des SQL Server beschrieben.
Selbstverständlich lassen sich beim Laden der Daten über die entsprechenden Quellen die Collation auch anpassen, so dass man die Daten z.B. auch mit einer binär Sortierung laden könnte.
SELECT DISTINCT [Name] COLLATE Latin1_General_BIN FROM [Daten] ORDER BY [Name] COLLATE Latin1_General_BIN
Aggregate – Count, Count distinct und Group by
In der Aggregate Komponente können die 3 wesentlichen unterschiedliche Einstellungen Count, Count distinct und Group by vorgenommen werden.
Bei der Einstellung Group by kann mit den Comparison Flag eine zusätzliche Einstellung Ignore Case gesetzt werden. Durch diese beiden unterschiedlichen Einstellungen ändert sich die Ausgabe der Komponente wesentlich.
Die Ergebnisse lassen sich dabei mit der Collation Latin1_General_CI_AS vergleichen.
Bei den Einstellungen Count und Count distinct lässt sich jedoch keine weitere Einstellung wie Ignore Case oder ähnliches vornehmen. Das Ergebnis bei einem Count und einem Count distinct ist bei der Komponente somit immer 8.
Dies ist relativ verwunderlich, da der SQL Server bei der Standardeinstellung bei einem Count zwar auf 8 kommt, bei einem Count distinct jedoch auf 3. Wird bei dem SQL Server die Collation auf Case-Sensitive geändert, ist das Ergebnis 7. Nur bei einer binären Sortierung ist das Ergebnis 8.
Somit sind an dieser Stelle, die Ergebnisse nicht wirklich zu vergleichen bzw. innerhalb der Komponente nicht konsistent gegenüber dem SQL Server zu setzen. Hier fehlt eine Möglichkeit um bei einem Count distinct auch auf das Ergebnis 3 zukommen.
Sort
Bei der Sort-Komponente existieren 4 verschiedene Konfigurationsmöglichkeiten, die dieses Beispiel betreffen.
Dies sind das Sortieren mit den jeweiligen Optionen “Remove rows with duplicate sort values” – was einem DISTINCT entspricht – und wie auch bei Aggregate dem Comparison Flag Ignore Case. Die Ergebnisse sehen dabei recht unterschiedlich aus:
Remove rows with duplicate sort values = False
Ignore Case = False
Bei diesen Einstellungen werden alle Datensätze sortiert zurückgegeben. Diese Einstellung entspricht der Collation Latin1_General_CS_AS.
Remove rows with duplicate sort values = True
Ignore Case = False
Bei dieser Einstellung werden nur 7 Datensätze zurückgegeben. Diese Einstellung entspricht der Collation Latin1_General_CS_AS.
Remove rows with duplicate sort values = True
Ignore Case = True
Bei dieser Einstellung werden nur 3 Datensätze zurückgegeben. Bei diesem Beispiel kann keine vergleichbare Collation für den SQL Server gefunden werden. Wie man bei den obigen Datensätzen sieht, gibt der SQL Server bei den Case-Insensitive Einstellungen immer einen kleinen Mueller mit zurück.
Remove rows with duplicate sort values = False
Ignore Case = True
Bei dieser Einstellung werden alle Datensätze zurückgegeben. Interessant ist bei dieser Einstellung, das die Sortierung mit keiner der zuvor auf dem SQL Server verwendeten Collations 100% übereinstimmt. Zwar sind die Namen “Weiß” als Latin1_General_CI_AS sortiert, die Müller’s sind jedoch als Latin1_General_CS_AS sortiert, obwohl die Daten vollständig anderweitig sortiert in die Komponente laufen.
Zumindest die vergleichbare DISTINCT Einstellung der Sort Komponente mit den SQL Server Collations verglichen werden. Die Sortierung der einzelnen Groß-/Kleinschreibungen entspricht an dieser Stelle jedoch nicht der in dem SQL Server verwendeten Collations.
LookUp
Die LookUp Komponente gehört denke ich mit zu den häufiger verwenden Komponenten.
Dabei werden sehr oft Namen oder besser gesagt Strings in einer bestehenden Tabelle nachgeschlagen und die entsprechende ID des Wertes dem Datensatz hinzugefügt bzw. auf neue Datensätze dann anderweitig reagiert.
Die LookUp Komponente bietet viele verschiedene Einstellungsmöglichkeiten, die bei der Verarbeitung von Datensätzen in denen ggf. ein ß vorkommen kann unbedingt beachtet werden sollte.
Der Full-Cache mit einer OLE DB Connection liefert zu jedem einzelnen Datensatz genau einen Treffer zurück, es werden also die Datensätze Weiß = Weiß, Weiss = Weiss usw. getroffen.
Das Verhalten ändert sich nun aber wieder vollständig wenn der Cache auf No-Cache oder auch Partial Cache geändert wird. In diesem Fall wird auf die Collation des SQL-Servers zugegriffen und dann ist Weiß = Weiß, Weiss = Weiß, WEISS = Weiß und WEIß = Weiß.
Besonders hier ist denke ich ein hohes Fehlerpotential vorhanden, da der Cache während der Entwicklung schnell umgestellt werden kann ohne dass dies weitere Änderungen im Datenfluss nach sich zieht, die Ergebnisse dadurch aber Grundlegend geändert werden können.
Flat File Source, Merge Join und die Sortierung
Spätestens in der Kombination verschiedener Komponenten sollte man die Ergebnisse sehr stark analysieren und vergleichen. Im Folgenden Beispiel werden Daten aus einer Textdatei zusammen mit Daten aus einer Datenbank über einen Merge Join zusammengeführt. Je nach Kombination und Einstellungen der einzelnen Task unterscheiden sich die Ergebnisse hier extrem von einander.
So ändert sich die Ausgabe des Merge Join z.b. je nachdem ob die Daten aus der OLE DB Source mit der Standard Collation, Case Sensitive oder einer binäre Sortierung geladen werden.
Speziell dieses Beispiel zeigt, wie stark die Unterschiede zwischen den Datensätzen innerhalb eines SSIS Pakets sein können und wie wichtig es ist, Datenflüsse, die entsprechenden Datensätze und deren Ergebnisse genauestens zu testen und zu vergleichen.
Wer auf den Merge Join zurückgreifen möchte oder muss, dem empfehle ich an dieser Stelle meinen Beitrag einen Hashwert zu erstellen, der auch hierbei sehr gut helfen kann.
Danke!
An dieser Stelle auch noch einmal vielen Dank an Olaf Pietsch und Torsten Strauß, die mir beide verschiedene Hinweise gegeben haben, selber einige Demopakete gebaut haben und mit mir zusammen auch die verschiedenen Demos getestet haben.
An dieser Stelle dann auch direkt der Hinweis auf den Vortrag “SSIS – Performanceoptimierung” von Torsten am Dienstag (21.08.2012). Nähere Informationen dazu finden sich auf der Seite der Regionalgruppe Ruhrgebiet.
