Lista trybów sortowania (COLLATION)

Lista COLLATION? A co to?

COLLATION to definicja sposobu porównywania dwóch wartości tekstowych w SQL Server. Podczas instalacji SQL Server wybiera domyślny tryb sortowania, który będzie wykorzystywany we wszystkich przypadkach - chyba, że wskażemy inaczej. Okazuje się, że wiele osób nie ma świadomości, że coś takiego jak COLLATION gdzieś w serwerze siedzi.

Skąd SQL Server wie, jaki domyślny tryb wybrać? Wybiera go na podstawie ustawień regionalnych i językowych systemu operacyjnego. W jednym z okien konfiguracyjnych wyświetlanych podczas instalowania SQL Server pojawia się pole z możliwością zmiany tego trybu, ale najczęściej zostawia się ustawienie domyślne. Dla polskiej wersji systemu Windows będzie to najprawdopodobniej opcja Polish_CI_AS. Oznacza to, że użyty będzie polski alfabet, wielkość liter nie będzie miała znaczenia i rozróżniane będą litery akcentowane. Więcej szczegółów dalej.

Jak wyświetlić dostępną listę opcji COLLATION w SQL Server

Zdarza się, że sposób porównywania dwóch wartości tekstowych ustawiony dla całego SQL Server nie jest dla nas właściwy. O ile zmiana COLLATION dla całego serwera jest wyjątkowo trudna i, tak prawdę mówiąc, łatwiej zainstalować zupełnie nowy serwer, o tyle zmiana COLLATION dla konkretnej bazy danych, wybranej tabeli lub kolumny nie powinno stanowić problemu. Jakie są możliwe opcje trybów sortowania? Jak je pobrać? Pokazałem to na poniższym listingu:

SELECT * FROM sys.fn_helpcollations()

Wynikiem działania będzie długa lista, której część pokazałem poniżej:

name	description
Albanian_BIN	Albanian, binary sort
Albanian_BIN2	Albanian, binary code point comparison sort
Albanian_CI_AI	Albanian, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Albanian_CI_AI_WS	Albanian, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive
...	...

Nie są to wszystkie informacje o trybach COLLATION. Każda z opcji ma pewien, niewielki, zestaw właściwości. Aby się do nich dostać należy skorzystać z funkcji COLLATIONPROPERTY. Pokazałem to na poniższym listingu:

SELECT name,
  [description],
  COLLATIONPROPERTY(name, 'CodePage') [CodePage],
  COLLATIONPROPERTY(name, 'ComparisonStyle') ComparisonStyle,
  COLLATIONPROPERTY(name, 'LCID') LCID,
  COLLATIONPROPERTY(name, 'Version') [Version]
FROM sys.fn_helpcollations()

Tym razem otrzymamy nieco rozszerzony zestaw informacji:

name	description	CodePage	LCID	ComparisonStyle	Version
Albanian_BIN	Albanian, binary sort	1250	1052	0	0
Albanian_BIN2	Albanian, binary code point comparison sort	1250	1052	0	0
Albanian_CI_AI	Albanian, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive	1250	1052	196611	0
Albanian_CI_AI_WS	Albanian, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive	1250	1052	65539	0
...	...	...	...	...	...

Poszczególne kolumny oznaczają:

Nazwa	Opis
name	Nazwa COLLATION.
description	Opis COLLATION.
CodePage	Strona kodowa. Dla Polski będzie to 1250.
LCID	Kod lokalizacji (ang. locale identifier). Dla Polski będzie to 1045.
ComparisonStyle	Styl porównania Windows. W API systemu Windows, w bibliotece Kernel32.dll, znajduje się funkcja CompareStringEx i tam odsyłam po szczegóły. Dla porównań binarnych w kolumnie będzie 0.
Version	Wersja COLLATION.

Interpretacja nazwy COLLATION

Lista COLLATION jest długa. Dobrze, że w całym tym nazewnictwie są pewne reguły. Nazwa COLLATION zaczyna się od języka, który definiuje jakiś alfabet. W podanym przykładzie był to język albański. Polskie opcje rozpoczynają się przedrostkiem Polish. Po definicji języka następuje (lub nie) numer wersji COLLATION. Nowsza wersja polskiego trybu sortowania może więc przyjąć postać "Polish_100...". Po nazwie języka (alfabetu) i wersji lub po samej nazwie języka następuje seria dwuliterowych oznaczeń.

COLLATION i wielkość liter

Pierwsza grupa określa sposób traktowania małych i dużych liter. Mamy zatem CS (ang. case sensitive - wrażliwy na wielkość), dla którego mała litera będzie mniejsza niż duża litera, oraz CI (ang. case insensitive - niewrażliwy na wielkość), dla którego mała i duża litera to będzie to samo. Popatrzmy na przykład:

SELECT * FROM
(VALUES(N'A'), (N'a'), (N'Ą'), (N'ą')) S(Tekst)
ORDER BY Tekst COLLATE Polish_CS_AI

Użyłem polskiego trybu, z oznaczeniem CS. Wynik zapytania będzie następujący:

Tekst
a
A
ą
Ą

Jeżeli teraz zmienimy tryb sortowania na Polish_CI_AI to możemy otrzymać trochę inny rezultat. Możemy, ale nie musimy. Taki sam rezultat równie dobry, bo przecież nie zależało nam na porównaniach uwzględniających wielkość liter. Jeżeli chcemy zobaczyć różnicę, polecam poeksperymentować z sortowaniem odwrotnym (DESC).

COLLATION z akcentem

W nazwie COLLATION z poprzedniego przykładu pojawiła się druga grupa liter. Oznacza ona akcent i sposób jego obsługi. Mamy zatem AS (ang. accent sensitive - wrażliwy na akcent) oraz AI (ang. accent insensitive). Popatrzmy na poniższy przykład:

SELECT * FROM
(VALUES(N'A'), (N'Ą'), (N'a'), (N'ą')) S(Tekst)
ORDER BY Tekst COLLATE Polish_CS_AI

Rezultat może być zaskakujący, bo większość mylnie sądzi, że nasze ą, ę, ż, ź, ś, ć, ń, ł i ó są akcentowane. Przedstawiłem ten przykład, aby ten mit obalić. Popatrzmy na wynik:

Tekst
a
A
ą
Ą

Co to ten akcent? Popatrzmy na jeszcze jeden przykład i wszystko powinno się wyjaśnić:

SELECT 'Ogonek' [OK] WHERE N'e' = N'ę' COLLATE Polish_CI_AI
UNION ALL
SELECT 'Akcent' [OK] WHERE N'e' = N'ë' COLLATE Polish_CI_AI

Przy okazji pokazałem, w jaki sposób użyć COLLATION w zwykłej instrukcji porównania. Nie o to jednak chodziło, a o rezultat, który jest następujący:

OK
Akcent

Jak można się przekonać, nasz polski ogonek został odrzucony, natomiast e, w trybie ignorownia akcentów jest równe nieco innemu e, z kropeczkami.

Inne opcje

W nazwie mogą się pojawić jeszcze dwa dwuliterowe symbole. KS oznacza, że rozróżniane będą symbole z japońskiego systemu pisma sylabicznego kana. Przyznam się, że nigdy nie musiałem się tym zajmować. Może to i dobrze. Powiem otwarcie: przykładu nie będzie. WS oznacza, że rozróżniane będą dwie reprezentacje tego samego znaku, ale w innej postaci binarnej. Znak zapisany na jednym bajcie będzie się różnił od tego samego znaku zapisanego na dwóch bajtach.

W nazwie COLLATION mogą się pojawić jeszcze inne znaki, które nie zostały opisane. Co znaczają nazwy Latin1_General_BIN i Latin1_General_BIN2?

COLLATION i porównania binarne

Przyrostek BIN informuje nas, że stosowany będzie binarny sposób porównywania dwóch wartości tekstowych. Co to oznacza? Ano to, że dane będą porównywane jako sekwencja dowolnych bajtów. To, że te bajty reprezentują jakiś napis nie będzie miało znaczenia. Oznacza to też najwyższą wydajność porównań. Algorytm nie musi sprawdzać jak w danym alfabecie interpretować ą i gdzie to umieścić. Litera ą ma swój bitowy odpowiednik i to te bity będą porównywane. Żeby nieco rozjaśnić zagadnienie popatrzmy na przykład:

SELECT ASCII('a') a, ASCII('ą') ą, ASCII('b') b

SELECT * FROM
(VALUES(N'a'), (N'ą'), (N'b')) S(Tekst)
ORDER BY Tekst COLLATE Latin1_General_BIN

Wynikiem działania będą dwie tabele. Pierwsza:

a	ą	b
97	185	98

I druga:

Tekst
a
b
ą

Widać, że porównywane są wartości liczbowe. Jest to typowo komputerowy sposób postrzegania świata i może dawać rezultaty nieakceptowalne przez ludzi.

Różnica pomiędzy przyrostkiem BIN i BIN2 jest niewielka. Jeżeli typ danych to char lub varchar, oba tryby są równoważne. Jeżeli mamy do czynienia z typami nchar lub nvarchar, BIN porównuje wartości bajt po bajcie, natomiast BIN2 jako jednostkę porównawczą wykorzystuje wartość pełnego znaku UNICODE. Ot taki niuans.

Kategoria:SQL Server

Paweł Dyl, 2013-12-20

Spis treści: