Разница между различными бинарными коллациями (языки, версии, BIN против BIN2)
Пересказ статьи Solomon Rutzky. Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2)
Бинарные коллации во многом проще для понимания, чем небинарные, поскольку они не имеют сложностей, связанных с национальными лингвистическими правилами. Они просто работают со значениями символов и тут нет места для чудес: все символы имеют базовые значения; если два символа имеют одинаковое значение, то они равны, в противном случае не равны; и это значение определяет порядок, правильно?
И все же имеются некоторые сложности, связанные с бинарными коллациями, о которых вы, возможно, не знаете. Чтобы определить, что это за проблемы, нам сначала нужно выяснить, почему существует так много бинарных коллаций. Я имею в виду, что бинарные коллации работают на базовых значениях символов, и сравнение чисел не меняется при смене языков или версий: 12 = 12, 12 > 11 и 12 < 13 всегда. Тогда какая разница между:
Давайте подробней рассмотрим каждый из этих случаев.
Мы можем отметить, что как Latin1_General_100_BIN2, так и Hebrew_100_BIN2 имеют одинаковую коллацию “_100_BIN2”, а различие состоит только в “Latin1_General” против “Hebrew” (т.е. в языке). Для данных NVARCHAR это различие совершенно бессмысленно: поведение всех бинарных коллаций одной и той же версии и типа бинарного сравнения в точности совпадает.
Однако для данных VARCHAR имеется важное отличие. Все коллации имеют связанную с ними кодовую страницу, т.е. набор символов, используемый для 8-битных (т.е. VARCHAR) данных. Даже если это кодовая страница 0 (что означает отсутствие кодовой страницы), она все равно определяет, какие символы могут сохраняться в 8-битном типе данных. И в этом случае, это будет "никакие", вот почему эти коллации называются "только Юникод", и поэтому они не работают для данных VARCHAR:
Отсутствие кодовой страницы означает, что здесь нет 8-битного (т.е. VARCHAR) набора символов. Конечно, разница в языке будет более очевидной, если мы рассмотрим несколько ненулевых кодовых страниц:
Теперь мы внедрим одни и те же базовые значения в каждую из этих трех кодовых страниц. Пожалуйста, заметьте, что нам нужно использовать значения в диапазоне 128 - 255 (0х80 - 0хFF), поскольку диапазон 0 - 127 (0х00 - 0x7F) одинаков для всех кодовых страниц, которые могут быть представлены в SQL Server, включая данные Юникод (как UTF-16 для NVARCHAR, так и, начиная с SQL Server 2019, UTF-8 для VARCHAR).
Как показывают результаты, те же 2 байта - 0xE1 И 0xFA - дают совершенно разные символы из-за разницы кодовых страниц (кодовая страница 949 является двухбайтовым набором символов (DBCS), и поэтому только один символ возвращается для корейской коллации). То же справедливо для байтов 0хЕ4 и 0хЕ8. Это единственная причина иметь более одной коллации “_100_BIN2”. И вот почему Майкрософт могла бы предложить единственную коллацию UnicodeOnly_90_BIN2 и единственную коллацию UnicodeOnly_100_BIN2 вместо 15, которые имеются для обеих этих версий:
В обоих названиях Latin1_General_100_BIN2 и Latin1_General_BIN2 используются коллации “Latin1_General_*_BIN2”, которые отличаются только отсутствие номера, что подразумевает 80 в одном случае, и 100 (т.е. номер версии) - в другом. Для данных VARCHAR эта разница не имеет значения: поведение всех бинарных коллаций одного и того же языка одинаково.
Однако для данных Юникод (главным образом, NVARCHAR, но, начиная с версии SQL Server 2019 CTP2.3, также и VARCHAR, если используется новая коллация UTF8_BIN2) имеется важное различие. Все коллации отображают кодовые точки на различные свойства, такие как: веса сортировки (используется для сортировки и сопоставления), числовые значения для "чисел", версии нижнего и верхнего регистра, и т.д. Например:
Но именно эти сопоставления верхнего и нижнего регистров являются здесь важными. Всего несколько кодовых точек отсутствуют в этом отображении, но с каждой новой версией введенных коллаций все больше кодовых точек занимают эти места. Например, следующий пример показывает кодовую точку, которая отсутствует в отображении верхнего регистра в коллациях обоих версий - 80 и 90 (если она отсутствует в какой-либо конкретной версии, то будет отсутствовать и во во всех предыдущих версиях также - вот почему мне не потребовался столбец для коллации версии 80):
Следующий пример показывает кодовую точку, которая отсутствует в своём отображении верхнего регистра в коллациях версий 80, 90 и 100:
Хорошо, так сколько таких сопоставлений отсутствует в каждой версии? Я рад, что вы спросили.
Единственный способ, с помощью которого мы можем определить, какие сопоставления отсутствуют, это - посмотреть на различия между наиболее старой версией (наибольшее число пропущенных сопоставлений) и новейшей версией (наименьшее число пропущенных сопоставлений) коллаций. С точки зрения логики это означает, что, поскольку у нас есть 4 версии - 80, 90, 100 и 140 - данные версии 80 будут естественно "все отсутствовать", а данные версии 140 будут естественно "все присутствовать". Возможны дополнительные сопоставления, отсутствующие во всех версиях, но а) у нас нет способа определить это теперь, как это требует перекрестных ссылок на официальную Базу данных Символов Юников, что возможно, но не в данном случае, и б) не любые потенциально отсутствующие сопоставления релевантны вопросу "какая разница между существующими коллациями, доступными в SQL Server".
Нижеприведенный запрос находит все кодовые точки, которые возвращают тот же символ, который был использован с функцией LOWER, при использовании коллации версии 80, но возвращал отличный символ, когда тот же исходный символ использовался, но в коллации версии 140. Что касается коллаций версии 90 и 100, если функция LOWER возвращает символ, который отличается от того, который вводился, отображается символ в нижнем регистре, в противном случае будет возвращен NULL. В последней строке подводятся итоги. Поле для коллаций версии 80 включено только для того, чтобы ясно показать, что не существует отображения в нижнем регистре для этих символов.
Я включил случайную выборку 4 строк из результирующего набора, плюс суммарную строку. Последняя показывает, что имеется 305 символов, для которых отсутствует отображение в нижнем регистре в коллациях 80 версии. Коллации версии 90 не добавили отображений. Однако коллации версии 100 добавили 200 отображений, а в версии - еще 105 отображений, что составляет 305 отображений, отсутствующих в коллациях версий 80 и 90.
Мы еще не все сделали. Отображение нижнего регистра - это только одно направление. Нам нужно также включить отображение верхнего регистра. И, привлекая его, не следует предполагать, что отсутствующие отображения верхнего регистра есть просто версии в верхнем регистре символов, которые отсутствуют в их отображениях нижнего регистра. Каждая кодовая точка имеет отображение верхнего или нижнего регистра (или может иметь), и они полностью независимы, поэтому просто из того, что один вход был добавлен, не следует, что какое-то другое отображение было добавлено также, даже если кажется, что так должно быть.
Приведенный ниже запрос находит все кодовые точки, которые возвращают тот же самый символ, который использовался в функции UPPER при использовании коллации версии 80, но которые также возвращают отличный символ, когда вводился исходный символ, но использовалась коллация версии 140. Для коллаций версий 90 и 100, если функция UPPER возвращает символ, который отличается от вводимого, отображается символ в верхнем регистре, в противном случае - NULL. Последняя строка показывает итоговые результаты. Поле для коллаций версии 80 включены просто для того, чтобы ясно продемонстрировать отсутствие отображения в верхний регистр для этих символов.
Я включил случайную выборку 4 строк из результирующего набора, плюс суммарную строку. Последняя показывает, что имеется 344 символа, для которых отсутствует отображение в верхнем регистре в коллациях 80 версии. Коллации версии 90 не добавили отображений. Однако коллации версии 100 добавили 238 отображений, а в версии - еще 106 отображений, что составляет 344 отображений, отсутствующих в коллациях версий 80 и 90.
Соберем данные обоих запросов вместе, вот итоговые результаты:
Эти данные показывают почему:
1. если возможно, важно использовать самую последнюю доступную версию (версия 100 была введена в SQL Server 2008, а версия 140 - в SQL Server 2017)! Имейте в виду, что эти отображения имеют только одно отличие. Имеются также многие тысячи весов сортировки, отсутствующие в более ранних версиях (почти 20К отсутствующих в версии 80, 13К - в версии 90, и почти 5К в версии 100).
2. коллация UTF8_BIN2 является новой в CTP 2.3 SQL Server 2019 и лучше, чем не имеющая бинарной коллации UTF-8 (которая использовалась до CTP 2.3), однако, что касается коллации 80, в ней отсутствуют 649 отображений верхнего/нижнего регистра.
Судя по именам, мы видим, что Latin1_General_100_BIN2 и Latin1_General_100_BIN являются коллациями “Latin1_General_100”, а различие между ними заключается в “_BIN” против “_BIN2” (т.е. в типе бинарного сравнения). Для данных VARCHAR, не являющихся UTF-8, эта разница не имеет значения: поведение всех бинарных коллаций одного и того же языка аналогично.
Однако для данных Юникод (главным образом, NVARCHAR, но и VARCHAR, если используется новая коллация UTF8_BIN2, начиная с SQL Server 2019 CTP 2.3) имеется важное различие. При сравнении значений, поведение всех бинарных коллаций в точности одинаково. Поэтому то, что нас интересует, это сортировка.
Согласно документации Майкрософт Collation and Unicode Support:
В коллации BIN2 все символы сортируются согласно из кодовым точкам. В коллации BIN только первый символ сортируется в соответствии с кодовой точкой, а остальные символы сортируются согласно из байтовым значениям. (Поскольку платформа Intel предполагает архитектуру с порядком байтов от младшего к старшему, символы кодов Юникод всегда сохраняются побайтово.)
Это означает: поведение совпадает только для первого символа. Поэтому если вы тестируете различие между “_BIN” и “_BIN2” сортировкой списка единичных символов, то никогда не увидите разницы. Для того, чтобы увидеть разницу в поведении, необходимо отбросить первый символ. Следующий тест демонстрирует это выводя как поле с единичным символом, так и поле с символом-префиксом, который одинаков для обеих строк, погашая эффект первого символа при сортировке:
Поле “PrefixedBIN” показывает разницу, поскольку начиная с байта #3, коллация “_BIN” видит 0x25 и 0x13 (благодаря побайтовой кодировке от младшего байта к старшему), в то время как коллация “_BIN2” видит 0x12 и 0x21. Если это не ясно, возможно, следующая схема поможет. И помним о том, что мы работаем с "префиксным" значением, которое эффективно игнорирует первый символ, поскольку нет разницы в сортировке по позиции первого символа. Схема, приведенная ниже, показывает символ и его байты, начиная с символа с номером 2.
Следующий запрос показывает как обрабатываются дополнительные символы в коллациях “_BIN“, “_BIN2”, а также в небинарных коллациях версии 90 или выше:
Этот запрос возвращает следующие 3 строки:
Этот запрос предназначен для выявления двух связанных нюансов работы с UTF-16 (т.е. кодировка Юникод, используемая данными NVARCHAR): один относительно терминологии, а другой - поведения.
Глядя на столбцы с порядковыми номерами (шесть крайних справа столбцов), можно заметить следующее:
1. Оба - префиксный и непрефиксный столбцы для “_BIN2” сортируются в одном и том же порядке.
2. Непрефиксные символы и для “_BIN”, и для “_BIN2” сортируются в одном и том же порядке.
3. Непрефиксные и префиксные символы для “_BIN” не сортируются в одном и том же порядке.
Пока что все это поведение ожидаемо и согласуется с тем, что мы видели выше.
Однако небинарные столбцы коллации версии 90 (два крайних справа столбца) показывают для обоих порядок:
Эта разница в порядке сортировки выявляет техническую особенность, которая нечасто упоминается, вероятно, потому что по большей части отсюда извлекается небольшая практическая польза. Теперь вы уже знаете, что надлежащие значения, используемые в Юникод для однозначной идентификации каждого символа, называются "кодовыми точками". Кодовая точка может быть закодирована различными способами. Спецификация Юникод предлагает 5 вариантов кодировки: UTF-8, UTF-16 Big Endian, UTF-16 Little Endian, UTF-32 Big Endian и UTF-32 Little Endian (Я оставляю в стороне не-Endian вариации UTF-16 и UTF-32, поскольку они просто применяют Big Endian). UTF-8 кодирует каждую кодовую точку как отдельный объект, используя от 1 до 4 байтов. UTF-32 кодирует каждую кодовую точку как отдельный объект посредством 4 байтов.
Напротив, UTF-16 кодирует каждую кодовую точку как одно- или двухбайтовый объект. Для первых 65536 кодовых точек (общее число 2-байтовых комбинаций, 0х0000 - 0хFFFF) все сказанное остается справедливым. Но дело немного запутывается, когда речь идет о дополнительных символах (т.е. всех символах за пределами начальных 65536). Дополнительные символы, подобно всем остальным символам, имеют каждый уникальную кодовую точку. В UTF-8 и UTF-32 не существует различий, но в UTF-16 каждый дополнительный символ состоит из двух недополнительных символов (т.е. "суррогатных пар"). Тогда к чему относится термин "кодовая точка"? Относится ли она к реальному дополнительному символу, или же к каждому из двухбайтовых кусочков, которые составляют дополнительный символ? Это не может быть и тем, и другим. И не является.
В UTF-16 двухбайтовые объекты фактически известны как "кодовые единицы". И просто так случилось, что каждая их первых 65536 кодовых точек состоит из единственной кодовой единицы, имеющей то же самое значение (например, кодовая точка U+FB06 имеет кодовую единицу FB06). Дополнительные символы являются кодовыми точками, составленными их двух кодовых единиц (например, кодовая точка U+1F643 составлена из кодовых единиц D83D и DE43).
Заглянув обратно в документацию Майкрософт, процитированную в начале этого раздела, мы теперь можем увидеть, что утверждение "В коллации BIN2 все символы сортируются согласно их кодовым точкам" не является вполне точным. Конечно, для не-дополнительных символов это справедливо, однако случай с дополнительными символами показывает нам, что определение коллации BIN2 технически некорректно. Данные NVARCHAR в коллации BIN2 сортируются по кодовым единицам, а не по кодовым точкам.
Я несправедлив, или чрезмерно придирчив? Нет, поскольку запрос и результаты выше показывают, что имеется три способа сортировки этих значений, и невозможно объяснить это поведение без понимания разницы между кодовой единицей и кодовой точкой. Надеюсь, что схема ниже поможет визуализировать различие в этих трех методах сортировки. Эта схема показывает те же три символа в той же последовательности, что и результаты выше: BIN, потом BIN2 и затем небинарная версия 90 или новее. Но теперь я показываю, как алгоритм сортировки видел каждый символ, что объяснит, почему каждый подход делает что-то отличное.
Учитывая описание коллаций "_BIN2" как упорядочение по "кодовым точкам", которые фактически являются упорядочением по "кодовым единицам", какое будет правильным? Согласно следующей цитате из официального стандарта Юникод (т.е. спецификации) в Chapter 2: General Structure / Section 2.5: Encoding Forms (page 39):
Все три формы кодирования дают одни и те же результаты для сравнения бинарных строк или сортировке строк, только когда дело касается BMP-символов (в диапазоне U+0000..U+FFFF). Однако когда речь идет о дополнительных символах (в диапазоне U+10000..U+10FFFF), бинарный порядок UTF-16 не соответствует порядку кодовых точек Юникод.
Похоже, что это поведение верное, и документация нуждается в обновлении.
Начиная с SQL Server 2019 стало возможным использовать кодировку UTF-8.
Спецификация Юникод (на той же упоминаемой странице) гласит:
Бинарная сортировка строк UTF-8 дает тот же порядок, что и бинарная сортировка по кодовым точкам Юникод. Это очевидно тот же порядок, как для бинарной сортировки строк UTF-32.
Мы можем протестировать это следующим запросом:
который возвращает:
Столбцы NVARCHAR показывают, что мы имеем дело с одними и теми же тремя символами между столбцами UTF-8 и UTF-16, что порядок согласуется с тем, который мы наблюдали прежде для данных NVARCHAR, и что порядок отличается от порядка для столбца UTF-8. Теперь нам интересно поведение UTF-8, и которое может быть только обнаружено при работе с данными VARCHAR (и, конечно, при использовании коллации UTF8). Схема выше показывает, что, да, коллация UTF8_BIN2 (новая в SQL Server 2019 CTP2.3) действительно использует кодовую точку для упорядочивания.
- Latin1_General_100_BIN2 и Hebrew_100_BIN2 (только языки различны) или
- Latin1_General_100_BIN2 и Latin1_General_BIN2 (различаются только версии) или
- Latin1_General_100_BIN2 и Latin1_General_100_BIN (только тип бинарного сравнения различается).
Давайте подробней рассмотрим каждый из этих случаев.
Различные языки (Cultures)
Мы можем отметить, что как Latin1_General_100_BIN2, так и Hebrew_100_BIN2 имеют одинаковую коллацию “_100_BIN2”, а различие состоит только в “Latin1_General” против “Hebrew” (т.е. в языке). Для данных NVARCHAR это различие совершенно бессмысленно: поведение всех бинарных коллаций одной и той же версии и типа бинарного сравнения в точности совпадает.
Однако для данных VARCHAR имеется важное отличие. Все коллации имеют связанную с ними кодовую страницу, т.е. набор символов, используемый для 8-битных (т.е. VARCHAR) данных. Даже если это кодовая страница 0 (что означает отсутствие кодовой страницы), она все равно определяет, какие символы могут сохраняться в 8-битном типе данных. И в этом случае, это будет "никакие", вот почему эти коллации называются "только Юникод", и поэтому они не работают для данных VARCHAR:
SELECT COLLATIONPROPERTY(N'Syriac_100_BIN2', 'CodePage');
-- 0
SELECT 'Не сработает, поверьте мне;-)' COLLATE Syriac_100_BIN2;
/
Msg 459, Level 16, State 1, Line XXXXX
Коллация 'Syriac_100_BIN2' поддерживается только на типах данных Юникод и не может применяться к типам данных char, varchar или text.
/
Отсутствие кодовой страницы означает, что здесь нет 8-битного (т.е. VARCHAR) набора символов. Конечно, разница в языке будет более очевидной, если мы рассмотрим несколько ненулевых кодовых страниц:
-- Какая 8-битная кодовая страница используется для каждой из этих коллаций
SELECT COLLATIONPROPERTY(N'Latin1_General_100_BIN2', 'CodePage'),
COLLATIONPROPERTY(N'Hebrew_100_BIN2', 'CodePage'),
COLLATIONPROPERTY(N'Korean_100_BIN2', 'CodePage');
-- 1252 1255 949
Теперь мы внедрим одни и те же базовые значения в каждую из этих трех кодовых страниц. Пожалуйста, заметьте, что нам нужно использовать значения в диапазоне 128 - 255 (0х80 - 0хFF), поскольку диапазон 0 - 127 (0х00 - 0x7F) одинаков для всех кодовых страниц, которые могут быть представлены в SQL Server, включая данные Юникод (как UTF-16 для NVARCHAR, так и, начиная с SQL Server 2019, UTF-8 для VARCHAR).
DECLARE @Data TABLE
(
[CodePage-1252] VARCHAR(50) COLLATE Latin1_General_100_BIN2,
[CodePage-1255] VARCHAR(50) COLLATE Hebrew_100_BIN2,
[CodePage-949] VARCHAR(50) COLLATE Korean_100_BIN2
);
INSERT INTO @Data VALUES (0xE1FA, 0xE1FA, 0xE1FA);
INSERT INTO @Data VALUES (0xE4E8, 0xE4E8, 0xE4E8);
SELECT FROM @Data;
/
CodePage-1252 CodePage-1255 CodePage-949
áú בת 守
äè הט 怏
/
Как показывают результаты, те же 2 байта - 0xE1 И 0xFA - дают совершенно разные символы из-за разницы кодовых страниц (кодовая страница 949 является двухбайтовым набором символов (DBCS), и поэтому только один символ возвращается для корейской коллации). То же справедливо для байтов 0хЕ4 и 0хЕ8. Это единственная причина иметь более одной коллации “_100_BIN2”. И вот почему Майкрософт могла бы предложить единственную коллацию UnicodeOnly_90_BIN2 и единственную коллацию UnicodeOnly_100_BIN2 вместо 15, которые имеются для обеих этих версий:
SELECT ROW_NUMBER() OVER (
PARTITION BY CASE WHEN col.[name] LIKE N'%[_]90[_]%' THEN 1 ELSE 2 END
ORDER BY CASE WHEN col.[name] LIKE N'%[_]90[_]%' THEN 1 ELSE 2 END,
col.[name]
),
col.[name]
FROM sys.fn_helpcollations() col
WHERE col.[name] LIKE N'%[_]BIN2'
AND COLLATIONPROPERTY(col.[name], 'CodePage') = 0;
/
1 Divehi_90_BIN2
2 Indic_General_90_BIN2
3 Syriac_90_BIN2
1 Assamese_100_BIN2
2 Bengali_100_BIN2
3 Divehi_100_BIN2
4 Indic_General_100_BIN2
5 Khmer_100_BIN2
6 Lao_100_BIN2
7 Maltese_100_BIN2
8 Maori_100_BIN2
9 Nepali_100_BIN2
10 Pashto_100_BIN2
11 Syriac_100_BIN2
12 Tibetan_100_BIN2
/
Различные версии
В обоих названиях Latin1_General_100_BIN2 и Latin1_General_BIN2 используются коллации “Latin1_General_*_BIN2”, которые отличаются только отсутствие номера, что подразумевает 80 в одном случае, и 100 (т.е. номер версии) - в другом. Для данных VARCHAR эта разница не имеет значения: поведение всех бинарных коллаций одного и того же языка одинаково.
Однако для данных Юникод (главным образом, NVARCHAR, но, начиная с версии SQL Server 2019 CTP2.3, также и VARCHAR, если используется новая коллация UTF8_BIN2) имеется важное различие. Все коллации отображают кодовые точки на различные свойства, такие как: веса сортировки (используется для сортировки и сопоставления), числовые значения для "чисел", версии нижнего и верхнего регистра, и т.д. Например:
SELECT 1 WHERE N'½' COLLATE Latin1_General_100_CI_AS LIKE N'[3-5]';
-- {не возвращает строк}
SELECT 2 WHERE N'½' COLLATE Latin1_General_100_CI_AS LIKE N'[0-1]';
-- 2
Но именно эти сопоставления верхнего и нижнего регистров являются здесь важными. Всего несколько кодовых точек отсутствуют в этом отображении, но с каждой новой версией введенных коллаций все больше кодовых точек занимают эти места. Например, следующий пример показывает кодовую точку, которая отсутствует в отображении верхнего регистра в коллациях обоих версий - 80 и 90 (если она отсутствует в какой-либо конкретной версии, то будет отсутствовать и во во всех предыдущих версиях также - вот почему мне не потребовался столбец для коллации версии 80):
SELECT NCHAR(0x1FF3) AS [TheChar],
UPPER(NCHAR(0x1FF3) COLLATE Japanese_90_BIN2) AS [v90],
UPPER(NCHAR(0x1FF3) COLLATE Japanese_XJIS_100_BIN2) AS [v100],
UPPER(NCHAR(0x1FF3) COLLATE Japanese_XJIS_140_BIN2) AS [v140];
/
TheChar v90 v100 v140
ῳ ῳ ῼ ῼ
/
Следующий пример показывает кодовую точку, которая отсутствует в своём отображении верхнего регистра в коллациях версий 80, 90 и 100:
SELECT NCHAR(0x0250) AS [TheChar],
UPPER(NCHAR(0x0250) COLLATE Japanese_90_BIN2) AS [v90],
UPPER(NCHAR(0x0250) COLLATE Japanese_XJIS_100_BIN2) AS [v100],
UPPER(NCHAR(0x0250) COLLATE Japanese_XJIS_140_BIN2) AS [v140];
/
TheChar v90 v100 v140
ɐ ɐ ɐ Ɐ
/
Хорошо, так сколько таких сопоставлений отсутствует в каждой версии? Я рад, что вы спросили.
Единственный способ, с помощью которого мы можем определить, какие сопоставления отсутствуют, это - посмотреть на различия между наиболее старой версией (наибольшее число пропущенных сопоставлений) и новейшей версией (наименьшее число пропущенных сопоставлений) коллаций. С точки зрения логики это означает, что, поскольку у нас есть 4 версии - 80, 90, 100 и 140 - данные версии 80 будут естественно "все отсутствовать", а данные версии 140 будут естественно "все присутствовать". Возможны дополнительные сопоставления, отсутствующие во всех версиях, но а) у нас нет способа определить это теперь, как это требует перекрестных ссылок на официальную Базу данных Символов Юников, что возможно, но не в данном случае, и б) не любые потенциально отсутствующие сопоставления релевантны вопросу "какая разница между существующими коллациями, доступными в SQL Server".
Нижеприведенный запрос находит все кодовые точки, которые возвращают тот же символ, который был использован с функцией LOWER, при использовании коллации версии 80, но возвращал отличный символ, когда тот же исходный символ использовался, но в коллации версии 140. Что касается коллаций версии 90 и 100, если функция LOWER возвращает символ, который отличается от того, который вводился, отображается символ в нижнем регистре, в противном случае будет возвращен NULL. В последней строке подводятся итоги. Поле для коллаций версии 80 включено только для того, чтобы ясно показать, что не существует отображения в нижнем регистре для этих символов.
;WITH nums AS
(
SELECT TOP (65534) ROW_NUMBER() OVER(ORDER BY @@SPID) AS [num]
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
), chars AS
(
SELECT nums.[num] AS [CodePoint],
CONVERT(BINARY(2), nums.[num]) AS [CodePointHex],
NCHAR(nums.[num]) AS [ TheChar ],
LOWER(NCHAR(nums.[num]) COLLATE Japanese_BIN2) AS [v80],
' ' AS [----],
IIF(LOWER(NCHAR(nums.[num]) COLLATE Japanese_90_BIN2)
<> NCHAR(nums.[num]),
LOWER(NCHAR(nums.[num]) COLLATE Japanese_90_BIN2),
NULL) AS [v90],
IIF(LOWER(NCHAR(nums.[num]) COLLATE Japanese_XJIS_100_BIN2)
<> NCHAR(nums.[num]),
LOWER(NCHAR(nums.[num]) COLLATE Japanese_XJIS_100_BIN2),
NULL) AS [v100],
IIF(LOWER(NCHAR(nums.[num]) COLLATE Japanese_XJIS_140_BIN2)
<> NCHAR(nums.[num]),
LOWER(NCHAR(nums.[num]) COLLATE Japanese_XJIS_140_BIN2),
NULL) AS [v140]
FROM nums
WHERE LOWER(NCHAR(nums.[num]) COLLATE Japanese_BIN2)
= NCHAR(nums.[num]) -- mapping not in version 80 collations
AND LOWER(NCHAR(nums.[num]) COLLATE Japanese_XJIS_140_BIN2)
<> NCHAR(nums.[num]) -- mapping in version 140 collations
)
SELECT FROM chars
UNION ALL
SELECT NULL, NULL, N'TOTALS:',
CONVERT(NVARCHAR(5), COUNT(chars.[v80])),
' ' AS [----],
CONVERT(NVARCHAR(5), COUNT(chars.[v90])),
CONVERT(NVARCHAR(5), COUNT(chars.[v100])),
CONVERT(NVARCHAR(5), COUNT(chars.[v140]))
FROM chars;
/
CodePoint HexValue TheChar v80 -- v90 v100 v140
...
502 0x01F6 Ƕ Ƕ NULL ƕ ƕ
...
8498 0x2132 Ⅎ Ⅎ NULL ⅎ ⅎ
...
11374 0x2C6E Ɱ Ɱ NULL NULL ɱ
...
42820 0xA744 Ꝅ Ꝅ NULL NULL ꝅ
...
NULL NULL TOTALS: 305 0 200 305
/
Я включил случайную выборку 4 строк из результирующего набора, плюс суммарную строку. Последняя показывает, что имеется 305 символов, для которых отсутствует отображение в нижнем регистре в коллациях 80 версии. Коллации версии 90 не добавили отображений. Однако коллации версии 100 добавили 200 отображений, а в версии - еще 105 отображений, что составляет 305 отображений, отсутствующих в коллациях версий 80 и 90.
Мы еще не все сделали. Отображение нижнего регистра - это только одно направление. Нам нужно также включить отображение верхнего регистра. И, привлекая его, не следует предполагать, что отсутствующие отображения верхнего регистра есть просто версии в верхнем регистре символов, которые отсутствуют в их отображениях нижнего регистра. Каждая кодовая точка имеет отображение верхнего или нижнего регистра (или может иметь), и они полностью независимы, поэтому просто из того, что один вход был добавлен, не следует, что какое-то другое отображение было добавлено также, даже если кажется, что так должно быть.
Приведенный ниже запрос находит все кодовые точки, которые возвращают тот же самый символ, который использовался в функции UPPER при использовании коллации версии 80, но которые также возвращают отличный символ, когда вводился исходный символ, но использовалась коллация версии 140. Для коллаций версий 90 и 100, если функция UPPER возвращает символ, который отличается от вводимого, отображается символ в верхнем регистре, в противном случае - NULL. Последняя строка показывает итоговые результаты. Поле для коллаций версии 80 включены просто для того, чтобы ясно продемонстрировать отсутствие отображения в верхний регистр для этих символов.
;WITH nums AS
(
SELECT TOP (65534) ROW_NUMBER() OVER(ORDER BY @@SPID) AS [num]
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
), chars AS
(
SELECT nums.[num] AS [CodePoint],
CONVERT(BINARY(2), nums.[num]) AS [CodePointHex],
NCHAR(nums.[num]) AS [ TheChar ],
UPPER(NCHAR(nums.[num]) COLLATE Japanese_BIN2) AS [v80],
' ' AS [----],
IIF(UPPER(NCHAR(nums.[num]) COLLATE Japanese_90_BIN2)
<> NCHAR(nums.[num]),
UPPER(NCHAR(nums.[num]) COLLATE Japanese_90_BIN2),
NULL) AS [v90],
IIF(UPPER(NCHAR(nums.[num]) COLLATE Japanese_XJIS_100_BIN2)
<> NCHAR(nums.[num]),
UPPER(NCHAR(nums.[num]) COLLATE Japanese_XJIS_100_BIN2),
NULL) AS [v100],
IIF(UPPER(NCHAR(nums.[num]) COLLATE Japanese_XJIS_140_BIN2)
<> NCHAR(nums.[num]),
UPPER(NCHAR(nums.[num]) COLLATE Japanese_XJIS_140_BIN2),
NULL) AS [v140]
FROM nums
WHERE UPPER(NCHAR(nums.[num]) COLLATE Japanese_BIN2)
= NCHAR(nums.[num]) -- mapping not in version 80 collations
AND UPPER(NCHAR(nums.[num]) COLLATE Japanese_XJIS_140_BIN2)
<> NCHAR(nums.[num]) -- mapping in version 140 collations
)
SELECT FROM chars
UNION ALL
SELECT NULL, NULL, N'TOTALS:',
CONVERT(NVARCHAR(5), COUNT(chars.[v80])),
' ' AS [----],
CONVERT(NVARCHAR(5), COUNT(chars.[v90])),
CONVERT(NVARCHAR(5), COUNT(chars.[v100])),
CONVERT(NVARCHAR(5), COUNT(chars.[v140]))
FROM chars;
/
CodePoint HexValue TheChar v80 -- v90 v100 v140
...
1165 0x048D ҍ ҍ NULL Ҍ Ҍ
...
8097 0x1FA1 ᾡ ᾡ NULL ᾩ ᾩ
...
42805 0xA735 ꜵ ꜵ NULL NULL Ꜵ
...
42849 0xA761 ꝡ ꝡ NULL NULL Ꝡ
...
NULL NULL TOTALS: 344 0 238 344
/
Я включил случайную выборку 4 строк из результирующего набора, плюс суммарную строку. Последняя показывает, что имеется 344 символа, для которых отсутствует отображение в верхнем регистре в коллациях 80 версии. Коллации версии 90 не добавили отображений. Однако коллации версии 100 добавили 238 отображений, а в версии - еще 106 отображений, что составляет 344 отображений, отсутствующих в коллациях версий 80 и 90.
Соберем данные обоих запросов вместе, вот итоговые результаты:
- Версия 80: отсутствует 649 отображений (305 + 344)
- Версия 90: отсутствует 649 отображений (305 + 344)
- Версия 100: добавлено 438 отображений (200 + 238) по сравнению с версиями 80 и 90; отсутствует только 211 отображений (105 + 106, или 649 - 438)
- Версия 140: добавлено 211 отображений (105 + 106) по сравнению с версией 100; добавлены все 649 отображений относительно версий 80 и 90
Эти данные показывают почему:
1. если возможно, важно использовать самую последнюю доступную версию (версия 100 была введена в SQL Server 2008, а версия 140 - в SQL Server 2017)! Имейте в виду, что эти отображения имеют только одно отличие. Имеются также многие тысячи весов сортировки, отсутствующие в более ранних версиях (почти 20К отсутствующих в версии 80, 13К - в версии 90, и почти 5К в версии 100).
2. коллация UTF8_BIN2 является новой в CTP 2.3 SQL Server 2019 и лучше, чем не имеющая бинарной коллации UTF-8 (которая использовалась до CTP 2.3), однако, что касается коллации 80, в ней отсутствуют 649 отображений верхнего/нижнего регистра.
Различные типы бинарного сравнения
Судя по именам, мы видим, что Latin1_General_100_BIN2 и Latin1_General_100_BIN являются коллациями “Latin1_General_100”, а различие между ними заключается в “_BIN” против “_BIN2” (т.е. в типе бинарного сравнения). Для данных VARCHAR, не являющихся UTF-8, эта разница не имеет значения: поведение всех бинарных коллаций одного и того же языка аналогично.
Однако для данных Юникод (главным образом, NVARCHAR, но и VARCHAR, если используется новая коллация UTF8_BIN2, начиная с SQL Server 2019 CTP 2.3) имеется важное различие. При сравнении значений, поведение всех бинарных коллаций в точности одинаково. Поэтому то, что нас интересует, это сортировка.
Согласно документации Майкрософт Collation and Unicode Support:
В коллации BIN2 все символы сортируются согласно из кодовым точкам. В коллации BIN только первый символ сортируется в соответствии с кодовой точкой, а остальные символы сортируются согласно из байтовым значениям. (Поскольку платформа Intel предполагает архитектуру с порядком байтов от младшего к старшему, символы кодов Юникод всегда сохраняются побайтово.)
Это означает: поведение совпадает только для первого символа. Поэтому если вы тестируете различие между “_BIN” и “_BIN2” сортировкой списка единичных символов, то никогда не увидите разницы. Для того, чтобы увидеть разницу в поведении, необходимо отбросить первый символ. Следующий тест демонстрирует это выводя как поле с единичным символом, так и поле с символом-префиксом, который одинаков для обеих строк, погашая эффект первого символа при сортировке:
SELECT tab.[ID],
tab.[col] AS [TheChar],
N'_' + tab.[col] AS [PrefixedChar],
'--' AS [--],
ROW_NUMBER() OVER (ORDER BY tab.[col]
COLLATE Latin1_General_100_BIN) AS [TheCharBIN],
ROW_NUMBER() OVER (ORDER BY N'_' + tab.[col]
COLLATE Latin1_General_100_BIN) AS [PrefixedBIN],
'--' AS [--],
ROW_NUMBER() OVER (ORDER BY tab.[col]
COLLATE Latin1_General_100_BIN2) AS [TheCharBIN2],
ROW_NUMBER() OVER (ORDER BY N'_' + tab.[col]
COLLATE Latin1_General_100_BIN2) AS [PrefixedBIN2]
FROM (VALUES (1, NCHAR(0x1225)), (2, NCHAR(0x2113))) tab([ID], [col])
ORDER BY tab.[ID];
/
ID Char Prefixed -- Char Prefixed -- Char Prefixed
BIN BIN BIN2 BIN2
1 ሥ _ሥ -- 1 2 -- 1 1
2 ℓ _ℓ -- 2 1 -- 2 2
*/
Поле “PrefixedBIN” показывает разницу, поскольку начиная с байта #3, коллация “_BIN” видит 0x25 и 0x13 (благодаря побайтовой кодировке от младшего байта к старшему), в то время как коллация “_BIN2” видит 0x12 и 0x21. Если это не ясно, возможно, следующая схема поможет. И помним о том, что мы работаем с "префиксным" значением, которое эффективно игнорирует первый символ, поскольку нет разницы в сортировке по позиции первого символа. Схема, приведенная ниже, показывает символ и его байты, начиная с символа с номером 2.
Кодовая точка Символ UTF-16 LE BIN-сортировка BIN2-сортировка
U+1225 ሥ 0x2512 0x25 затем 0x12 0x12 затем 0x25
U+2113 ℓ 0x1321 0x13 затем 0x21 0x21 затем 0x13
Дополнительные символы
Следующий запрос показывает как обрабатываются дополнительные символы в коллациях “_BIN“, “_BIN2”, а также в небинарных коллациях версии 90 или выше:
SELECT tab.[ID],
tab.[col] AS [TheChar],
N'_' + tab.[col] AS [PrefixedChar],
'---' AS [---],
ROW_NUMBER() OVER (ORDER BY tab.[col]
COLLATE Latin1_General_100_BIN) AS [Char_BIN],
ROW_NUMBER() OVER (ORDER BY N'_' + tab.[col]
COLLATE Latin1_General_100_BIN) AS [Prefixed_BIN],
'---' AS [---],
ROW_NUMBER() OVER (ORDER BY tab.[col]
COLLATE Latin1_General_100_BIN2) AS [Char_BIN2],
ROW_NUMBER() OVER (ORDER BY N'_' + tab.[col]
COLLATE Latin1_General_100_BIN2) AS [Prefixed_BIN2],
'---' AS [---],
ROW_NUMBER() OVER (ORDER BY tab.[col]
COLLATE Japanese_90_CI_AS) AS [Char_CI_AS],
ROW_NUMBER() OVER (ORDER BY N'_' + tab.[col]
COLLATE Japanese_90_CI_AS) AS [Prefixed_CI_AS]
FROM (VALUES (1, NCHAR(0xFB06)), (2, NCHAR(0xD802) + NCHAR(0xDC2C)),
(3, NCHAR(0xD83D) + NCHAR(0xDE43))) tab([ID], [col])
ORDER BY tab.[ID];
Этот запрос возвращает следующие 3 строки:
ID Char Prefixed BIN BIN2 90_CI_AS
Char Prefixed Char Prefixed Char Prefixed
1 st _st 3 2 3 3 1 1
2 ???? _???? 1 1 1 1 2 2
3 ???? _???? 2 3 2 2 3 3
Этот запрос предназначен для выявления двух связанных нюансов работы с UTF-16 (т.е. кодировка Юникод, используемая данными NVARCHAR): один относительно терминологии, а другой - поведения.
Глядя на столбцы с порядковыми номерами (шесть крайних справа столбцов), можно заметить следующее:
1. Оба - префиксный и непрефиксный столбцы для “_BIN2” сортируются в одном и том же порядке.
2. Непрефиксные символы и для “_BIN”, и для “_BIN2” сортируются в одном и том же порядке.
3. Непрефиксные и префиксные символы для “_BIN” не сортируются в одном и том же порядке.
Пока что все это поведение ожидаемо и согласуется с тем, что мы видели выше.
Однако небинарные столбцы коллации версии 90 (два крайних справа столбца) показывают для обоих порядок:
- уже одинаковый для непрефиксных и префиксных столбцов;
- отличный от любого из двух других порядков сортировки, демонстрируемых столбцами слева.
Эта разница в порядке сортировки выявляет техническую особенность, которая нечасто упоминается, вероятно, потому что по большей части отсюда извлекается небольшая практическая польза. Теперь вы уже знаете, что надлежащие значения, используемые в Юникод для однозначной идентификации каждого символа, называются "кодовыми точками". Кодовая точка может быть закодирована различными способами. Спецификация Юникод предлагает 5 вариантов кодировки: UTF-8, UTF-16 Big Endian, UTF-16 Little Endian, UTF-32 Big Endian и UTF-32 Little Endian (Я оставляю в стороне не-Endian вариации UTF-16 и UTF-32, поскольку они просто применяют Big Endian). UTF-8 кодирует каждую кодовую точку как отдельный объект, используя от 1 до 4 байтов. UTF-32 кодирует каждую кодовую точку как отдельный объект посредством 4 байтов.
Напротив, UTF-16 кодирует каждую кодовую точку как одно- или двухбайтовый объект. Для первых 65536 кодовых точек (общее число 2-байтовых комбинаций, 0х0000 - 0хFFFF) все сказанное остается справедливым. Но дело немного запутывается, когда речь идет о дополнительных символах (т.е. всех символах за пределами начальных 65536). Дополнительные символы, подобно всем остальным символам, имеют каждый уникальную кодовую точку. В UTF-8 и UTF-32 не существует различий, но в UTF-16 каждый дополнительный символ состоит из двух недополнительных символов (т.е. "суррогатных пар"). Тогда к чему относится термин "кодовая точка"? Относится ли она к реальному дополнительному символу, или же к каждому из двухбайтовых кусочков, которые составляют дополнительный символ? Это не может быть и тем, и другим. И не является.
В UTF-16 двухбайтовые объекты фактически известны как "кодовые единицы". И просто так случилось, что каждая их первых 65536 кодовых точек состоит из единственной кодовой единицы, имеющей то же самое значение (например, кодовая точка U+FB06 имеет кодовую единицу FB06). Дополнительные символы являются кодовыми точками, составленными их двух кодовых единиц (например, кодовая точка U+1F643 составлена из кодовых единиц D83D и DE43).
Заглянув обратно в документацию Майкрософт, процитированную в начале этого раздела, мы теперь можем увидеть, что утверждение "В коллации BIN2 все символы сортируются согласно их кодовым точкам" не является вполне точным. Конечно, для не-дополнительных символов это справедливо, однако случай с дополнительными символами показывает нам, что определение коллации BIN2 технически некорректно. Данные NVARCHAR в коллации BIN2 сортируются по кодовым единицам, а не по кодовым точкам.
Я несправедлив, или чрезмерно придирчив? Нет, поскольку запрос и результаты выше показывают, что имеется три способа сортировки этих значений, и невозможно объяснить это поведение без понимания разницы между кодовой единицей и кодовой точкой. Надеюсь, что схема ниже поможет визуализировать различие в этих трех методах сортировки. Эта схема показывает те же три символа в той же последовательности, что и результаты выше: BIN, потом BIN2 и затем небинарная версия 90 или новее. Но теперь я показываю, как алгоритм сортировки видел каждый символ, что объяснит, почему каждый подход делает что-то отличное.
- Бинарный (_BIN): этот алгоритм движется побайтово (после первого символа, т.е. после первых двух байтов). И поскольку мы работаем на платформе Little Endian (порядок от младшего байта к старшему), байты каждого базового объекта меняются. Объект UTF-16 (т.е. кодовая единица) всегда представляет собой 2 байта. Значение в скобках в столбце "Binary" есть перевернутая первая кодовая единица, вот почему первый байт в первой строке равен 0х02 вместо 0хD8, а первый байт во второй строке равен 0х06 вместо 0хFB.
- Кодовая единица (_BIN2): этот алгоритм переходит от кодовой единице к кодовой единице. Значение кодовой единицы остается одинаковой вне зависимости от того, как она кодируется. Поэтому число байтов и их физический порядок не имеют значения. Здесь нет необходимости показывать различные представления в скобках в столбце "CodeUnit", поскольку D802 есть D802, даже если эта кодовая единица сохраняется как 0x02D8 на диске.
- Кодовая точка (небинарная версия 90 или новее): этот алгоритм идет от кодовой точки к кодовой точке (только для дополнительных символов; в противном случае небинарные коллации используют лингвистические правила конкретного языка). Значение в скобках в столбце “CodePoint” представляет собой фактическую кодовую точку, и это то значение, которое используется при сортировке (не байты или кодовые единицы).
Binary (_BIN) CodeUnit (_BIN2) CodePoint (_90_CI_AS)
0xD802DC2C (02D8) 0xD802DC2C 0xFB06 (U+FB06)
0xFB06 (06FB) 0xD83DDE43 0xD802DC2C (U+1082C)
0xD83DDE43 (3DD8) 0xFB06 0xD83DDE43 (U+1F643)
Учитывая описание коллаций "_BIN2" как упорядочение по "кодовым точкам", которые фактически являются упорядочением по "кодовым единицам", какое будет правильным? Согласно следующей цитате из официального стандарта Юникод (т.е. спецификации) в Chapter 2: General Structure / Section 2.5: Encoding Forms (page 39):
Все три формы кодирования дают одни и те же результаты для сравнения бинарных строк или сортировке строк, только когда дело касается BMP-символов (в диапазоне U+0000..U+FFFF). Однако когда речь идет о дополнительных символах (в диапазоне U+10000..U+10FFFF), бинарный порядок UTF-16 не соответствует порядку кодовых точек Юникод.
Похоже, что это поведение верное, и документация нуждается в обновлении.
Бонусный раунд UTF-8
Начиная с SQL Server 2019 стало возможным использовать кодировку UTF-8.
Спецификация Юникод (на той же упоминаемой странице) гласит:
Бинарная сортировка строк UTF-8 дает тот же порядок, что и бинарная сортировка по кодовым точкам Юникод. Это очевидно тот же порядок, как для бинарной сортировки строк UTF-32.
Мы можем протестировать это следующим запросом:
SELECT tab.[ID],
tab.[col] AS [TheCharNVC],
CONVERT(VARCHAR(10), tab.[col] COLLATE UTF8_BIN2) AS [TheCharVC],
'--' AS [--],
ROW_NUMBER() OVER (ORDER BY tab.[col] COLLATE UTF8_BIN2)
AS [CharNVC_UTF8_BIN2],
ROW_NUMBER() OVER (ORDER BY CONVERT(VARCHAR(10), tab.[col]
COLLATE UTF8_BIN2)) AS [CharVC_UTF8_BIN2]
FROM (VALUES (1, NCHAR(0xFB06)),
(2, NCHAR(0xD802) + NCHAR(0xDC2C)),
(3, NCHAR(0xD83D) + NCHAR(0xDE43))) tab([ID], [col])
ORDER BY tab.[ID];
который возвращает:
ID TheCharNVC
(UTF-16) TheCharVC
(UTF-8) CharNVC_UTF8_BIN2
(UTF-16) CharVC_UTF8_BIN2
(UTF-8)
1 st st 3 1
2 ???? ???? 1 2
3 ???? ???? 2 3
Столбцы NVARCHAR показывают, что мы имеем дело с одними и теми же тремя символами между столбцами UTF-8 и UTF-16, что порядок согласуется с тем, который мы наблюдали прежде для данных NVARCHAR, и что порядок отличается от порядка для столбца UTF-8. Теперь нам интересно поведение UTF-8, и которое может быть только обнаружено при работе с данными VARCHAR (и, конечно, при использовании коллации UTF8). Схема выше показывает, что, да, коллация UTF8_BIN2 (новая в SQL Server 2019 CTP2.3) действительно использует кодовую точку для упорядочивания.
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой