SQL-Ex blog

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

Вы можете установить коллацию на разных уровнях в SQL Server, а именно:

• Уровень сервера


• Уровень базы данных


• Уровень столбца


• Уровень выражения

Коллацию уровня сервера иногда называют коллацией уровня экземпляра 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. Всегда выполняйте надлежащее планирование и тестирование изменений.

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