Конфликты схем сопоставления (collation) в Microsoft SQL Server 2000
Лимонов Алексей
Введение
Обработка и хранение символьных данных на сервере MS SQL 2000 осуществляется при помощи схем сопоставления (collation). Схемы содержат шаблоны каждого символа, правила сортировки и сравнения. В предыдущих версиях сервера MS SQL необходимо было отдельно указывать кодовую страницу и порядок сортировки символьных данных, причем эти настройки действовали сразу на все объекты сервера. В MS SQL 2000 схемы сопоставления позволили более гибко подходить к работе с текстовыми данными. В данной статье рассматриваются основные принципы работы схем, а также их применение в российских условиях.
Назначение collation
Символьные данные
В машинном представлении любой символ или знак представляет различные комбинации битов. Соответственно, использование одного байта для хранения символа дает возможность определить до 256 различных символов. Если увеличить объем данных до двух байт, появится возможность распознавать 65 536 символов.
Кодовая страница есть ни что иное, как набор различных комбинации состояний битов (всего 256) в байтовой структуре. Эти комбинации определяют символы верхнего и нижнего регистров, цифры, специальные символы. При переносе данных между компьютерными системами с различными кодовыми страницами необходимо выполнить преобразование. Символы, битовая комбинация которых отсутствует в схеме назначения, в результате будут потеряны.
Для устранения подобных ситуаций международная организация стандартов ISO и группа Unicode разработали стандарт Unicode.
Порядок сортировки определяет правила сравнения и представления символов. Например, символ «а» больше символа «б». Кроме того, порядок сортировки устанавливает правила сопоставления символов верхнего и нижнего регистров.
Описание схем сопоставления
Схемы сопоставления collation определяют способы хранения и обработки символьных данных сервера. Каждая схема устанавливает:
порядок сортировки для данных с кодировкой Unicode;
порядок сортировки для данных с кодировкой не-Unicode;
кодовую страницу для данных с кодировкой не-Unicode.
Для MS SQL 2000 не надо указывать все три параметра, достаточно выбрать имя схемы и порядок сортировки.
На сервере реализованы две группы схем – Windows collations и SQL collations. Первая группа схем сопоставления реализована на сервере для поддержки региональных настроек Windows. Рекомендуется работать именно с этой группой. Вторая группа, SQL collations, используется для совместимости с предыдущими версиями сервера MS SQL. Ее выбор может быть оправдан, если вы планируете обмениваться данными с серверами MS SQL 6.5 или MS SQL 7.0, или если приложение, работающее с данными, разработано с учетом схем сопоставления предыдущих версий сервера.
На разных уровнях могут использоваться различные схемы сопоставления:
Уровень сервера. Схема сопоставления выбирается при установке сервера. Выбранная схема будет использована по умолчанию для всех системных баз и пользовательских баз данных, а также всех объектов каждой базы. При необходимости изменить схему на уровне сервера используется утилита Rebuild Master.
Уровень базы данных. Схему сопоставления можно указать при создании базы. Все объекты базы будут использовать эту схему по умолчанию. Также выбранная схема будет использоваться для символьных переменных и параметров. Изменить схему сопоставления базы данных можно при помощи команды ALTER DATABASE.
Уровень поля таблицы. При создании таблицы можно указать собственную схему сопоставления.
На уровне объектов базы (таблиц) схема не указывается.
Практическое применение
Как ни странно, на схемы сопоставления, как и на триггеры, часто не обращают должного внимания. Точнее – о схемах вспоминают только во время возникновения ошибки «Cannot resolve collation conflict». Для решения возникающих проблем необходимо понимать причины их возникновения и пути их возможного решения.
Рассмотрим вариант работы на ОС Windows 2000 Server с региональными настройками Russian. При установке MS SQL 2000 программа предлагает установить схему collation Cyrillic_General_CI_AS. Первая часть схемы «Cyrillic_General» определяет кодовую страницу. Далее идут правила сортировки, например, CI (case-insensitive) – нечувствительная к регистру, AS (accent-sensitive) – чувствительная к ударению. Можно получить полный список схем сопоставления с расшифровкой, выполнив запрос
select * from ::fn_helpcollations()
При выборе Cyrillic_General_CI_AS все системные базы данных, в том числе база TempDB, будут использовать именно эту схему сравнения. Как указано выше, все вновь создаваемые пользовательские базы и таблицы по умолчанию будут иметь точно такую же схему. Однако ничто не мешает при установке выбрать другую схему и так же с ней работать.
Когда вы работаете в рамках одной структуры collation, проблем не возникает. Чаще всего они появляются, когда вы присоединяете или устанавливаете базу с другой схемой сопоставления. В большинстве случаев это схема SQL_Latin1_General_CP1251_CI_AS. Она представляет собой схему сопоставления вида SQL collations, доставшуюся в наследство от версии MS SQL 7.0. К примеру, указанная схема устанавливается, если вы выполняете обновление сервера или переносите БД с версии MS SQL 7.0 на MS SQL 2000. Здесь следует отметить, что хоть по смыслу схемы SQL_Latin1_General_CP1251_CI_AS и Cyrillic_General_CI_AS схожи, на самом деле для сервера это различные схемы сопоставления. И при их одновременном использовании сложно избежать ошибок.
Для примера рассмотрим ситуацию, когда сервер установлен с collation Cyrillic_General_CI_AS, есть база данных NEW_BASE с серверной схемой сопоставления Cyrillic_General_CI_AS, и база данных OLD_BASE для работы со старым приложением со схемой SQL_Latin1_General_CP1251_CI_AS. За базу NEW_BASE можно не беспокоиться – в рамках серверной схемы сопоставления все запросы будут корректно обрабатывать символьные данные. Другое дело, когда необходимы данные из OLD_BASE.
Ошибка «Cannot resolve collation conflict» будет появляться:
При соединениях JOIN или UNION с таблицами из базы с другой схемой сопоставления.
При работе с временными таблицами в контексте рабочей базы данных. Временные таблицы создаются в базе TempDB, где, как было уже отмечено, используется серверная схема сопоставления, и символьные данные в этом случае корректно сравнить не удается.
Самый общий случай – когда пытаются сравнить значения символьных полей разных схем сопоставления (даже в пределах одной таблицы или базы данных).
Сообщение об ошибке говорит само за себя – сервер не в состоянии сравнить символы из различных схем сопоставления. Решение напрашивается следующее: привести данные к одной схеме collation.
Если в запросах к БД OLD_BASE идет работа с временными таблицами, либо переменными табличного типа, то при их создании надо явно указывать нужную схему collation для каждого символьного поля. Например:
create table #t
(f1 int not null,
f2 char(5) collate SQL_Latin1_General_CP1251_CI_AS,
f3 varchar(150) collate SQL_Latin1_General_CP1251_CI_AS)
Далее, выполнить соединение между полями с различными схемами напрямую нельзя. Соответственно, нельзя сделать JOIN или UNION для таблиц с различными схемами collation из одной или разных баз. Иначе опять будет выдано сообщение об ошибке. В этом случае объединяемые поля также необходимо привести к одной схеме при помощи преобразования схемы сопоставлений. Допустим, соединение таблиц OLD_BASE и NEW_BASE можно выполнить так:
select *
from NEW_BASE.dbo.Report as A
join OLD_BASE.dbo.Report as B
on A.char_key = B.char_key collate Cyrillic_General_CI_AS
а запрос на объединение так:
select int_data, date_data, char_key
from NEW_BASE.dbo.Report
union all
select int_data, date_data, char_key collate Cyrillic_General_CI_AS
from OLD_BASE.dbo.Report
Преобразование схем сопоставления полей можно делать в различных вариантах соединений. Но писать каждый раз подобные запросы, с явным указанием схемы collation – не самое лучшее времяпровождение. Тогда можно рассмотреть вариант приведения всех баз к единой схеме – серверной. Для изменения схемы collation, используемой в БД по умолчанию, служит команда
alter database OLD_BASE collate Cyrillic_General_CI_AS
Однако это еще не изменит схему для символьных полей в базе. Менять их нужно либо вручную через Enterprise Manager, либо написать подобный запрос:
alter table Report alter column char_key char(5) collate Cyrillic_General_CI_AS
При этом имеется ряд ограничений – нельзя изменить схемы для вычисляемых полей, индексированных полей, полей с ограничением CHECK или внешних ключей. Необходимо вначале удалить их, а после изменения схемы сопоставления заново создать. Так что работа здесь может быть проделана большая и серьезная.
Если вы не в состоянии привести новую базу к серверной схеме, и у вас нет возможности менять код в приобретенном приложении – надо менять серверную схему и схему всех ваших баз данных (опять-таки, если это не приведет к остановке работы других приложений и баз). Самый надежный и простой способ замены серверной схемы – переустановка всего сервера, что в принципе равносильно использованию утилиты Rebuild Master. После этого надо воссоздать структуры баз (но не данные в них!) уже с новой схемой collation, затем импортировать данные в обновленную структуру.
Если старая БД привязана к определенной схеме collation, а новая база использует иную схему, то остается один способ – поставить новый сервер или установить именованный экземпляр (instance) SQL-сервера. Правда, еще не ясно, сколько уйдет ресурсов на реализацию именованной установки сервера, и поддерживает ли приобретенное приложение вообще такую конфигурацию. Вполне возможно, что проще будет установить отдельный сервер со своей схемой collation на отдельной машине.
Заключение
Как вы могли убедиться – выбор схемы сопоставления может существенно повлиять на разработку и сопровождение серверных решений. Поэтому необходимо определится с оптимальным выбором схемы collation на начальном этапе в соответствии с требованиями существующих приложений и стратегией развития системы в целом.
Список литературы
Для подготовки данной работы были использованы материалы с сайта http://www.rsdn.ru/