Header RSS-подписка на обновления сайта eMail-подписка на обновления сайта

Понимаете ли вы коллейшены? Часть 5/8.









Вот обещанный в конце предыдущей части код, демонстрирующий проблему сортировки лингвистическими алгоритмами:

1
2
3
4
5
6
7
8
9
SELECT CASE WHEN
   'ЧБ'+CHAR(0xB2) COLLATE SQL_Latin1_General_Cp1251_CI_AS < 'ЧБ'+CHAR(0xB5)
   THEN 'YES' ELSE 'NO' END
SELECT CASE WHEN
   'ЧБ'+CHAR(0xB2) COLLATE SQL_Latin1_General_Cp1251_CS_AS < 'ЧБ'+CHAR(0xB5)
   THEN 'YES' ELSE 'NO' END
SELECT CASE WHEN
   'ЧБ'+CHAR(0xB2) COLLATE Cyrillic_General_BIN < 'ЧБ'+CHAR(0xB5)
   THEN 'YES' ELSE 'NO' END

К удивлению почти всех запускающих код приведенный в конце предыдущей части впервые, оба первых коллейшена печатают NO вместо ожидаемого YES, и только последний справляется с задачей. Почему так? Потому что вес символа и его код никак не связаны! То, что почти всегда сохраняется правило «больше там — больше тут» всего лишь историческое стечении обстоятельств, не более того. Если бы кодовую страницу 1251 «срисовывали» бы с русской клавиатуры (слева направо и сверху вниз, по рядам клавиш) веса и коды были полностью разделены и не имели бы даже такой, «подразумеваемой» связи. Приведенный пример лишь констатирует уже и так понятный факт: есть символы (их совсем мало, но они — есть) стоящие в таблице кодов выше (т.е. ближе к символу 0x0), но «весящие» при этом больше. B2 тяжелее B5, вот и вся история, а так же обещанная «засада» вынуждающая вас обратиться только к числовому коллейшену дабы иметь «ASCII-предсказуемый» результат. Да, это будет совсем не часто, но все же...

Так вот по поводу «регистро-независимой-ASCII» сортировки автор не может сказать ничего утешительного. Нет в SQL Server аналога OrdinalIgnoreCase в дот-нете, увы. И в ближайшие планы команды SQL/Microsoft (насколько может быть в курсе этих планов автор) не входят какие-то подвижки в этом направлении. Придется как-то выкручиваться, если что... :(

Но и это еще не все, автор же обещал что «интересных вопросов» будет 2! ;) Вот второй: если зайти на страницу с именами всех коллейшенов текущей группы (ссылку на нее автор приводил в предыдущей части статьи) и поискать там варианты числовых сортировок, то мы обнаруживаем:

  • Latin1_General_BIN
  • Hungarian_BIN
  • Turkish_BIN
  • Hebrew_BIN
  • Greek_BIN
  • ...

и еще тележку иных BIN-ов. А спрашивается в задаче — куда столько-то?? Если алгоритм у всех, очевидно, одинаков («сравнивай значения байт-в-байт», буквально), то почему не ограничились чем-то вроде All_BIN, да и дело с концом? Ведь мы же, в обсуждаемой ситуации, имеем дело с кодами, и только с ними — нам все-равно что скрывается за этим кодом и как оно будет отображено на дисплее. Да, этот вопрос «позабористее» предыдущего будет, а заодно и отличный тест на понимание реально тонких нюансов SQL Server. Если хотите — поставьте чтение статьи на «паузу» и попытайтесь объяснить этот «феномен» самостоятельно, сравнив затем свое объяснение с точкой зрения автора по данному вопросу.

Так вот, что бы раскусить и этот «орешек» рассмотрим кусок кода (из одной строки) имеющий к нашему вопросу, на первый взгляд, очень косвенное отношение:

1
SELECT ASCII(CHAR(0xA7) COLLATE SQL_Latin1_General_CP850_BIN)

