Collation, Unicode и nvarchar против varchar в SQL Server

Для большинства приложений на SQL Server храните читаемый человеком текст в nvarchar, пишите строковые литералы как N'...' и создавайте базу с современной collation, например Latin1_General_100_CI_AS_SC. Такое сочетание правильно хранит любой язык и любые эмодзи, сравнивает текст без учёта регистра, как ожидают пользователи, и совпадает с тем, что по умолчанию отправляет любой драйвер. Альтернатива начиная с SQL Server 2019 - varchar с collation UTF-8, которая хранит преимущественно английский текст вдвое компактнее, но требует большей аккуратности. Всё, что идёт не так с текстом в SQL Server, - вопросительные знаки на месте букв с диакритикой, Cannot resolve the collation conflict, индекс, который вдруг перестал использоваться, - происходит от того, что эти варианты смешали, сами того не желая. Это руководство объясняет каждую часть, чтобы вы выбирали осознанно.

varchar, nvarchar и что означает N#

В SQL Server два семейства строковых типов:

Ловушка - в третьем столбце. varchar(50) - это 50 байт, а не 50 символов, и nvarchar(50) - это 50 кодовых единиц UTF-16, тоже не 50 символов. На обычном латинском тексте разница никогда не проявляется. На эмодзи, который занимает две кодовые единицы UTF-16, или на китайском, хранимом в UTF-8 по три байта на символ, - проявляется.

Без collation UTF-8 varchar хранит текст в одной устаревшей кодовой странице - Windows-1252 для латинских collation, которые используются на большинстве серверов. В этой кодовой странице есть место для английского и большинства западноевропейских диакритических знаков, и больше ни для чего. Сохраните в неё польский, греческий, кириллицу, арабский или любой эмодзи, и SQL Server молча, в момент записи, подставит вопросительный знак или похожий символ. Данные потеряны; никакое последующее преобразование их не вернёт.

Префикс N у литерала - тот же выбор, применённый к константам. 'Zoë' - литерал varchar в кодовой странице базы; N'Zoë' - литерал Unicode. Вставьте 'Łódź' в столбец nvarchar, и вы всё равно получите испорченный текст, потому что литерал был преобразован в кодовую страницу ещё до того, как дошёл до столбца:

CREATE TABLE dbo.Cities (Name nvarchar(100) NOT NULL);INSERT dbo.Cities (Name) VALUES ('Łódź');   -- arrives as 'Lódz' or with '?'INSERT dbo.Cities (Name) VALUES (N'Łódź');  -- arrives intactSELECT Name FROM dbo.Cities;

Код приложения, использующий параметры, здесь в безопасности, потому что драйверы по умолчанию отправляют строки как Unicode. N важен в скриптах, миграциях, начальных данных и во всём, что набирается в SSMS. Сделайте его привычкой для каждого строкового литерала, который направляется в столбец nvarchar.

Что определяет collation#

Collation - это набор правил, привязанный к серверу, базе, столбцу или выражению. Она определяет три вещи:

1. Какие символы может хранить `varchar` - кодовая страница или UTF-8.
2. Как сравниваются строки - равно ли 'abc' = 'ABC', равно ли 'resume' = 'résumé'.
3. Как сортируются строки - порядок, который возвращает ORDER BY, а значит, и порядок индекса по текстовому столбцу.

Имена collation кодируют правила, и когда вы умеете их читать, выбирать становится намного проще:

Так что Latin1_General_100_CI_AS_SC_UTF8 - это общие латинские правила, версия 100, без учёта регистра, с учётом диакритики, с поддержкой дополнительных символов, UTF-8 в varchar. Список всех collation, известных серверу, выводит SELECT name, description FROM sys.fn_helpcollations();.

Windows-collation и collation SQL_#

Collation, начинающиеся с SQL_, - устаревшее семейство, сохранённое ради совместимости с версиями SQL Server из 1990-х. Самая известная - SQL_Latin1_General_CP1_CI_AS, до сих пор collation сервера по умолчанию для установки Windows с языком English (United States) и по умолчанию на Linux. Остальные - Latin1_General_100_CI_AS и родственные - это Windows-collation.

Главное различие - в том, как они обращаются с varchar. Windows-collation применяет одинаковые лингвистические правила к varchar и nvarchar. Collation SQL_ использует для varchar более старые и простые правила, а для nvarchar - правила Windows, так что одни и те же две строки могут сравниваться или сортироваться по-разному в зависимости от типа. Это же меняет то, что происходит, когда параметр nvarchar встречается со столбцом varchar: с Windows-collation оптимизатор часто всё ещё может сделать seek по индексу, с collation SQL_ он сканирует. На нагруженной таблице это разница между запросом за миллисекунду и запросом за секунду, и в коде её не видно. Как это выглядит в плане, показывает статья индексы и планы выполнения SQL Server.

Причин выбирать collation SQL_ для новой базы нет. Используйте Windows-collation версии 100 и добавьте _SC, чтобы строковые функции правильно считали эмодзи и другие дополнительные символы:

SELECT LEN(N'😀' COLLATE Latin1_General_100_CI_AS)     AS without_sc,   -- 2       LEN(N'😀' COLLATE Latin1_General_100_CI_AS_SC)  AS with_sc;      -- 1

Collation UTF-8: varchar, который хранит всё#

В SQL Server 2019 появились collation с окончанием _UTF8. С такой collation столбцы varchar хранят UTF-8, поэтому вмещают любой символ Unicode, а обычный ASCII-текст занимает один байт на символ вместо двух, как в nvarchar.

Для приложения, текст которого в основном английский, коды товаров, URL и JSON, varchar в UTF-8 может примерно вдвое сократить место под строки - а это важно в Express, где каждая база ограничена 10 ГБ. Для текста, который в основном восточноазиатский, он больше nvarchar, а не меньше.

Издержки стоит знать до того, как вы решитесь:

• varchar(n) по-прежнему считает байты, так что varchar(50) вмещает 50 символов ASCII, но только 16 китайских. Задавайте размер столбцов по длине реальных данных в байтах или используйте varchar(max), где это неважно.
• Драйверы по-прежнему по умолчанию отправляют строки как nvarchar, так что каждое сравнение с параметром включает преобразование, если не объявить параметры как varchar. EF Core делает это, когда свойство сопоставлено с IsUnicode(false); в ADO.NET задайте SqlDbType.VarChar; в драйвере JDBC - sendStringParametersAsUnicode=false.
• Не все инструменты и старые клиентские библиотеки хорошо с этим работают. Проверьте весь стек, включая отчёты и ETL.

Collation UTF-8 - разумный выбор для нового приложения, где вы контролируете слой доступа к данным и место имеет значение. Если сомневаетесь, nvarchar - вариант, который не преподнесёт сюрпризов.

Collation сервера, базы, столбца и выражения#

Collation задаётся на четырёх уровнях, и каждый служит значением по умолчанию для следующего:

• Сервер - выбирается при установке. Это collation системных баз, включая tempdb. На Linux она задаётся через MSSQL_COLLATION или mssql-conf set-collation, а изменение её потом означает пересоздание системных баз.
• База - задаётся в CREATE DATABASE ... COLLATE, по умолчанию равна серверной. Это значение по умолчанию для новых столбцов, для литералов и переменных в этой базе, и она управляет именами объектов - поэтому в базе без учёта регистра dbo.Orders и dbo.ORDERS - одна и та же таблица.
• Столбец - задаётся в CREATE TABLE или ALTER TABLE ... ALTER COLUMN, по умолчанию равна collation базы.
• Выражение - конструкция COLLATE переопределяет её для одного сравнения или сортировки.

SELECT SERVERPROPERTY('Collation')                  AS server_collation,       DATABASEPROPERTYEX(DB_NAME(), 'Collation')   AS database_collation;SELECT t.name AS table_name, c.name AS column_name, c.collation_nameFROM sys.columns AS cJOIN sys.tables  AS t ON t.object_id = c.object_idWHERE c.collation_name IS NOT NULLORDER BY t.name, c.column_id;

