SqlCmd все о SQL технологиях.





Все что необходимо для изучения и работы с СУБД Microsoft SQL Server, MySQL, MariaDB, MongoDB. Авторские статьи, библиотека фрагментов T-SQL кода, сборник полезных инструментов.



MS SQL Server, SQL Server, T-SQL, исходники, сниппеты, статьи, учебники SQL, утилиты SQL, инструменты SQL



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

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





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, задав ей в качестве «базы для нумерации» требуемую в каждом случае сортировку. Запускаем:

str     bytestr   rawBytes  collate_SQL_BIN collate_Win_BIN collate_Win_BIN2
!Zг    0x215AE3    1           1               1               1
!гZ    0x21E35A    2           2               2               2
Zв!    0x5AE221    3           3               3               3
б!г    0xE121E3    4           4               4               4

Что наблюдаем? Да в общем-то предсказуемую картину, можно сказать — так оно и мыслилось. Пишем себе заключение:

Для не-юникод символов все BIN/BIN2-коллейшены работают абсолютно идентично и сводятся к прямолинейному сопоставлению «байт-в-байт». Однако и в этом случае (случае A-символов), и даже можно сказать особенно в этом, помните — предыдущий вывод вовсе не означает что для A-символов вы можете применять абсолютно любой BIN-коллейшен с идентичным результатом. Отнюдь. Скажем так: можно использовать любой BIN-коллейшен из набора разделяющих одну и ту же кодовую страницу. А лучше просто применять BIN-коллейшен «языка колонки».
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

Запуск:

str      bytestr      rawBytes  collate_SQL_BIN collate_Win_BIN collate_Win_BIN2
!г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, так ее. :x И вот примерно таким макаром весь этот бред сохранился аж до самой последней версии сервера. И более того, судя по всему, имеет все шансы выжить еще пару-другую релизов, прежде чем его «прихлопнут» окончательно. Будем утешаться отсутствием претензий к алгоритму BIN2: каждая пара байт аккуратно разворачивается и лишь затем сопоставляется такой же развернутой паре. Результат сортировки — в точности согласно кодовым точкам каждого юникод символа. Пишем второе заключение:

При необходимости выполнить числовое сопоставление N-символов следует пользоваться только и исключительно BIN2 коллейшенами. Зато вот здесь можно пользоваться любым BIN2 коллейшеном. Т.е. применив, допустим, Uzbek_Latin_90_BIN2 к колонке содержащей русские, греческие и ивритские буквы вы получите совершенно корректный результат. Другое дело, что просто с точки зрения здравого смысла будет неплохо если имя коллейшена «намекнет» читающему T-SQL код о предполагаемой языковой принадлежности строк той или иной колонки. Однако технически у вас действительно руки полностью развязаны, можете выбирать что больше нравится.
Семантика сортировки коллейшенов двух групп.

Наконец, руки доходят и до сути текущего раздела. Зачем же нам, в конце-то концов, нужны коллейшены разных групп? Ну с числовой сортировкой, допустим, разобрались. По сути там 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

Первый резал-сет:

Col1
New-York
New-Zel
NewYork

Второй резал-сет:

Col1
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

Третий резал-сет:

Col1
NewYork
New-York
New-Zel

Четвертый резал-сет:

Col1
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. Вот как она будет начинаться:

Icelandic, case-insensitive, accent...

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