Все предельно просто: сначала байт A7 в символ, а затем его же обратно. Что, по вашему, мы будем наблюдать в резалт-сете? A7? Ответ неверный. Не A7? Ответ снова неверный. :) А вот верный: «недостаточно информации для ответа». А именно — мы не знаем номер кодовой страницы коллейшена той базы данных, в контексте которой выполняется указанная строка кода. А это, представьте себе, важно! Например запустив эту строку подключившись к базе с коллейшеном SQL_Scandinavian_CP850_CS_AS мы получим ожидаемый A7. А сменив коллейшен на Cyrillic_General_CI_AS и выполнив тоже самое мы получим совсем не ожидаемый F5. А если еще «пошаманить» с коллейшенами мы получим третий результат, четвертый и так пока не надоест. При этом сам код не меняется ни на запятую! Нет, сервер вовсе не надумал играть с нами в лотерею, отнюдь! :) Все математически обосновано и давайте посмотрим на это обоснование при запуске кода «под базой» с коллейшеном Cyrillic_General_CI_AS, откуда ж там F5 берется? Пошаговый процесс (на логическом, разумеется, уровне) разбора команды выглядит примерно так:

  • сначала сервер видит, что предстоит работа с символом с кодом A7 и что символ будет не юникодный, т.к. CHAR() возвращает строго char(1);
  • сервер берет таблицу для CP1251 (Cyrillic) (кстати, проверьте себя — почему в нашей задаче сервер берет именно эту и никакую иную таблицу?) и находит там указанный символ, а именно символ с кодом A7. Да, шаг выглядит загадочно... Что «забыл» сервер в указанной таблице?? Любопытно стало взглянуть на внешний вид символа? И дальше что? Отрисовка символов по любому вопрос только клиентского приложения, серверу что за забота на них «смотреть»? Что он надеется еще там обнаружить кроме той информации что и так у него «на руках»?
  • но резон для таких «странностей» таки есть! Посмотрите и вы на эту же таблицу по ссылке чуть выше. Видите, в квадратике с нашим символом (а им оказался всего лишь знак параграфа), внизу, подпись — 00A7? Так это не ASCII-код еще раз продублирован, это — кодовая точка юникод, так же известная как unicode code point. Вы, должно быть, часто встречали записи вида U+<четыре_цифры> обозначающее ничто иное как уникальное значение, присвоенное каждому символу в наборе символов кодовой таблицы Unicode. А проще говоря, это как ASCII, но только в «гигантской» юникод-таблице. И тут уже никаких «подвижек» в связи со сменой кодовых страниц быть не может в принципе, каждый символ «прибит» к своей ячейке намертво — не оторвать! И подпись, на которую вы смотрите, это именно U+00A7, а не 0xA7. Хотя в данный момент наш символ имеет и тот код, и этот;
  • проанализировав все это движок помечает у себя «внутри» символ как 0xA7/U+00A7 и переходит к следующему шагу;
  • теперь он видит приказ, который в переводе можно интерпретировать так: воспринимать указанный символ как принадлежащий кодовой странице CP850 (Multilingual Latin I). Исполнительный движок берет «в зубы» и эту таблицу и ожидает следующего и финального приказа...
  • ...отыскать и вернуть его, символа, ASCII (других-то у не юникод символа нет!) код. Движок прилежно «сканит» вторую таблицу. Вот только ищет он там не 0xA7, как полагают многие, а U+00A7!! И находит его на позиции... разумеется, F5. О чем исправно сообщает возвратом его десятичного представления 245.

Т.е. в середину работы полностью не юникодной команды «врубается» юникод, «переставляет байтики», и галантно удаляется перед ее завершением. «Какой юникод, вы о чем»?

К каким еще последствиям приводит сей изящный «финт», помимо клина левого полушария мозга у администраторов/разработчиков пытающихся анализировать его последствия? К тому, что при любой (и лингвистической, и числовой) сортировке не-юникодной колонки результат будет зависеть сразу от двух коллейшенов:

  • коллейшен этой самой колонки (или ее базы, если у колонки нет персонального коллейшена)
  • коллейшен указанный в команде извлекающей данные, в клаузуле ORDER BY

