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

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

















Группы коллейшенов.

Итак, как вы уже прекрасно знаете, в нашем распоряжении коллейшены двух больших групп: SQL и Windows. Коллейшены этих групп отличаются уже даже на этапе составления их полных имен, и значительно отличаются по области применения и своему предназначению. Любой администратор/разработчик обязан четко представлять ситуации подходящие для коллейшенов как первой группы, так и второй. И уметь обосновать применение коллейшена выбранной группы. Коллейшены обеих групп отчасти (но далеко не полностью) перекрываются когда речь идет об особом виде сопоставления — двоичном, то есть тот самый числовой алгоритм. Про суффиксы BIN/BIN2 не забыли? Так вот по сути такой алгоритм выделяется в собственную подгруппу или даже две: SQL-BIN и Windows-BIN(2). Коллейшены этих «искусственных» подгрупп тоже следует применять взвешенно и продуманно, четко выделяя подходящие для них ситуации. Обо всем этом пойдет речь в данной, заключительной части статьи.

Как сохраняются строковые данные.

Начнем мы, однако, немного издалека и рассмотрим основополагающие моменты связанные с логическим и физическим хранением строк различной разновидности. Как было уже не раз упомянуто в ходе статьи, коллейшены в принципе оказывают влияние на данные 6-ти типов: char/nchar, varchar/nvarchar, text/ntext. С учетом того, что последняя «парочка» доживает свой, быть может, последний релиз сервера, и очень скоро будет полностью заменена max-разновидностями средней пары — число наших (и коллейшеновских, конечно) «клиентов» сокращается до четырех. Это, собственно, потому, что сервер умеет хранить строки/символы тут и только тут. Для всего нашего последующего разговора указанное число можно сократить еще вдвое, поскольку длинна типов (фиксированная/переменная) не будет играть в нашем повествовании сколько-нибудь заметной роли. Итого, постановляем: сервер хранит строковые данные или в обычных однобайтовых символах (к коим мы привыкли со времен ASCII и кодовых страниц), или в «необычных» :) двубайтовых символах «глобальной» таблицы Юникод. Для краткости автор будет первые называть A-символами (потому что ASCII), а вторые N-символами (потому что Unicode и потому что с этого префикса должна начинаться любая символьная строка содержащая такие символы. Кстати, напомню, что N должна быть строго заглавной, например n'много €€€' — не «катит»).

Стало быть, A-символ сохраняется в единственном байте и без соответствующей кодовой страницы практически бесполезен — невозможно соотнести код (байт) с визуальным представлением. Страницы же задаются у нас где? Да, вы знаете это :) — в коллейшенах, прямо в их именах. N-символы независимы от какой-бы то ни было страницы, их визуальное представление однозначно вытекает из составляющих их двух байтов. И у многих почему-то складывается совершенно неверное представление о назначении коллейшенов двух групп. «SQL коллейшены — для A-символов, Windows коллейшены — для N-символов», говорят они. И жестоко ошибаются. Нет никаких, ни синтаксических, ни технических ограничений применения любого коллейшена к любому же символу. Докажем это:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
USE tempdb
GO
CREATE TABLE dbo.CollationAndCodePage
(
nouniType_uniColl char(1) COLLATE Finnish_Swedish_CI_AS,
nouniType_nouniColl char(1) COLLATE SQL_Latin1_General_Cp1251_CS_AS,
nouniType_uniColl_Greek char(1) COLLATE Greek_CI_AS,
nouniType_uniColl_Hebrew char(1) COLLATE Hebrew_CI_AS,
uniType_nouniColl1 nchar(1) COLLATE SQL_Latin1_General_Cp437_CI_AS,
uniType_nouniColl2 nchar(1) COLLATE Greek_CI_AS,
uniType_nouniColl3 nchar(1) COLLATE Hebrew_CI_AS
)
GO

