Collation sql server что это

Изменение правил сопоставления (collation) и порядка сортировки в MS SQL 2005

Сопоставление (collation) в SQL — это ряд правил, согласно которым сортируются и сравниваются данные. Эти правила определяют порядок сортировки символьных данных, в зависимости от регистра, надстрочных знаков (акцента), символьных типов Kana, ширины символов.

1. Регистр
   Если «A» и «a»,» B» и «b», и т.д. считаются одинаковыми, это называется независимостью от регистра. Компьютер считает «A» и «a» различными символами, поскольку им соответствуют разные коды ASCII (ASCII-значение буквы «A» равно 65, в то время как «a» — 97).
2. Надстрочные знаки (акцент)
   Если «a» и «á», «o» и «ó» считаются одинаковыми, это называется нечувствительностью к акценту. Компьютер считает «a» и «á» разными, поскольку для них используются различные коды ASCII (ASCII значение «a» равно 97, а символа «á» — 225).
3. Символьные типы Kana
   Когда японские kana символы, Hiragana и Katakana, считаются разными, это называют Kana чувствительностью.
4. Ширина символов
   Когда однобайтный символ (полуширина) и тот же самый символ, представленный двумя байтами (полная ширина), считаются разными, это называется чувствительностью к ширине.

В MS SQL Server 2005 правила сопоставления можно задать на уровне:

• Сервера
• Базы данных
• Столбца
• Выражения

На уровне сервера правила сопоставления задаются во время первоначальной установки SQL сервера. Выбранное правило сопоставления применяется для системных баз данных. После того как правило сопоставления применено к любому объекту отличному от базы данных или столбца, вы не сможете изменить сопоставление, кроме как с помощью удаления и пересоздания объекта.

В SQL Server 2000 можно было изменить сопоставление на уровне сервера без переустановки сервера. Для этого надо запустить утилиту Rebuild Master (RebuildM.exe), которая расположена в папке Program Files\Microsoft SQL Server\80\Tools\BINN.

В SQL Server 2005 утилита RebuildM.exe не поддерживается. Поэтому для изменения сопоставления вам понадобится перестроить системную базу данных master с помощью параметра REBUILDDATABASE в Setup.exe. Для этого сделайте резервную копию БД, отсоедините все пользовательские БД и выполните:

start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=Cyrillic_General_CI_AS

INSTANCENAME — имя экземпляра SQL
SAPWD — пароль пользователя sa
SQLCOLLATION — новое сопоставление