Почему при любой сортировке? Потому что указанный процесс (ASCII-код → кодовая точка юникод → ASCII-код) случается всегда, когда кодовые страницы указанных коллейшенов «вступают в спор». Собственно, я более чем уверен, что для единообразия процесс повторяется и тогда, когда никакого «конфликта страниц» нет, просто в этом случае прогон получается «пустым» и на выходе мы имеем тот же ASCII-код что и на входе, по понятным причинам. И лишь выяснив «правильный» ASCII-код движок применит к нему или «смысловое» правило (лингвистический алгоритм), или правило «двоичное» (числовой алгоритм). Если же второй, «принудительный», коллейшен отсутствует, то всем заправляет коллейшен первый, которого не может не быть. И вот тут мы приходим к ответу на вторую загадку: множественные BIN-ы нам нужны потому, что имея колонку с коллейшеном SQL_Latin1_General_CP850_BIN мы, вдруг, можем захотеть отсортировать ее по числовому алгоритму кодовой страницы Cyrillic_General_BIN. А при внешней похожести алгоритмов обоих коллейшенов (да что там похожести — при 100% идентичности) результат будет все-таки разный, потому что некоторые символы (знак параграфа тому пример) имеют разный ASCII-код в этих двух страницах, что «детектится» движком при посредничестве кодовых точек юникода.

Автор честно пытался и хотел сделать хоть эту часть простой и компактной, о чем сразу заявил в ведении к ней. Но коллейшены — это такие коллейшены... О них можно говорить бесконечно. В общем, автор, предоставив более чем солидный объем материала для размышлений своим читателям, ставит принудительную точку хотя бы в этом под-разделе и переходит к вопросу имен коллейшенов второй группы — Windows.

Имена коллейшенов группы Windows.

Здесь, как сразу хочет отметить автор, порядка куда как больше, чем с именами коллейшенов предыдущей группы. То есть формальный синтаксис соблюден практически идеально и очень строго. Отчасти, схема формирования имен коллейшенов этой группы перекликается со схемой группы предыдущей, так что не забывайте поглядывать и туда тоже. Итак, вновь отбрасывая совсем уж второстепенные детали не шибко актуальные для русской/английской письменности и языка, можно сказать, что формальный синтаксис разбираемых имен будет:

базис_версия_суффикс1_суффикс2