INSERT INTO dbo.CollationAndCodePage
--греческий алфавит, буква СИГМА (ЮНИКОД!)
SELECT NCHAR(931),NCHAR(931),NCHAR(931),NCHAR(931),NCHAR(931),NCHAR(931),NCHAR(931)
UNION ALL
--алфавит иврита, буква АЛЕФ (ЮНИКОД!)
SELECT NCHAR(1488),NCHAR(1488),NCHAR(1488),NCHAR(1488),NCHAR(1488),NCHAR(1488),NCHAR(1488)

--вот что мы вставляли...
SELECT NCHAR(931),NCHAR(931),NCHAR(931),NCHAR(931),NCHAR(931),NCHAR(931),NCHAR(931)
UNION ALL
SELECT NCHAR(1488),NCHAR(1488),NCHAR(1488),NCHAR(1488),NCHAR(1488),NCHAR(1488),NCHAR(1488)
--...а вот что сохранилось
SELECT * FROM dbo.CollationAndCodePage
GO
DROP TABLE CollationAndCodePage
GO

Здесь, как легко видеть, у нас два представителя SQL-группы (имена обоих начинаются с SQL_) и пять представителей «конкурирующей группировки». Все они без каких либо проблем применяются к символам обоих типов. Таким образом из сочетания (2 типа символов)*(2 типа коллейшенов) рождается 4 возможные комбинации символ-коллейшен. Давайте на них посмотрим на примере резал-сета для кода приведенного выше:

Compare_Different_Collations

