A-символы и BIN-коллейшены.
При описании схемы имен коллейшенов группы Windows в позапрошлой, 5-й части статьи автор обещал подробно осветить разницу в числовых алгоритмах сортировки BIN и BIN2. Для системного подхода к этому вопросу сначала определимся с «претендентами» на изучение. Во-первых, не исключено что алгоритмы работают по разному на A- и N-символах. Во-вторых, у нас есть BIN-SQL-коллейшен алгоритм, BIN-Windows-коллейшен алгоритм, и BIN2-Windows-коллейшен алгоритм. Итого получаем 6 возможных сочетаний типов символов и типов алгоритмов. Для начала давайте «прогоним» все 3 BIN-алгоритма для A-символов. Наш тест-код:
1 2 3 4 5 6 7 | SELECT str, convert(binary(3), str) AS bytestr, row_number() OVER(ORDER BY convert(varbinary, str)) AS rawBytes, row_number() OVER(ORDER BY str COLLATE Cyrillic_General_BIN) AS collate_SQL_BIN, row_number() OVER(ORDER BY str COLLATE Cyrillic_General_100_BIN) AS collate_Win_BIN, row_number() OVER(ORDER BY str COLLATE Cyrillic_General_100_BIN2) AS collate_Win_BIN2 FROM (VALUES ('б!г'), ('!гZ'), ('Zв!'), ('!Zг')) AS T(str) ORDER BY rawBytes |
Для каждой из 4-х трехбуквенных цепочек мы выводим:
- саму эту цепочку как набор букв
- ее внутреннее представление, «сырые» байты
- распределение мест если проводить сортировку именно по этим «сырым» байтам; т.е. проводить сопоставление «байт-в-байт» не заморачиваясь никакими юникодами и порядками следования этих байт
- и, завершающие 3 колонки, распределение мест при 3-х возможных BIN-алгоритмах
Для выяснения порядка следования строк мы применяем ранжирующую функцию row_number, задав ей в качестве «базы для нумерации» требуемую в каждом случае сортировку. Запускаем:
!Zг 0x215AE3 1 1 1 1
!гZ 0x21E35A 2 2 2 2
Zв! 0x5AE221 3 3 3 3
б!г 0xE121E3 4 4 4 4
Что наблюдаем? Да в общем-то предсказуемую картину, можно сказать — так оно и мыслилось. Пишем себе заключение:
N-символы и BIN-коллейшены.
Теперь тот же эксперимент с юникодом. Код практически не изменился, лишь добавился префикс N для всех 4-х строк и размер «сырых» байт предсказуемо возрос с трех до шести. Итого:
1 2 3 4 5 6 7 | SELECT str, convert(binary(6), str) AS bytestr, row_number() OVER(ORDER BY convert(varbinary, str)) AS rawBytes, row_number() OVER(ORDER BY str COLLATE Cyrillic_General_BIN) AS collate_SQL_BIN, row_number() OVER(ORDER BY str COLLATE Cyrillic_General_100_BIN) AS collate_Win_BIN, row_number() OVER(ORDER BY str COLLATE Cyrillic_General_100_BIN2) AS collate_Win_BIN2 FROM (VALUES (N'б!г'), (N'!гZ'), (N'Zв!'), (N'!Zг')) AS T(str) ORDER BY rawBytes |
Запуск:
!гZ 0x210033045A00 1 1 1 2
!Zг 0x21005A003304 2 2 2 1
б!г 0x310421003304 3 4 4 4
Zв! 0x5A0032042100 4 3 3 3
Так, тут уже, кажется, интереснее... Ну, то что «лажает» «сырая» сортировка ставя русские буквы перед латинскими это ожидаемо, откуда ей-то про все эти little-endian знать? Как сравнивала «байт-в-байт» так и продолжает. Забавно ведут себя оба BIN (без 2) алгоритма. При сопоставлении 'б!г' vs. 'Zв!' все верно, латинская буква выносится вперед. А вот '!гZ' vs. '!Zг' — ошибка при сличении второго символа, наша г оказывается впереди. Как это им удается «ошибаться местами»? Не будем томить общественность: любой BIN (но не BIN2!) алгоритм при сличении юникод цепочек воспринимает как юникодный только первый символ цепочки. И для него делает необходимую для корректного вывода по вопросу «больше/меньше» «рокировку» двух байт. А для всех прочих — нет. Для них идет обычное сравнение «байт-в-байт». На резонный вопрос «это кто ж такой бред придумал?» автор может лишь констатировать, что придуман он был давным-давно, замечен, предполагаю, когда уже поздно было (хотя, может тогда так «модно» было? первый символ типа WCHAR а потом обычные... не знаю, полагаю что просто баг), ну а дальше понятно — backward compatibility, так ее. 
И вот примерно таким макаром весь этот бред сохранился аж до самой последней версии сервера. И более того, судя по всему, имеет все шансы выжить еще пару-другую релизов, прежде чем его «прихлопнут» окончательно. Будем утешаться отсутствием претензий к алгоритму BIN2: каждая пара байт аккуратно разворачивается и лишь затем сопоставляется такой же развернутой паре. Результат сортировки — в точности согласно кодовым точкам каждого юникод символа. Пишем второе заключение:
Семантика сортировки коллейшенов двух групп.
Наконец, руки доходят и до сути текущего раздела. Зачем же нам, в конце-то концов, нужны коллейшены разных групп? Ну с числовой сортировкой, допустим, разобрались. По сути там Windows-коллейшены благодаря наличию у них как алгоритма старого (BIN), так и нового (BIN2) покрываю все возможные варианты и делают SQL-коллейшены просто не нужными (опять же — мы не берем в расчет существующие приложения и проблемы обратной совместимости). А что с лингвистическими алгоритмами? Хоть тут-то группа SQL-коллейшенов способна предложить что дельное? Ну — скажем так: предложить способна однозначно, а вот что касается «дельности» этого предложения...
Ситуация у нас следующая. Все лингвистические алгоритмы базируются на т.н. наборах правил сопоставлений. Собственно слово коллейшен просто короткий вариант названия той же сущности. Именно эти правила диктуют: вот эта буква «больше» чем та (вспоминайте, кстати, про вес символов), эту при сравнении следует игнорировать, а вот эту следует считать за два вот этих символа и т.д. И правил таких не одно, а множество (для простоты, хоть формально это и не так, считайте один язык — одно правило). Но и само множество не одно! А два: SQL-набор правил и Windows-набор правил. Да, вы уже ухватили аналогию, я вижу. Эти два множества и формируют два ядра функциональности сравнения строк двух групп коллейшенов. Что можно сказать о первом множестве, т.е. SQL-наборе? То что это в основном устаревшие, очень примитивные правила стоящие практически вплотную к обычной BIN-сортировке. Т.е. в большинстве случаев результаты лингвистического сопоставления по SQL-правилам приводят к тем же результатам, как если бы вы просто упорядочили колонку по байтам тех слов, что она содержит, т.е. провели обычную BIN-сортировку. Нет, кое-какие отличия есть, иначе бы вообще не стоило и «огород городить», но поискать такие отличия еще днем с огнем придется. Совсем другие песни будут у нас в случае Windows-набора. Тут уже правила заметно отличаются от «лобовой» BIN-сортировки и гораздо ближе к сортировке «ручной», т.е. как бы упорядочил строки живой человек, посади мы его на эту работу. Конечно, значение буквы (ее код в таблице) все еще остается основополагающим фактором сортировки и в этом наборе правил, но теперь это уже не фактор решающий ее результат на 99.9% Легко обнаруживаются ситуации, когда «по таблице» строка должна быть внизу, а она наверху. И наоборот. В общем, как вы уже поняли, Windows-набор — «это круто». Еще лучше то, что наборы правил этой (и только этой!) группы обновляются, модернизируются и улучшаются с выходом каждой новой версии... нет, не SQL Server. А как раз-таки OS Windows. Да, это ее, «операционки» правила! Их тождественность там и тут имеют еще один приятный побочный эффект: если не SQL-программист пишет приложение хранящее информацию где угодно (да хоть в файле) и потом сортирует эту информацию силами самого приложения (например, обращаясь к Win32 функции CompareString), он получает абсолютно тождественный результат как если бы та же информация была «впихнута» в колонку таблицы и отсортирована там силами сервера. Конечно, этот интересный факт не кажется прям-таки «советом дня» и не сразу сообразишь когда он может пригодиться, но не вызывает сомнения факт другой, прямо вытекающий из первого: чем меньше «зоопарка» в работе двух взаимосвязанных систем — тем лучше. Даже если это «лучше» не проявляется мгновенно. И еще учтите, все вышесказанное не означает что при каждой необходимости соотнести два слова/символа SQL Server «бежит» к Windows и начинает ей «объяснять»: «а вот, у меня тут,... как считаешь?». Нет, сервер имеет свою копию тех же самых правил и действует полностью самостоятельно. Отсюда вытекает и простое следствие: хотя новые правила появляются сначала в OS, мы, работающие на сервере, не сможем получить новые их «плюшки», пока не выйдет новый релиз сервера в который эти правила будут скопированы и вставлены. Правда, с учетом того, что в последние годы то и это релизится практически одновременно, следствие носит скорее характер теоретический. На практике новые правила доступны почти сразу если вы, конечно, озаботитесь апгрейдом своего сервера (хинт: еще одна причина не тянуть эту резину года 3-4).
Так, теперь вопрос: и когда же применяются правила того набора, а когда этого? Кажется ответ очевиден: каков коллейшен — таков и набор (точнее его группа), нет? Нет! 
Правильный ответ: каков тип строкового выражения (A- или N-) и может быть коллейшена — таков и набор. Да, как ни странно, определяющим является тип, а не коллейшен. Кратко — правила выбора группы набора:
- если тип выражения/колонки N — применяются правила Windows-набора, точка;
- если тип выражения/колонки A, но указанный ей коллейшен принадлежит группе Windows — снова применяются правила Windows-набора;
- и только если тип выражения/колонки A, и плюс указанный ей коллейшен принадлежит группе SQL — применяются правила SQL-набора;
Таким образом, вытащить из «пыли веков» тот или иной набор группы SQL не так и легко, как может показаться. Но пока еще возможно. Можно ли увидеть применение перечисленных трех правил «вживую» и, тем самым, подтвердить их правильность? Легко. Сначала SQL-коллейшены и оба типа строк:
1 2 3 4 5 | --SQL коллейшен SELECT Col1 FROM (VALUES('New-York'), ('NewYork'), ('New-Zel')) AS T(Col1) ORDER BY Col1 COLLATE SQL_Latin1_General_Cp1_CI_AI SELECT Col1 FROM (VALUES(N'New-York'), (N'NewYork'), (N'New-Zel')) AS T(Col1) ORDER BY Col1 COLLATE SQL_Latin1_General_Cp1_CI_AI |
Первый резал-сет:
New-York
New-Zel
NewYork
Второй резал-сет:
NewYork
New-York
New-Zel
Не будем пока «вчитываться» в результаты, просто констатируем — они различны. Первая половина правил подтверждена. Теперь Windows-коллейшены с различными типами строк:
1 2 3 4 5 | --Windows коллейшены SELECT Col1 FROM (VALUES('New-York'), ('NewYork'), ('New-Zel')) AS T(Col1) ORDER BY Col1 COLLATE Latin1_General_100_CI_AS SELECT Col1 FROM (VALUES(N'New-York'), (N'NewYork'), (N'New-Zel')) AS T(Col1) ORDER BY Col1 COLLATE Latin1_General_100_CI_AS |
Третий резал-сет:
NewYork
New-York
New-Zel
Четвертый резал-сет:
NewYork
New-York
New-Zel
Как легко видеть из всех четырех резалт-сетов не совпал только самый первый, при сочетании A-символы+SQL-коллейшен. Правила подтверждены. Теперь — «что лучше»? Ну что лучше — когда NewYork и New-York стоят рядом или когда они «размазаны» по резалт-сету, сами-то как считаете? Вот и я думаю что надо Windows-наборов держаться. И, как вы увидите далее, есть намного больше чем одна причина держаться именно этой группы коллейшенов всегда, вне зависимости от типа (A- или N-) выражения или колонки.
Еще один момент, который мог вас удивить. Что бы получить второй резалт-сет из четырех мы юникодовским колонкам «пристегнули» коллейшен из SQL группы! На первый взгляд не понятно, как он вообще «разрулил» ситуацию? Ведь группа коллейшенов SQL это, кажется, кодовые страницы, ASCII и все такое? А тут мало того, что все символы верно интерпретированы, так еще правило их сортировки применено именно из группы коллейшенов Windows! Это на самом деле потому, что в реальности работал коллейшен как раз из последней группы. Вот как это случается. Каждому SQL коллейшену поставлен в соответствие наиболее близкий ему по смыслу его Windows-«коллега». И первый «знает» второго. Если вы достаточно любопытны, то можете так же это увидеть, хотя в большинстве случаев все совершенно очевидно. Но, что бы убедиться:
1 | SELECT * FROM fn_helpcollations() WHERE name=[полное_имя_коллейшена_SQL_группы] |
Для примера:
1 | SELECT * FROM fn_helpcollations() WHERE name='SQL_Icelandic_Pref_Cp1_CI_AS' |
И смотрим в резалт-сете на вторую колонку, description. Вот как она будет начинаться:
Первое же слово в этой колонке сообщает нам базис имени коллейшена из противоположной группы, а их, как вы знаете, содержит таблица имен коллейшенов группы Windows. Посмотрев в нее мы обнаруживаем, что базис имени будет Icelandic_100_ /Icelandic_, в зависимости от версии сервера. SQL-коллейшену осталось проанализировать собственные суффиксы, сформировать из них и базиса полное имя коллейшена противоположной группы, отдать «бразды правления» ему и полностью самоустраниться. Собственно, описанная процедура случается автоматически и каждый раз (и наше желание никто не спрашивает) когда SQL-коллейшен пытается работать с строковыми данными N-типа. И именно поэтому правило «юникод? — только группа Windows!» выполняется всегда и неукоснительно.