Конфликты схем сопоставления (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, или если приложение, работающее с данными, разработано с учетом схем сопоставления предыдущих версий сервера.

На разных уровнях могут использоваться различные схемы сопоставления:

1. Уровень сервера. Схема сопоставления выбирается при установке сервера. Выбранная схема будет использована по умолчанию для всех системных баз и пользовательских баз данных, а также всех объектов каждой базы. При необходимости изменить схему на уровне сервера используется утилита Rebuild Master.
2. Уровень базы данных. Схему сопоставления можно указать при создании базы. Все объекты базы будут использовать эту схему по умолчанию. Также выбранная схема будет использоваться для символьных переменных и параметров. Изменить схему сопоставления базы данных можно при помощи команды ALTER DATABASE.
3. Уровень поля таблицы. При создании таблицы можно указать собственную схему сопоставления.

На уровне объектов базы (таблиц) схема не указывается.

Практическое применение

Как ни странно, на схемы сопоставления, как и на триггеры, часто не обращают должного внимания. Точнее – о схемах вспоминают только во время возникновения ошибки «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 на начальном этапе в соответствии с требованиями существующих приложений и стратегией развития системы в целом.

Эта статья опубликована в журнале RSDN Magazine #1-2005. Информацию о журнале можно найти здесь

SQL-Ex blog

Понимание коллации уровня базы данных и влияние её изменения

Добавил Sergey Moiseenko on Среда, 26 января. 2022

Когда вы разрабатываете приложение или пишете код в системе баз данных SQL, важно понимать, как будут сравниваться и сортироваться данные. Вы можете хранить ваши данные на конкретном языке, или вы можете захотеть, чтобы SQL Server различал, в каком регистре написаны данные. Microsoft в SQL Server предоставляет настройку, которая называется коллация или схема сопоставления (Collation) и отвечает за выполнение подобных требований.

Что такое коллация в SQL Server?

• Уровень сервера
• Уровень базы данных
• Уровень столбца
• Уровень выражения

Коллация уровня базы данных будет наследоваться из установок коллации уровня сервера, если вы не выберите какую-либо специфическую коллацию при создании базы данных. Вы также можете позже изменить коллацию уровня базы данных. Заметим, что изменение коллации базы данных будет применяться только к последующим или новым объектам, которые будут создаваться после изменения коллации.

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

Есть несколько способов это сделать. Один из них — копирование данных из существующей таблицы в новую, созданную с новой коллацией, а затем заменить старую таблицу на новую. Вы можете также переместить ваши табличные данные в новую базу данных, имеющую новую коллацию, и заметить старую базу данных новой.

Замечание. Изменение коллации — сложная задача, и следует избегать её, если это не диктуется требованиями бизнеса.

Как найти и изменить коллацию базы данных в SQL Server?

Давайте проверим коллацию экземпляра SQL Server и всех баз данных, находящихся в этом экземпляре. Вы можете проверить коллацию, получив доступ на уровне экземпляра или базы данных, в окне свойств (properties) в SQL Server Management Studio или просто выполняя нижеприведенных оператор T-SQL. Коллация для каждой базы данных хранится в системном объекте sys.databases — обратимся к нему, чтобы получить эту информацию.

--Проверяем коллацию базы данных 
SELECT name, collation_name 
FROM sys.databases 
GO 
--Проверяем коллацию уровня сервера или экземпляра 
SELECT SERVERPROPERTY('Collation') As [Instance Level Collation]

Я выполнил этот оператор T-SQL и получил следующий вывод. Вы можете видеть, что коллация всех баз данных и сервера имеет одни и те же настройки — SQL_Latin1_General_CP1_CI_AS. Это означает, что коллации баз данных были унаследованы от коллации уровня сервера при создании этих баз, и значение по умолчанию не менялось.

Теперь я покажу как проверить коллацию базы данных с помощью графического интерфейса в SQL Server Management Studio.

Сначала подключитесь к вашему экземпляру SQL Server, используя SQL Server Management Studio. Раскройте узел экземпляра, а затем папку Databases. Выполните щелчок правой кнопкой на выбранной базе данных и выберите Свойства (Properties):

Откроется окно свойств базы данных.

Теперь щелкните вкладку Опции (Options) на левой панели. Вы получите множество установок свойств в правой панели. Коллация — первое свойство на этой странице, как видно, оно имеет то же значение, которое было получено с помощью скрипта T-SQL.

Аналогично вы можете щелкнуть узел экземпляра SQL Server и выбрать в контекстном меню свойства уровня экземпляра, чтобы увидеть коллацию сервера.

Если вы хотите изменить эту коллацию на новую, вам просто нужно щелкнуть на выпадающем списке Collation и выбрать нужный вариант. Убедитесь, что вы сделали полный бэкап вашей базы данных перед этим.

Я выбрал для этой базы похожую коллацию с чувствительностью к регистру SQL_Latin1_General_CP1_CS_AS и щелкнул OK, чтобы применить её.

Замечание: убедитесь, что никто не подключен к базе данных во время этой процедуры; в противном случае, вам нужно переключиться в однопользовательский режим (single user) и изменить эту конфигурацию.

Вы можете также изменить коллацию базы данных с помощью оператора T-SQL. Для этого используйте предложение COLLATE в операторе ALTER DATABASE.

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

--Изменение коллации базы данных с помощью T-SQL 
USE master; 
GO 
Alter DATABASE [AdventureWorks2019] SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO 
ALTER DATABASE [AdventureWorks2019] 
COLLATE SQL_Latin1_General_CP1_CI_AS; 
GO 
Alter DATABASE [AdventureWorks2019] SET MULTI_USER

Перечень всех поддерживаемых коллаций в SQL Server

В этом разделе будет показано, как найти все доступные коллации в SQL Server. Сначала давайте покажем, как получить список всех поддерживаемых коллаций для экземпляра SQL Server.

В SQL Server имеется системная функция fn_helpcollations(), которую вы можете использовать для перечисления всех коллаций.

Выполните нижеприведенную команду для отображения списка.

--Отображение списка всех коллаций 
SELECT name, description FROM fn_helpcollations()

Мы можем видеть все 5508 поддерживаемых коллаций в области вывода. Если вы не уверены, какую коллацию выбрать, то можете использовать предложение WHERE, чтобы отфильтровать все возможные коллации, которые могут быть установлены для базы данных.

Допустим вам необходимо хранить данные на языке US English, и вы хотите, чтобы SQL Server обрабатывал их в чувствительном к регистру формате. Вы можете использовать команду ниже для перечисления списка всех возможных и поддерживаемых коллаций для вашего запроса:

--Выводит список всех коллаций с предложением WHERE 
SELECT Name, Description FROM fn_helpcollations() 
WHERE Name like 'SQL_Latin1%' AND Description LIKE '%case-sensitive%'

В выводе показаны только 10 коллаций, удовлетворяющих вашему запросу. Вы можете использовать этот скрипт для фильтрации различных коллаций.

Влияние изменения коллации базы данных на результаты запросов

В этом разделе я покажу разницу в выводе одного и того же запроса при его выполнении с различными коллациями.

Сначала я создам базу данных с именем MSSQL и коллацией SQL_Latin1_General_CP1_CS_AS. Затем я выполню дважды один и тот же запрос. Потом я изменю коллацию на SQL_Latin1_General_CP1_CI_AS и снова выполню те же запросы. Вы сможете сравнить оба результата и понять воздействие изменения коллациии базы данных. Итак, начнем с создания базы данных.

Откройте окно создания новой базы данных, показанное на картинке ниже. Вы можете также создать эту базу данных с помощью T-SQL. После этого вы можете увидеть имя базы данных и её файлы данных. Теперь щелкните вторую вкладку на левой панели, чтобы переключиться в окно свойств коллации.

Как видно, в качестве имени коллации для этой базы данных указано default. Это означает, что база данных будет наследовать тип коллации уровня сервера. Раскройте выпадающий список Collation, чтобы выбрать вашу новую коллацию.

Я выбрал для этой базы данных коллацию SQL_Latin1_General_CP1_CS_AS — не ту, которая выбирается по умолчанию. Для продолжения создания базы данных щелкните ОК.

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

В названии SQL_Latin1_General_CP1_CS_AS CS означает режим чувствительности к регистру (case-sensitive), а CI — режим нечувствительности к регистру (case-insensitive). Теперь вы можете выполнить либо нижеприведенный код T-SQL, либо любой другой код для получения вывода.

Я выполнил одну и ту же команду дважды. Первый скрипт фильтрует имена столбцов по значению SYS заглавными буквами, в то время как второй скрипт будет фильтровать те же столбцы по тому же значению, но строчными буквами — sys. Область вывода демонстрирует, что первый скрипт ничего не выводит, в то время как второй скрипт выводит результат из-за чувствительного к регистру поведения.

Select * from sysusers 
Where name='SYS' 
Go 
Select * from sysusers 
Where name='sys' 
GO

Теперь мы поменяем коллацию этой базы данных на нечувствительную к регистру коллацию SQL_Latin1_General_CP1_CI_AS, выполнив приведенные ниже операторы T-SQL. Вы можете также изменить её в окне свойств базы данных графического интерфейса SQL Server Management Studio.

--Изменение коллации базы данных на SQL_Latin1_General_CP1_CI_AS 
USE master; 
GO 
Alter DATABASE [MSSQL] SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO 
ALTER DATABASE [MSSQL] 
COLLATE SQL_Latin1_General_CP1_CI_AS; 
GO 
Alter DATABASE [MSSQL] SET MULTI_USER

Я выполнил этот скрипт, и коллация базы данных успешно поменялась на поддерживающую нечуствительность к регистру.

Вы можете проверить это изменение, выполнив следующие скрипты. Как видно на рисунке ниже, для этой базы данных была установлена новая коллация.

Мы снова выполним тот же оператор T-SQL, что и до изменения коллации, чтобы увидеть влияние этого изменения. Как видно, оба оператора T-SQL имеют вывод.

Заключение

Очевидно, что коллация в SQL Server имеет ключевое значение. Мы выяснили, какое влияние будет оказано, если вы измените коллацию на любом уровне в SQL Server. Всегда выполняйте надлежащее планирование и тестирование изменений.

В следующей статье я шаг за шагом покажу метод изменения коллации на уровне сервера.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Изменение параметра Collation(сортировка) MS SQL Server.

Collation(сортировка) — это правила согласно которым сортируются и сравниваются данные. Эти правила определяют порядок сортировки символьных данных.

• Для изменения параметра сортировки сервера после установки MS SQL SERVER необходимо выполнить следующую команду:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=Your_Instans /SQLSYSADMINACCOUNTS=Your_AdminLogin /SAPWD=Your_Password /SQLCOLLATION=Your_Collation

где
Your_Insta ns — экземпляр MS SQL SERVER по умолчанию MSSQLSERVER
Your_AdminLogin — Логин администратора, например sa
Your_Password — Пароль администратора
Your_Collation — Желаемая сортировка, например SQL_Latin1_General_CP1251_CI_AS

• Для изменения параметра сортировки базы данных необходимо выполнить следующий код:

ALTER DATABASE Your_Database_Name COLLATE Your_Collation

где
Your_Database_Name — Наименование базы данных
Your_Collation — Желаемая сортировка, например SQL_Latin1_General_CP1251_CI_AS