Итак, в эксперименте участвуют по одному представителю от греческого алфавита (буква сигма) и алфавита иврита (буква алеф). Обе буквы имеют характерное визуальное представление почему и были отобраны. Как легко видеть из кода все вставляемые символы — строго N-символы, но вот «приемники» для них (колонки) отличаются. Что означает N-символ «на входе»? То, что мы абстрагируемся от коллейшена той базы в контексте которой выполняется данный код. Движку сервера нет нужды анализировать кодовую страницу указанного коллейшена в поиске кодовой точке юникод, как это происходило, например, в случае подмены символа A7 символом F5 в предыдущей части. Но там у нас прямо на входе был А-символ, здесь же движок сервера получает требуемую кодовую точку юникод «в чистом виде». Прямо из T-SQL кода он понимает, что указанное значение для сигмы равно U+03A3, для алефU+05D0. В коде, как вы понимаете, те же шестнадцатеричные числа записаны в их десятичном представлении, только и всего. Так вот, на этапе анализа входной информации кодовая страница движку не нужна. Вообще никакая. Все меняется когда дело доходит до вставки тех же двух букв в колонки. Давайте анализировать:

  • колонка 1 — A-тип+Windows-коллейшен. Как поступает движок в этом случае? Очень просто, сначала он видит что тип приемника — A. Почему он сначала проверяет тип, и лишь потом обращает свое внимание на коллейшен? Потому что от типа хранилища зависят две крайне важные характеристики, точнее ответа на два вопроса:
    • нужно ли анализировать кодовую страницу целевого коллейшена (под целевым коллейшеном понимается коллейшен той колонки, что готова «принять» нашу строку/символ)?
    • и — нужно ли проводить процесс конвертации кодовой точки юникод в ASCII-код? Там же, в предыдущей части, процесс этот уже был разобран нами «по косточкам»
    В случае типа колонки A ответ на оба вопроса — «да», при типе N, понятно — «нет» для обоих. Так вот в данном случае кодовая страница нужна. Какая она будет? Ну как всем давно известно, финны, шведы (у нас же коллейшен Finnish_Swedish) и англичане всю жизнь пользовались одной и той же кодовой страницей, а именно 1252. OK, страница «на руках» — дальше что? Правильно, конвертация. Мы (вместе с движком) находим в ней кодовую точку U+03A3 (для сигмы) и выясняем что соответствующий ASCII код — какой? Правильно, никакого. Нету в данной кодовой странице такой кодовой точки! :cry: Да и странно было бы видеть в англо-ориентированной таблице совершенно греческую букву, пусть и довольно распространенную в математике. Что делать движку? Можно было б, конечно, остановить весь сервер по критической ошибке :) но движок у нас очень, очень умный. Он проводит резервный поиск по фонетическому соответствию. Вот скажите сами, для буквы сигма и с учетом что при взгляде на нее первое приходящее в голову слово это «сумма» — какая буква английского алфавита ее способна хоть сколько заменить? Да S, конечно! Вот она-то «вытягивается» из страницы и отправляется на хранение в колонку (смотрите первую ячейку второго из анализируемых резалт-сетов). А если б буква греческая была б не сигма, а фи? Сохранилась бы F. А для омеги? O, разумеется. Вы думали я иронизирую называя движок «умным»? Однако, понятно, не всесилен и он. Допустим еще одна греческая буква — омикрон. Вроде по фонетике ближайшая O, но та уже отдана под омегу... Включить фантазию и назначить на эту роль A? Да уж лучше ее под альфу оставить, разумеется. Ну в общем нет соответствия, ни прямого через кодовую точку, ни фонетического, хоть ты тресни! Что теперь? Вот тут придется движку «влепить» в хранилище формальный ? (знак вопроса) имеющий практически во всех кодовых страницах и в юникоде код 3F/63. Кстати, вопрос «я ему SELECT, а он мне знаки вопроса» повторяется на SQL-форумах регулярно, и теперь вы можете отвечать на него вполне квалифицированно. Ну или давать ссылку на этот текст. :) В общем, знак вопроса означает: движок очень старался найти в целевой кодовой странице символ такой же, или максимально близкий ему, как и символ «входящий». Но у него ничего не получилось. Да, фонетический поиск далек от идеала (а кто из нас к нему приблизился?) и местами откровенно «лажает». Скажем, то же сопоставление альфа=a. Для малой альфы движок без колебаний ставит на замену малую же a, но не может сообразить сделать такую же замену для альфы большой и заменяет ее знаком вопроса. Все мы не безгрешны, увы... Почему во второй строчке той же колонки из последнего резалт-сета алеф оказался заменен вопросом пояснять еще раз надо?
  • колонка 2 — A-тип+SQL-коллейшен. Снова движок сервера ставит перед собой те же два вопроса, и отвечает на них аналогично предыдущей колонке, только страница для поиска соответствия будет иная, 1251. Вообще-то, согласно исследованиям автора поиск фонетического соответствия проводится только для целевой страницы 1252. Для всех прочих страниц выполняется лишь «обычный» поиск, т.е. по кодовой точке юникода. Однако документального подтверждения этому своему наблюдению автор не обнаружил и посему относит его в категорию «неподтверждённых фактов». Как бы там ни было, но соответствия не было найдено ни для сигмы, ни для алефа. Получили в результате два знака вопроса.
  • колонки 3 и 4 — A-тип+Windows-коллейшены. Все аналогично второй колонке, но на этот раз целевые кодовые страницы подобраны таким образом, что «обычный» (по кодовой точке) поиск приводит к однократному успеху по разу в каждой колонке. В 3-ей колонке в кодовой странице обнаруживается сигма, в 4-й — алеф. Ну и по разу символов обнаружить не удается. Получаем знаки вопроса. Все предсказуемо.
  • оставшиеся колонки — N-тип+различные коллейшены. А вот тут уже движку сервера просто нет необходимости анализировать целевую кодовую страницу и проводить конвертацию входных символов. Входная пара байтов (на каждый символ) попросту помещается в хранилище-колонку, и это все. Результат перед вами. Таким образом мы «отвязались» (не абсолютно, конечно. В смысле управления порядком строк в колонке коллейшены продолжают работать) вообще от всех коллейшенов! Что на входе — то на выходе, прекрасно!!
Как физически хранятся N-символы.

