Im Projekt bei einem Kunden wurden in der Datenbank Bestellnummern gespeichert. Damit die Bestellnummern alle eine vorgegebene Länge haben, wurden sie mit führenden Nullen aufgefüllt. Deshalb wurden die Bestellnummern in einem Varchar-Feld gespeichert. Um geplante Bestellvorgänge und tatsächlich getätigte Bestellungen in einer Datenbank pflegen zu können, wurden die noch nicht bestätigten Bestellvorgänge durch „negative“ Bestellnummern, also durch ein Minuszeichen als Präfix, gekennzeichnet. Bei der Bearbeitung der Bestellvorgänge zu einer Kundennummer war es nötig Duplikate zu entfernen, also im Falle mehrerer Bestellnummern zu einer Kundennummer immer die letzte Bestellnummer zu erhalten und die übrigen zu verwerfen.
Vereinfacht lässt sich das durch folgende Beispieltabelle veranschaulichen:
CREATE TABLE [TSQL2012].[HR].[OrderTable](
[OrderNo_varchar] [nvarchar](30) NOT NULL,
[CustNo_int] [int] NULL
) ON [PRIMARY];
INSERT INTO [TSQL2012].[HR].[OrderTable] (OrderNo_varchar, [CustNo_int])
VALUES ('010', 100), ('020', 200), ('030', 300),
('-020', 200), ('015', 300), ('-015', 150), ('-025', 250);
OrderNo_varchar CustNo_int 010 100 020 200 030 300 -020 200 015 300 -015 150 -025 250
Dabei möchten wir bei den doppelten Einträgen zu den Kundennummern 300 und 200 die Bestellnummer „030“ und „020“ erhalten (und die „015“ und „-020“ ausfiltern).
Um die Duplikate zu entfernen, kann man die Tabelle nach CustNo_int-Spalte partitionieren, jede Partition nach OrderNo_varchar absteigend sortieren und die Zeilennummer für die jeweilige Partition (mit der ROW_NUMBER()-Fensterfunktion) generieren. Der Eintrag mit der Zeilennummer=1 sollte dabei den größten OrderNo_varchar-Wert unter den doppelten (oder auch mehrmals vorkommenden) CustNo_int-Werten beinhalten:
WITH Table_CTE ([OrderNo_varchar], [CustNo_int], ROWNUMBER)
AS
(
SELECT [OrderNo_varchar]
,[CustNo_int]
,ROW_NUMBER() OVER(PARTITION BY [CustNo_int] ORDER BY [OrderNo_varchar] DESC) AS ROWNUMBER
FROM [TSQL2012].[HR].[OrderTable]
)
SELECT [OrderNo_varchar], [CustNo_int]
FROM Table_CTE
WHERE ROWNUMBER=1
Dabei entsteht ein unerwartetes Resultat für CustNo_int=200 („-020“ anstatt „020):
OrderNo_varchar CustNo_int 010 100 -015 150 -020 200 -025 250 030 300
Die Erklärung dafür ist, dass eben die Zeichen und nicht die Zahlen sortiert werden und standardmäßig das Minuszeichen vor einer Zahl für Spalten mit character-Datentypen von der SQL Server Wörterbuch-Sortierung ignoriert wird:
SELECT [OrderNo_varchar]
,[CustNo_int]
FROM [TSQL2012].[HR].[OrderTable]
ORDER BY [OrderNo_varchar]
OrderNo_varchar CustNo_int 010 100 015 300 -015 150 020 200 -020 200 -025 250 030 300
Dabei sortiert SQL Server die Zeichenfolgen intern anhand der so genannten Collation (Deutsch: Sortierungseinstellung). Die Collation legt die Regeln fest, nach denen character-Daten sortiert und verglichen werden [1].
Es werden zwei Arten von Collations beim SQL Server unterschieden: Windows Collations und Binäre Collations.
Windows Collations basieren auf Windows-Systemgebietschemata, die unter Systemsteuerung-> Regions- und Sprachoptionen-> Erweitert zu finden sind. Die Standard-Collation bei der SQL Server Installation entspricht (üblicherweise) der Collation von Windows-Gebietsschema. So lautet die Standard-Collation für Windows-Gebietsschema „Deutsch (Deutschland)“ der Collation „Latin1_General_CI_AS“ [2].
Die Windows Collations legen fest:
1) welches Alphabet verwendet wird, wenn alphabetische Sortierung mit den entsprechenden Regeln angewendet wird (z.B. „ä“ folgt auf „a“), und
2) die Zeichensatztabelle (code page) für die Speicherung von Nicht-Unicode-Zeichen.
Bei binären Collationen erfolgen Sortierung und Vergleich der Daten (im Gegensatz zu Windows Collations) streng auf Basis der Bitmuster, die für die einzelnen Zeichen definiert sind. Dabei bestimmt eine binäre Collation das Gebietsschema und die ANSI Zeichensatztabelle. Dies erzwingt eine binäre Sortierreihenfolge.
Mit dem Befehl „COLLATE“ gefolgt nach dem „ORDER BY“-Befehl kann eine bestimmte Collation (z.B. eine binäre) angewendet werden:
SELECT [OrderNo_varchar]
,[CustNo_int]
FROM [TSQL2012].[HR].[OrderTable]
ORDER BY [OrderNo_varchar]
COLLATE SQL_Latin1_General_CP850_BIN;
Man bekommt damit das erwartete Ergebnis:
OrderNo_varchar CustNo_int -015 150 -020 200 -025 250 010 100 015 300 020 200 030 300
Dabei kann eine bestimmte Collation nicht nur für einen einzelnen Befehl verwendet werden, sondern auf auch Server-Ebene, Datenbankinstanz-Ebene oder Spalten-Eben [3]. Somit kann die endgültige SQL-Abfrage für unser Praxisszenario wie folgt lauten:
WITH Table_CTE ([OrderNo_varchar], [CustNo_int], ROWNUMBER)
AS
(
SELECT [OrderNo_varchar]
,[CustNo_int]
,ROW_NUMBER() OVER(
PARTITION BY [CustNo_int] ORDER BY [OrderNo_varchar] COLLATE SQL_Latin1_General_CP850_BIN DESC) AS ROWNUMBER
FROM [TSQL2012].[HR].[OrderTable]
)
SELECT [OrderNo_varchar], [CustNo_int]
FROM Table_CTE
WHERE ROWNUMBER=1
OrderNo_varchar CustNo_int 010 100 -015 150 020 200 -025 250 030 300
Datenvergleiche bei binären Collations basieren auf den Codepoints aus der ANSI-Zeichensatztabelle (für Nicht-Unicode-Datentypen) oder Unicode-Codepoints (für Unicode-Datentypen). Bei binären Sortierungen von Unicode-Datentypen wird das Gebietsschema nicht berücksichtigt. Zum Beispiel liefern „Latin_1_General_BIN“ und „Japanese_BIN“ gleiche Ergebnisse, wenn sie auf Unicode-Daten angewendet werden.
Die Collation-Bezeichnungen unterliegen gewissen Syntax-Regeln. So bedeutet z.B. Latin1_General_CI_AS, dass Sortierungsregeln der Latin1_General entsprechen. Groß-und Kleinschreibung werden nicht unterschieden (CI für case-insensitive) und Akzente werden unterschieden (AS für accent-sensitive). Bei SQL_Latin1_General_CP850_BIN steht CP850 für Codepage 850 und BIN für binäre Collation (siehe mehr dazu auf [4] und [5])
Nun werden einige nützliche Collation-Befehle aufgeführt.
Liste der verfügbaren Collations auf dem Server:
SELECT name, description FROM ::fn_helpcollations();
Aktuell genutzte Collation auf dem Server:
SELECT SERVERPROPERTY('collation');
Aktuell genutzte Collation auf der Datenbank:
SELECT DATABASEPROPERTYEX('database_name','collation');
Änderung der Collation für eine Datenbank:
ALTER DATABASE myDB COLLATE SQL_Latin1_General_CP850_BIN;
Änderung der Collation für eine Spalte in einer Tabelle:
ALTER TABLE myTable ALTER COLUMN mycol NVARCHAR(10) COLLATE SQL_Latin1_General_CP850_BIN;