На хостинговом сервере вы получаете collation сервера, которую изменить нельзя. Под вашим контролем база: как sa вы можете создать новую базу с нужной collation. В RE:NODE для вас создаётся база, назначенная базой по умолчанию для sa; проверьте её collation запросом выше, прежде чем что-то на ней строить, и если она не та, создайте другую через CREATE DATABASE [app] COLLATE Latin1_General_100_CI_AS_SC;, пока база ещё пуста.

Ошибка конфликта collation#

Ошибка, с которой рано или поздно встречается каждый:

Msg 468, Level 16, State 9Cannot resolve the collation conflict between "Latin1_General_100_CI_AS_SC" and"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Две строки с разными collation нельзя сравнить, потому что SQL Server не знает, чьи правила использовать. Самый частый источник - tempdb. Временные таблицы создаются в tempdb, поэтому их столбцы получают collation сервера, а не вашей базы. Если они различаются, соединение временной таблицы с настоящей падает.

-- Fails if the server and database collations differCREATE TABLE #ids (Code varchar(20));-- Works everywhere: take the current database's collationCREATE TABLE #ids (Code varchar(20) COLLATE DATABASE_DEFAULT);

Возьмите в привычку COLLATE DATABASE_DEFAULT на каждом строковом столбце каждой временной таблицы, и код, переносимый между серверами, перестанет ломаться. Табличные переменные создаются с collation базы и этой проблемы не имеют. Для разового сравнения COLLATE на одной стороне выражения разрешает конфликт, но ценой того, что индекс на преобразованной стороне нельзя использовать для seek.

Восстановление базы на сервер с другой collation вызывает ту же проблему, при этом база сохраняет собственную collation; отличаются только tempdb и системные базы. Это ещё одна причина использовать DATABASE_DEFAULT, а не указывать collation по имени.

Чувствительность к регистру и другие сюрпризы сравнения#

Несколько вариантов поведения, которые правильны по правилам и неожиданны на практике:

• Уникальность без учёта регистра. С collation CI уникальный индекс на Email отклонит Bob@example.com, если существует bob@example.com. Обычно это то, что нужно; иногда, для идентификаторов с учётом регистра вроде токенов, - нет, и такому столбцу нужна collation CS или BIN2.
• Пробелы в конце при сравнении игнорируются. 'abc' = 'abc ' истинно - так требуют правила дополнения из стандарта SQL. LEN тоже игнорирует конечные пробелы, а DATALENGTH - нет. LIKE - исключение и не дополняет шаблон.
• Чувствительность к диакритике отделена от регистра. CI_AS считает, что 'resume' <> 'résumé'. Если пользователи ищут имена, не набирая диакритику, нужное им поведение даст collation AI на этом столбце или отдельный нормализованный столбец для поиска.
• `COLLATE` в условии `WHERE` отключает seek по индексу на этом столбце. Чтобы искать с учётом регистра в столбце без учёта регистра без сканирования, сначала сравните обычным образом (это может сделать seek), а затем добавьте проверку с учётом регистра:

SELECT UserIdFROM dbo.UsersWHERE UserName = @name  AND UserName = @name COLLATE Latin1_General_100_CS_AS;

Первый предикат делает seek к немногим строкам, совпадающим без учёта регистра; второй точно фильтрует эти немногие.

Языковые и двоичные collation#

Latin1_General - компромисс, который приемлемо сортирует английский и большинство западноевропейских языков. У некоторых языков есть правила, которым он не следует, и если ваши пользователи читают отсортированные списки на этих языках, они это заметят:

• В турецком есть i с точкой и без точки, поэтому заглавная от i - это İ, а строчная от I - ı. С Turkish_100_CI_AS UPPER(N'i') возвращает İ, а сравнение 'FILE' с 'file' без учёта регистра даёт ложь. С Latin1_General турецкие пользователи видят свои имена неправильно отсортированными и с неправильным регистром.
• В датском и норвежском æ, ø и å сортируются после z, а старое написание aa считается å. Danish_Norwegian_100_CI_AS так и делает; Latin1_General ставит Ålesund рядом с Aalborg в самое начало.
• Польский, чешский и другие центральноевропейские языки сортируют буквы с диакритикой как отдельные буквы после базовой. У каждого своё семейство collation.
• В немецком два соглашения. По умолчанию ä считается вариантом a; German_PhoneBook_100_CI_AS сортирует её как ae, как это делали телефонные справочники.

