Задание или изменение параметров сортировки столбца

Статья
05/23/2023

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Для типов данных char, varchar, text, nchar, nvarcharи ntext параметры сортировки базы данных можно переопределить, указав другие параметры сортировки для определенного столбца таблицы одним из следующих способов.

Предложение COLLATE в инструкциях CREATE TABLE и ALTER TABLE, как указано в примерах ниже.
- Преобразование на месте. Рассмотрим одну из существующих таблиц, определенных ниже:
```
-- NVARCHAR column is encoded in UTF-16 because a supplementary character enabled collation is used
CREATE TABLE dbo.MyTable (CharCol NVARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC);

-- VARCHAR column is encoded the Latin code page and therefore is not Unicode capable
CREATE TABLE dbo.MyTable (CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI);
```
  Чтобы преобразовать столбец на месте для использования UTF-8, выполните инструкцию ALTER COLUMN, которая задает требуемый тип данных и параметры сортировки с поддержкой UTF-8:
```
ALTER TABLE dbo.MyTable 
ALTER COLUMN CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8
```
  Этот метод прост в реализации, однако эта операция может вызывать блокировку, а это может стать проблемой для больших таблиц и занятых приложений.
- Копирование и замена. Рассмотрим одну из существующих таблиц, определенных ниже:
```
-- NVARCHAR column is encoded in UTF-16 because a supplementary character enabled collation is used
CREATE TABLE dbo.MyTable (CharCol NVARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC);
GO

-- VARCHAR column is encoded using the Latin code page and therefore is not Unicode capable
CREATE TABLE dbo.MyTable (CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI);
GO
```
  Чтобы преобразовать столбец для использования UTF-8, скопируйте данные в новую таблицу, где целевой столбец уже имеет требуемый тип данных и параметры сортировки с поддержкой UTF-8, а затем замените старую таблицу:
```
CREATE TABLE dbo.MyTableNew (CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8);
GO
INSERT INTO dbo.MyTableNew 
SELECT * FROM dbo.MyTable;
GO
DROP TABLE dbo.MyTable;
GO
EXEC sp_rename 'dbo.MyTableNew', 'dbo.MyTable';
GO
```
  Этот метод гораздо быстрее, чем преобразование на месте, но обработка сложных схем со множеством зависимостей (внешние ключи, первичные ключи, триггеры, DF) и синхронизация заключительного фрагмента таблицы (если база данных используется) требует больше планирования.
Дополнительные сведения см. в статье Collation and Unicode Support.
SQL Server Management Studio. Дополнительные сведения см. в разделе Изменение столбцов (ядро СУБД).
Использование свойства Column.Collation в объектах управления SQL Server (SMO).

Невозможно изменить параметры сортировки столбца, на который ссылаются:

вычисляемый столбец;
индекс;
статистика распределения, созданная автоматически либо с помощью инструкции CREATE STATISTICS;
ограничение CHECK;
ограничение FOREIGN KEY.

При работе с базой данных tempdbпредложение COLLATE содержит параметр database_default , указывающий, что столбец, находящийся во временной таблице, использует параметры сортировки по умолчанию для текущей базы данных пользователя для соединения, а не параметра сортировки базы данных tempdb.

Параметры сортировки и столбцы типа text

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

Параметры сортировки и база данных tempdb

База данных tempdb создается каждый раз при запуске SQL Server и имеет одинаковые параметры сортировки по умолчанию, что и база данных модели . Обычно они совпадают с параметрами сортировки экземпляра. Если при создании пользовательской базы данных указываются параметры сортировки, отличные от параметров сортировки базы данных model, то параметры сортировки пользовательской базы данных будут отличаться от базы данных tempdb. Все временные хранимые процедуры и временные таблицы создаются и сохраняются в базе данных tempdb. Это означает, что неявно столбцы временных таблиц и все константы, переменные и параметры хранимых процедур имеют параметры сортировки, отличные от сравниваемых объектов, создаваемых в постоянных таблицах и хранимых процедурах.

Это может привести к проблемам из-за различия параметров сортировки у объектов в пользовательских и системных базах данных. Например, экземпляр SQL Server использует параметры сортировки Latin1_General_CS_AS и выполняет следующие инструкции:

CREATE DATABASE TestDB COLLATE Estonian_CS_AS;  
USE TestDB;  
CREATE TABLE TestPermTab (PrimaryKey int PRIMARY KEY, Col1 nchar );

В рассматриваемой системе база данных tempdb использует параметры сортировки Latin1_General_CS_AS с кодовой страницей 1252, а база данных TestDB и столбец TestPermTab.Col1 — параметры сортировки Estonian_CS_AS с кодовой страницей 1257. Например:

USE TestDB;  
GO  
-- Create a temporary table with the same column declarations  
-- as TestPermTab  
CREATE TABLE #TestTempTab (PrimaryKey int PRIMARY KEY, Col1 nchar );  
INSERT INTO #TestTempTab  
         SELECT * FROM TestPermTab;  
GO

Согласно предыдущему примеру, база данных tempdb использует параметры сортировки Latin1_General_CS_AS, а база данных TestDB и столбец TestTab.Col1 параметры сортировки Estonian_CS_AS . Например:

SELECT * FROM TestPermTab AS a INNER JOIN #TestTempTab on a.Col1 = #TestTempTab.Col1;

Поскольку база данных tempdb использует параметры сортировки сервера по умолчанию, а TestPermTab.Col1 — другие параметры сортировки, SQL Server возвратит следующую ошибку: "Не удалось разрешить конфликт параметров сортировки между "Latin1_General_CI_AS_KS_WS" и "Estonian_CS_AS" в операции равенства".

Избавиться от этой ошибки можно одним из следующих способов:

укажите, что столбец временной таблицы использует параметры сортировки по умолчанию пользовательской базы данных, а не базы данных tempdb. Это позволит таблице работать с аналогичными таблицами в разных базах данных, если это необходимо;
```
CREATE TABLE #TestTempTab  
   (PrimaryKey int PRIMARY KEY,  
    Col1 nchar COLLATE database_default  
   );  
```

укажите правильные параметры сортировки в столбце #TestTempTab :

CREATE TABLE #TestTempTab  
   (PrimaryKey int PRIMARY KEY,  
    Col1 nchar COLLATE Estonian_CS_AS  
   );

См. также

Задание или изменение параметров сортировки сервера
Установка и изменение параметров сортировки базы данных
Поддержка параметров сортировки и Юникода

Поделиться через

Задание или изменение параметров сортировки столбца

Параметры сортировки и столбцы типа text

Параметры сортировки и база данных tempdb

См. также

Обратная связь

Дополнительные ресурсы