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:
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:
[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:
(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:
(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ć:
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 * 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
Brak komentarzy - bądź pierwszy