SQL Server verschluckt Minus – Besonderheit von Collations bei Sortierungen in SQL

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;

 

Schreibe einen Kommentar