Статья подготовлена для студентов курса «MS SQL Server разработчик»
Хочу поделиться историей из одного из предыдущих проектов, которая иллюстрирует, что Collation нужно выбирать очень вдумчиво. И о том, что бывает, если этот параметр все-таки выбрали неверно, и какие варианты решения проблемы бывают.

Сначала небольшое введение о том, что же такое Collation. В SQL Server параметр Collation указывает серверу, как нужно сортировать и сравнивать строки. Вот, например, строки “Apple” и “apple”. Они разные или нет? Это зависит от указанного Collation. Если с регистром все более менее понятно, то что делать с примером “елка” и “ёлка”? Считать их как одинаковые или как разные? Это все тоже в Collation.

История случилась в проекте, функционал которого очень похож на DropBox или Google Диск. Он предоставляет возможность управлять своими синхронизированными папками и файлами на разных машинах, а также возможность другим пользователям иметь доступ к данной синхронизированной папке.

Итак, история началась с того, что на Prod серверах было 75-90% ошибок в логах (см скриншот ниже), и непонятно откуда они возникали, и в чем была их причина. Ошибка звучала так: “ReadWrtLst is not complete”. Далее шли детали пользователя и его папки.

По коду довольно быстро было найдено место, которое генерирует ошибку, но понять, почему она возникала, и как ее воспроизвести, у нас не получалось.Понятно было только то, что ошибка каким-то образом связана с тем, что пользователь как-то умудрился сделать еще одну папку с таким же именем в своей ОС.

Мы собрали информацию по пользователям, по которым выдается эта ошибка. И тут нас ждал первый сюрприз: из миллионов пользователей системы эта ошибка случалась всего у 50. И эти 50 пользователей генерируют 90% логов об ошибках. Так как ситуацию не получалось воспроизвести, то мы решили связаться с одним из пользователей и выяснить, по какой причине у него не синхронизируется одна из папок. Папка выглядела для нас так же, как и другие, единственное отличие было в том, что называлась она на языке пользователя с использованием иероглифов. А пользователь был японцем. К слову сказать, среди этих 50 пользователей, японцев было большинство.

Благодаря одному из разработчиков команды, нам удалось воспроизвести ошибку. Ошибка заключалась в том, что операционная система считала названия папок разными, а SQL Server считал их одинаковыми из-за выбранного Collation.

Collation, который использовался в проекте:
SQL_Latin1_General_CP1_CI_AS

Небольшое отступление о том, как прочесть Collation. (Если вы знакомы с ним, смело пропускайте.)

Итак, в Collation есть несколько частей:

• SQL — параметры сортировки по SQL Server (SQL в начале Collation) или Windows (тогда было бы просто Latin1_ …);
• Latin1_General — локаль или используемый язык;
• CP1 — code page — кодовая страница;
• CI — Case Insensitive — без учета регистра;
• AS — Accent Sensitive — с учетом аксонов или диакритических знаков, проще говоря 'a' не считается равным '?'.

Этот Collation был когда-то Collation по умолчанию, когда устанавливали SQL Server.

Какие опции есть еще?

• _KS — с учетом японских иероглифов хирагана и катакана, если параметр не выбран, то SQL Server будет интерпретировать иероглифы хирагана и катакана как одинаковые.
• _WS — с учетом ширины символов, если параметр не выбран, то “Text” и “T e x t” считаются одинаковыми строками.
• _VSS — с учетом знаков выбора варианта написания в японском языке, появился с версии 2017.
• _UTF8 — позволяет хранить данные в UTF8.

Все текстовые поля в БД использовали тип NVARCHAR.
Получается, что, так как текущий Collation игнорировал разницу написания японских иероглифов и разницу в ширине символов, то SQL Server сравнивал строки не так, как это делала операционная система, что и вызывало проблему, т.е. пользователь мог создать папки, не мог их добавить в систему для синхронизации. Тоже самое возникло бы в дальнейшем при сравнении имен файлов.

Мы стали думать о том, как можно решить данную проблему и изменить Collation.

Collation можно выставлять на нескольких уровнях:

• SQL Server инстанс
• База данных
• Таблица
• Поле

При этом, не рекомендуется иметь внутри БД разные Collation, потому что каждый раз при сравнении строк с разным Collation нужно будет делать преобразование с помощью COLLATE, указывая серверу, какой порядок сравнения ему нужно использовать.

Какие опции есть в ситуации, когда понятно, что Collation выбран не очень правильно?

1. Изменить Collation на уровне БД;
2. Изменить Collation на уровне поля (в нашем случае не было смысла менять для всей таблицы);
3. Добавить поле Varbinary, в которое записывать дубликат из поля с названием папки, и использовать именно его для сравнения;
4. Сказать пользователям, что есть ограничения на поддержку символов в названиях директорий.

Первая опция — изменение Collation на уровне БД — наиболее сложна. В случае с БД потребовалось бы пересоздать базу данных и перезалить туда данные. Так как система работала 24/7, эта опция была отвергнута сразу.

Вторая опция про изменение поля: самый простой вариант ее реализовать — это добавить поле с нужным Collation и туда перенести данные. Но тогда нужно будет изменять код в БД, который работает с этим полем, а кода в БД было очень много.

Третья опция понравилась нам больше всего, так как в теории это вносило меньше всего изменений, так как основное поле продолжало бы существовать с текущим Collation, и мы бы не имели проблем с его преобразованием, при этом весь нужный функционал в виде учета японской азбуки или широких символов бы работал. Минус в том, что нужно было вносить изменения в часть ПО, но так как эта серверная часть, это можно было сделать.

Четвертая опция была наиболее простой в данном случае, потому что общее число пользователей было несколько миллионов, а проблема возникала только у 50. Однако, если бы приложение активно использовалось в Японии, данное решение было бы мало применимо.

После представления данных руководству, было решено известить пользователей о том, что ПО не поддерживает ряд символов, и при их использовании в названии синхронизируемых файлов и папок ПО может работать некорректно. Это временное решение, потому что при дальнейшем распространении, количество пользователей, сталкивающихся с подобной проблемой, будет нарастать, и нужно будет что-то менять, используя первые три опции.

Лучший вариант выбора Collation — исходить из требований вашего приложения. Если вам нужно, чтобы SQL Server сравнивал строки так же как ОС, то Collation по умолчанию точно неверен. К сожалению, такие нюансы редко видны на старте проекта при проектировании системы, но, надеюсь, прочитав статью, Вы вспомните об описанной ситуации и не наступите на подобные грабли сами.

Полезные материалы по Collation:
https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017
https://www.red-gate.com/simple-talk/sql/sql-development/questions-sql-server-collations-shy-ask/
https://www.virtual-dba.com/sql-server-collation/