где:

  • базис — это теперь и алфавит (основа в терминах предыдущей группы), и кодовая страница (страница в терминах предыдущей группы). Страница более не выделяется собственным префиксом CP, а является неотъемлемой частью конкретного базиса. Для юникодных символов страница, понятно, не актуальна, но отброшена она быть не может, поскольку, повторю, любой данный базис автоматически подразумевает соответствующую страницу. Некоторые языки по прежнему сгоняются в этакие «табунчики» типа уже знакомого нам Latin1_General, но таблица имен коллейшенов этой группы стала куда как прозрачнее. Вы просто поиском находите нужный даже не язык, а диалект языка (вроде Spanish (Mexico) — мексиканский испанский) и вуаля, ваш базис Modern_Spanish_100 или Modern_Spanish на ваш выбор. Только обратите внимание, в отличии от таблицы предыдущей группы эта — не содержит полных имен коллейшенов, она предлагает вам именно и только список базисов. Дополнить их опциональными суффиксами и сформировать валидное имя коллейшена — ваша задача. Предвижу так же вопрос: «а где тут у нас, в базисе, номер кодовой страницы, собственно»? Это да, номер теперь не выделен явно, а просто подразумевается как наиболее подходящий для данного языка. Ничего сложного, тем более что для данных коллейшенов могут использоваться только Windows-же code page, а их, собственно, очень немного. Просто исходите из здравого смысла: если язык греческий — какая страница? Ну уж не 1251, это наш родной Cyrillic, а как раз 1253 будет в этом случае. Польский/чешский? 1250, как и подавляющее большинство языков центрально-восточной Европы. Английский? 1252, как и почти все западно-европейские языки, и т.д. На совсем уж худой конец просто дайте серверу команду:
    1
    SELECT collationproperty('[полное_имя_коллейшена]', 'CodePage');
    Вы получите ячейку содержащую номер кодовой страницы приписанный данному коллейшену. Кстати, забавно, но на момент написания статьи авторы BOL «обидели» свой родной «английский из США», проставив ему в упомянутой таблице коллейшен SQL_Latin1_General_CP1. Это не только неверный коллейшен для данного языка, это коллейшен вообще другой группы (префикс SQL_ замечаете?), который в рассматриваемой таблице просто не может появиться. А ведь автор всегда говорил: «копи-паст — зло!». :) Правильные коллейшены для данного языка (точнее их базисы) — Latin1_General_100/Latin1_General, как и для всех прочих языков английской группы;
  • версия — это опциональная часть могущая следовать за базисом (но могущая и отсутствовать). Означает и показывает с какой версии SQL Server такой коллейшен существует. Возможны лишь 2 значения этого компонента имени: 90 — коллейшен появился в 2005-й версии и 100 — коллейшен появился в 2008-й версии. Пропуск данного компонента показывает что коллейшен существует с 2000-го сервера. Считается, что коллейшены соответствующей «серии» более «заточены» под соответствующие же выпуски операционных систем, 90-е — под Windows Server 2003, 100-е — Windows Server 2008. Ведь как станет ясно из дальнейшего изложения коллейшены этой группы работают в очень плотной связке с той операционной системой что «хостит» наш сервер. Точные технические отличия одних и тех же коллейшенов разных версий автору неизвестны, однако алгоритм отбора нужного — ясен как день: всегда используйте самую свежую версию коллейшена, не имея каких либо весомых оснований делать обратное. Таковыми можно считать необходимость обратной совместимости с существующим приложением. Или, допустим, ваш 2008-й сервер работает с удаленным (linked) сервером 2005-й/2000-й версии. Тогда да — тут нужно аккуратно взвесить ситуацию. Ну а в общем случае, повторю, 100 — наш выбор!
  • оба суффикса — полностью аналогичны таковым из схемы имен предыдущей группы, где и были объяснены достаточно подробно;
  • и снова — особый случай. Как и в предыдущей группе оба суффикса (и только вместе) можно заменить так же уже вам знакомым литералом BIN. Однако теперь есть и альтернатива, вместо обоих суффиксов можно поставить и BIN2. Да, я уже слышу: «в чем разница»!? И конечно же, она будет рассмотрена всенепременно и подробно, но уже в следующем разделе, где такой анализ будет более уместен.

Пока же вот несколько примеров полных имен коллейшенов данной группы с анализом их компонентов:

  • French_100_CI_AS — набор правил сортировки для французского языка, кодовая страница 1252, регистр букв игнорируется, диакритические знаки учитываются. Коллейшен оптимизирован для версии сервера 2008 и не может применяться на более ранних.
  • Macedonian_FYROM_90_CS_AS — набор правил сортировки для македонского языка, кодовая страница 1251, регистр букв и диакритические знаки учитываются. Коллейшен оптимизирован для версии сервера 2005 и не может применяться на более ранних.
  • Czech_CI_AI — набор правил сортировки для чешского языка, кодовая страница 1250, регистр букв и диакритические знаки игнорируются. Коллейшен может применяться на любой версии сервера.
  • Finnish_Swedish_BIN — числовой алгоритм сортировки для финского и шведского языков, кодовая страница 1252. Коллейшен может применяться на любой версии сервера.
  • Finnish_Swedish_100_BIN2 — как и предыдущий, но алгоритм альтернативный (2-й версии). Сам коллейшен оптимизирован для версии сервера 2008 и не может применяться на более ранних.

Напомню, что командой вида

1
SELECT * FROM fn_helpcollations() WHERE name LIKE 'Fin%'

вы можете получить все полные имена коллейшенов в принципе известных серверу и имеющих отношение к, например, финскому языку.