Да, по два байта на символ, «потому что юникод», вы уже все это знаете. Однако, как известно, юникод (UTF-8) юникоду (UTF-16) рознь (UTF-32)! И нам очень даже не помешает разобраться — а в каком формате преобразования юникода (Unicode Transformation Format, UTF) из перечисленных хранятся наши с вами «два байта»? Это важно для главной цели данного раздела — понимания разницы между двумя группами коллейшенов. Замечу, что корни и причины приведшие к возникновению различных систем для кодирования одного и того же (а именно — потока N-символов) лежат полностью за рамками данной статьи и отвлекаться на них автор не собирается. Будем надеяться, что читатели и сами в курсе «как оно так все сложилось».

Так вот вопрос, на первый взгляд, несложен. Всезнающий BOL открытым текстом декларирует использование схемы кодирования UCS-2. Однако вот тут, автор согласен, требуется пояснения. Все же UCS-2 это не всем известные UTF-xx, тут возможно и недопонимание. Несложные поиски убеждают нас, что UCS-2, друзья мои, ничто иное как устаревший поднабор более новой кодировки (системы кодирования, более точно) UTF-16. Если вас не интересуют столь гига-экзотичные вещи как отображение иероглифов древнего Египта и прочих символов не влезших в диапазон 0000-FFFF (так называемая основная многоязычная матрица, она же Basic Multilingual Plane, она же BMP — не путать с картинками!) то для вас разницы нет, и мы с полным правом записываем: N-символы кодируются нашим сервером в UTF-16. So far — so good, как говорят наши коллеги за рубежом.

Преимущество выбранной сервером системы кодировки в чем? В ее крайней простоте. Один N-символ=2 байта, два N-символа=4 байта, три N-символа=6 байт, и т.д. Байты всегда следуют друг за другом не разделяясь какими-либо «служебными кодами». Т.е. абсолютно каждый байт в символьном массиве — или первый, или второй байт какого либо символа. НО! UTF-16 и сам разделяется на 2 «течения». Потому что символ с кодом 0x1122 можно сохранить (в колонку, в память, на диск — куда угодно) и как цепочку байт 11,22 и как цепочку 22,11. Т.е. байты можно переставить по сравнению с их порядком в самом символе, а можно этого не делать. Для нашего сервера порядок был предопределен еще задолго до его рождения, потому что решение было принято еще при дизайне всей процессорной архитектуры получившей позже кодовое наименование x86. Раз наш сервер работает на именно такой архитектуре (в x64 в этом плане ничего не изменилось) то ему ничего не остается как принять «правила игры» ею же предписываемые: переставлять! Итак, сервер сохранит наш символ именно как 22,11 — без вариантов. Формат «с перестановкой» называют порядок от младшего к старшему, т.к. сначала идет менее значащий байт, затем — более значащий. На языке оригинала тоже самое называют little-endian (LE), ну а ответвление системы UTF-16 взявшее на вооружение такой порядок называют UTF-16LE. Итого, немного исправим нашу предыдущую запись: N-символы кодируются нашим сервером в UTF-16, причем в порядке «менее значащий байт первый», что и обозначается как UTF-16LE.

Обладая знаниями этого небольшого раздела вы, имея под рукой юникод-таблицу, без проблем можете нарисовать на листе бумаги внутреннее представление любой цепочки символов. Но нельзя ли «попросить» сервер сделать тоже самое? Пусть он сам нам покажет как он в реальности «видит» строчку N-символов 'АБz'. Это несложно, достаточно привести строку к типу varbinary и готово — мы видим «сырую» цепочку байтов без какой-либо ее интерпретации сервером. Сначала построим такую цепочку сами, дабы серверу не получилось нас обмануть. :) С учетом того, что А у нас русская коды всех трех символов будут: U+0410, U+0411, U+007A. Ну а цепочка, в соответствии с правилом little-endian, будет 10,04,11,04,7A,00. Теперь проверяем себя и сервер:

1
SELECT convert(varbinary, N'АБz') AS [3Nchars];
3Nchars
0x100411047A00

Сошлось? ;)