Выбор двоичной сортировки, которая может различать "ss" и "ß" для столбца nvarchar на Sql Server

Ответ 1

Несколько вещей о Collations:

• SQL_ Коллации были устаревшими с SQL Server 2000 (да, 2000). Если вы можете избежать их использования, вы должны (но это не значит, что вы меняете кучу вещей, если нет насущной необходимости!).

  Проблема с SQL_ Collations действительно связана только с данными VARCHAR (т.е. не Unicode), поскольку данные NVARCHAR (т.е. Unicode) используют правила из ОС. Но правила сортировки и сравнения данных VARCHAR, к сожалению, используют простое сопоставление и не включают более сложные лингвистические правила. Вот почему ss и ß не приравниваются при сохранении как VARCHAR с использованием того же SQL_Latin1_General_CP1_CI_AS Collation. Эти устаревшие коллажи также не могут дать более низкий вес тире при использовании в середине слова. Не SQL_ Collations (то есть Windows Collations) используют те же правила для VARCHAR и NVARCHAR, поэтому обработка VARCHAR более надежная, более согласованная с NVARCHAR.

• _BIN Коллации были устаревшими с SQL Server 2005. Если вы можете избежать их использования, вы должны (но это не значит, что вы меняете кучу вещей, если нет насущной необходимости!).

  Проблема с _BIN Collations довольно тонкая, так как она влияет только на сортировку. Сравнение одинаково между _BIN и _BIN2. Сопоставления, связанные с ними, сравниваются на уровне байтов (следовательно, нет языковых правил). НО, из-за того, что SQL Server (и Windows/ПК) является маленьким Endian, объекты сохраняются в порядке обратного байта. Это становится очевидным при работе с двухбайтовыми "символами", что составляет NVARCHAR данных: UTF-16 Little Endian. Это означает, что Unicode Code Point U + 1216 имеет шестнадцатеричное/двоичное представление 0x1216 в системах Big Endian, но хранится как 0x1612 в системах Little Endian. Чтобы пройти полный круг, чтобы важность этой последней точки (надеюсь) стала очевидной: _BIN Collations сравнивает байты по байтам (после первого символа) и, следовательно, видит U + 1216 как 0x16, а затем 0x12, тогда как _BIN2 Коллаборации будут сравнивать кодовую точку по кодовой точке и, следовательно, видеть U + 1216 как 0x12, а затем 0x16.

• Этот столбец столбца NVARCHAR (a VARCHAR, использующий SQL_Latin1_General_CP1_CI_AS, не будет приравнивать ss и ß), и поэтому только для этого столбца нет разницы между SQL_Latin1_General_CP437_BIN2 и SQL_Latin1_General_CP850_BIN2 из-за того, что Unicode является единым универсальным набором символов.

• Для данных VARCHAR будет разница, так как они являются разными кодовыми страницами (437 и 850), и оба они отличаются от тех, которые вы используете сейчас (CP1== code page 1252).

• При использовании двоичной сортировки часто бывает излишним, в этом случае это может быть необходимо, учитывая, что существует только одна локаль/культура, которая не приравнивает ß к ss: венгерский. Использование венгерской сортировки может иметь некоторые лингвистические правила, которые вы не хотите (или, по крайней мере, не ожидаете), поэтому бинарный сортировка, по-видимому, является лучшим выбором здесь (просто ни один из 4, о котором вы спрашиваете:-), Просто имейте в виду, что, используя двоичную сортировку, вы не только отказываетесь от всех лингвистических правил, но также теряете способность приравнивать разные версии одного и того же символа, такие как A (Latin Capital Letter A U + 0041) и Ａ (Fullwidth Latin Capital Letter A U + FF21).

  Используйте следующий запрос, чтобы увидеть, какие колликации не являются двоичными и не приравнивают эти символы:

  DECLARE @SQL NVARCHAR(MAX) = N'DECLARE @Counter INT = 1;';
  
  SELECT @SQL += REPLACE(N'
    IF(N''ß'' COLLATE {Name} = N''ss'' COLLATE {Name})
    BEGIN
      RAISERROR(N''%4d.  {Name}'', 10, 1, @Counter) WITH NOWAIT;
      SET @Counter += 1;
    END;
  ', N'{Name}', col.[name]) + NCHAR(13) + NCHAR(10)
  FROM   sys.fn_helpcollations() col
  WHERE  col.[name] NOT LIKE N'SQL[_]%'
  AND    col.[name] NOT LIKE N'%[_]BIN%'
  ORDER BY col.[name]
  
  --PRINT @SQL;
  EXEC (@SQL);
  

Итак:

• Если вы собираетесь использовать двоичную сортировку, используйте что-то вроде Latin1_General_100_BIN2.
• Вам не нужно изменять Collation всей БД и всех ее таблиц. Это большая работа, и единственный "встроенный" механизм для этого не документирован (т.е. Не поддерживается).
• Если вы хотите изменить стандартную сортировку базы данных, которая влияет на разрешение имен объектов с областью базы данных, таких как таблицы, столбцы, индексы, функции, хранимые процедуры и т.д. Значение: вам нужно будет отменить 100% приложения, которое касается базы данных, а также всех заданий агента SQL Server и т.д., которые касаются этой базы данных.

• Если для большинства/всех запросов, которые используют этот столбец, нужно ß с ss восприниматься как разные, а затем перейдите к столбцу, чтобы использовать Latin1_General_100_BIN2. Для этого, вероятно, потребуется отбросить следующие зависимые объекты, а затем воссоздать после ALTER TABLE:

  • Индексы
  • Уникальные ограничения
  • Ограничения внешнего ключа

  СОВЕТ: Обязательно проверьте текущую настройку NULL/NOT NULL столбца и укажите это в инструкции ALTER TABLE ... ALTER COLUMN ..., чтобы она не менялась.

• Если только некоторые запросы нуждаются в этом другом поведении, переопределите только те операции сравнения с предложением COLLATE на основе каждого условия (например, WHERE tab.[ThisColumn] LIKE N'%ss%' COLLATE Latin1_General_100_BIN2). Ключевое слово COLLATE должно быть необходимо только с одной стороны (оператора), поскольку приоритет сортировки будет применяться к другой стороне.

Ответ 2

В общем случае BIN2 предпочтительнее, чем BIN, и вы можете захотеть выбрать сортировку окон по сравнению со сверткой sql. например Latin1_General_100_BIN2

Рекомендации по использованию BIN и BIN2 Collations

Рекомендации по использованию BIN-сортировок

Если ваши приложения SQL Server взаимодействуют со старыми версиями SQL Server, которые используют двоичные сопоставления, продолжайте использовать двоичный файл. Бинарные сортировки могут быть более подходящим выбором для смешанных сред.

По тем же причинам, что только что были заявлены относительно сопоставлений BIN2, если у вас нет конкретных требований для поддержания обратной совместимости, вы должны опираться на использование сопоставлений Windows, а не на сортировки SQL Server (т.е. те, которые начинаются с SQL теперь рассматриваются как "sucky";-)).
- @srutzky - Влияние производительности Latin1_General_BIN при изменении сортировки по умолчанию в базе данных

демонстрационная версия рекстера: http://rextester.com/KIIDYH74471

create table t (
    a varchar(16)  --collate SQL_Latin1_General_CP1_CI_AS /* default */
  , b varchar(16)  --collate SQL_Latin1_General_CP1_CI_AS
  , c nvarchar(16) --collate SQL_Latin1_General_CP1_CI_AS
  , d nvarchar(16) --collate SQL_Latin1_General_CP1_CI_AS 
);
insert into t values ('ss','ß',N'ss',N'ß');
select *
    , case when a = b then '=' else '!=' end as [a=b] /* != */
    , case when a = d then '=' else '!=' end as [a=d] /* = */
    , case when c = b then '=' else '!=' end as [c=b] /* = */
    , case when c = d then '=' else '!=' end as [c=d] /* = */
from t;

возвращает:

+----+---+----+---+-----+-----+-----+-----+
| a  | b | c  | d | a=b | a=d | c=b | c=d |
+----+---+----+---+-----+-----+-----+-----+
| ss | ß | ss | ß | !=  | =   | =   | =   |
+----+---+----+---+-----+-----+-----+-----+

create table t (
    a varchar(16)  collate Latin1_General_100_BIN2
  , b varchar(16)  collate Latin1_General_100_BIN2
  , c nvarchar(16) collate Latin1_General_100_BIN2
  , d nvarchar(16) collate Latin1_General_100_BIN2
);
insert into t values ('ss','ß',N'ss',N'ß');
select *
    , case when a = b then '=' else '!=' end as [a=b] /* != */
    , case when a = d then '=' else '!=' end as [a=d] /* != */
    , case when c = b then '=' else '!=' end as [c=b] /* != */
    , case when c = d then '=' else '!=' end as [c=d] /* != */
from t;

возвращает:

+----+---+----+---+-----+-----+-----+-----+
| a  | b | c  | d | a=b | a=d | c=b | c=d |
+----+---+----+---+-----+-----+-----+-----+
| ss | ß | ss | ß | !=  | !=  | !=  | !=  |
+----+---+----+---+-----+-----+-----+-----+