Не нужно выбирать один язык для всей базы. Collation на уровне столбца или ORDER BY Name COLLATE Danish_Norwegian_100_CI_AS в одном запросе применяет правила там, где они важны. Однако индекс отсортирован по collation своего столбца, так что запрос, сортирующий по другой collation, не может использовать порядок индекса и должен сортировать сам.

На другом конце - двоичные collation с окончанием BIN2. Они сравнивают сырые кодовые точки: никаких лингвистических правил, с учётом регистра и диакритики, и это самые дешёвые сравнения, на которые способен SQL Server. Заглавные буквы сортируются раньше всех строчных, так что 'Zebra' идёт перед 'apple'. Поэтому они не подходят для всего, что человек читает в отсортированном виде, и подходят для машинных идентификаторов - ключей API, хешей, токенов, кодов с учётом регистра, - где весь смысл в точном совпадении, а скорость помогает. Collation BIN2 на таком столбце также не даёт значению по умолчанию без учёта регистра считать два разных токена дубликатами.

Смена collation задним числом#

Сменить collation базы можно, но это делает меньше, чем люди надеются:

ALTER DATABASE [app] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [app] COLLATE Latin1_General_100_CI_AS_SC;ALTER DATABASE [app] SET MULTI_USER;

Это меняет значение по умолчанию для новых столбцов и правила для имён объектов. Существующие столбцы это не затрагивает - они сохраняют старую collation, - и команда не выполнится, если от текущей collation зависят объекты, привязанные к схеме, например индексированные представления или вычисляемые столбцы. Каждый существующий столбец придётся изменять отдельно, а для этого нужно удалить и пересоздать каждый индекс, ограничение и статистику, которые его используют:

ALTER TABLE dbo.CustomersALTER COLUMN Name nvarchar(200) COLLATE Latin1_General_100_CI_AS_SC NOT NULL;

Повторите допустимость NULL для столбца, иначе ALTER COLUMN сделает его допускающим NULL. На базе сколько-нибудь заметного размера чище бывает создать новую базу с правильной collation, создать в ней схему и перенести данные через bcp или INSERT ... SELECT. Механику разбирает статья перенос базы на хостинг SQL Server, а статья utf8mb4 и collation в MySQL рассказывает ту же историю для MySQL, если вы переходите оттуда.

FAQ#

Что использовать в новом приложении: nvarchar или varchar?

nvarchar для всего, что набирают люди, - имён, адресов, сообщений, - если только место не ограничено и вы не контролируете каждый тип параметра; в этом случае хороший вариант - varchar с collation UTF-8. Обычный varchar с collation не UTF-8 безопасен только для кодов и идентификаторов, о которых вы точно знаете, что они в ASCII.

Почему мои символы с диакритикой превращаются в вопросительные знаки?

Текст прошёл через кодовую страницу, которая не может их представить: столбец varchar без collation UTF-8 или строковый литерал без префикса N. Проверьте тип столбца и добавьте N к литералам. Текст, уже сохранённый с вопросительными знаками, восстановить нельзя.

Можно ли хранить эмодзи в SQL Server?

Да, в nvarchar с любой collation или в varchar с collation UTF-8. Используйте collation _SC, если хотите, чтобы LEN, SUBSTRING и похожие функции считали каждый эмодзи одним символом, а не двумя.

Какую collation выбрать для новой базы?

Latin1_General_100_CI_AS_SC для англоязычного или западноевропейского приложения с nvarchar или Latin1_General_100_CI_AS_SC_UTF8, если собираетесь использовать varchar в UTF-8. Используйте языковую collation, когда сортировка должна следовать правилам одного языка, например польского или турецкого.

Чувствителен ли SQL Server к регистру?

По умолчанию нет. Сравнения данных и имена объектов подчиняются collation, а collation по умолчанию нечувствительны к регистру. Collation с учётом регистра на уровне базы делает чувствительными к регистру и имена таблиц и столбцов, что застаёт врасплох код приложения, так что применяйте её к конкретным столбцам.