Правила сортировки изолированной базы данных

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

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

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

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

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

Неизолированные базы данных

Все базы данных имеют параметры сортировки по умолчанию (которые можно задать при создании или изменении базы данных). Этот параметр сортировки используется для всех метаданных в базе данных и по умолчанию для всех строковых столбцов в базе данных. Пользователи могут выбрать другие параметры сортировки для любого столбца с помощью предложения COLLATE.

Пример 1

Например, если бы мы работали в Пекине, мы могли бы использовать китайские правила сортировки:

ALTER DATABASE MyDB
    COLLATE Chinese_Simplified_Pinyin_100_CI_AS;

Теперь, если мы создадим столбец, его параметры сортировки по умолчанию — это китайская сортировка, но мы можем выбрать другой, если мы хотим:

CREATE TABLE MyTable
(
    mycolumn1 NVARCHAR,
    mycolumn2 NVARCHAR COLLATE Frisian_100_CS_AS
);
GO

SELECT name, collation_name
FROM sys.columns
WHERE name LIKE 'mycolumn%';
GO

Вот набор результатов.

name            collation_name
--------------- ----------------------------------
mycolumn1       Chinese_Simplified_Pinyin_100_CI_AS
mycolumn2       Frisian_100_CS_AS

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

Пример 2

Например, рассмотрим ранее показанную (китайскую) базу данных, когда она используется в экземпляре с параметрами сортировки Latin1_General.

CREATE TABLE T1 (T1_txt NVARCHAR (MAX));
GO

CREATE TABLE #T2 (T2_txt NVARCHAR (MAX));
GO

На первый взгляд эти две таблицы выглядят так же, как они имеют одну схему, но так как параметры сортировки баз данных отличаются, значения несовместимы:

SELECT T1_txt, T2_txt
FROM T1
     INNER JOIN #T2
         ON T1.T1_txt = #T2.T2_txt;

Вот набор результатов.

Сообщение 468, уровень 16, состояние 9, строка 2

Не удается устранить конфликт сортировки между "Latin1_General_100_CI_AS_KS_WS_SC" и "Chinese_Simplified_Pinyin_100_CI_AS" в операции равенства.

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

CREATE TABLE T1 (T1_txt NVARCHAR (MAX));
GO

CREATE TABLE #T2 (T2_txt NVARCHAR (MAX) COLLATE DATABASE_DEFAULT);
GO

SELECT T1_txt, T2_txt
FROM T1
     INNER JOIN #T2
         ON T1.T1_txt = #T2.T2_txt;

Этот запрос выполняется без ошибок.

Мы также можем наблюдать поведение, зависящее от параметров сортировки, при работе с переменными. Рассмотрим следующую функцию:

CREATE FUNCTION f (@x INT)
RETURNS INT
AS
BEGIN
    DECLARE @I AS INT = 1;
    DECLARE @İ AS INT = 2;
    RETURN @x * @i;
END

Это довольно необычная функция. В случае сортировки с учетом регистра выражение @i не может привязаться ни к одному из @I или @İ. При сортировке Latin1_General без учета регистра @i сопоставляется с @I, и функция возвращает 1. Но при нечувствительной к регистру турецкой сортировке @i привязывается к @İ, а функция возвращает 2. Это может негативно отразиться на базе данных, которая перемещается между экземплярами с различными параметрами сортировки.

Контейнеризованные базы данных

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

В изолированной базе данных сортировка каталога — Latin1_General_100_CI_AS_WS_KS_SC. Этот параметр сортировки одинаков для всех содержащихся баз данных во всех экземплярах SQL Server и не может быть изменен.

Параметры сортировки базы данных сохраняются, но используются только в качестве параметров сортировки по умолчанию для пользовательских данных. По умолчанию правила сортировки базы данных совпадают с правилами сортировки базы данных model, но пользователь может изменить их с помощью команды CREATE или ALTER DATABASE, как и в случае с несодержимыми базами данных.

В предложении CATALOG_DEFAULT доступно новое ключевое слово COLLATE. Используется как сокращённое обозначение для текущего сопоставления метаданных как в изолированных, так и в неизолированных базах данных. То есть в незаключенной базе данных CATALOG_DEFAULT возвращает текущий порядок сортировки базы данных, так как метаданные сопоставляются в порядке сортировки базы данных. В автономной базе данных эти два значения могут отличаться, так как пользователь может изменить параметры сортировки базы данных, чтобы он не соответствовал параметрам сортировки каталога.

В этой таблице приведена сводка поведения различных объектов как в неавтономных, так и в автономных базах данных:

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

CREATE TABLE T1 (T1_txt NVARCHAR (MAX));
GO

CREATE TABLE #T2 (T2_txt NVARCHAR (MAX));
GO

SELECT T1_txt, T2_txt
FROM T1
     INNER JOIN #T2
         ON T1.T1_txt = #T2.T2_txt;

Этот запрос работает, поскольку и T1_txt, и T2_txt учитываются в сортировке содержащей базы данных.

Взаимодействие между изолированными и неизолированными контекстами

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

• Параметры сортировки для пакета определяются базой данных, в которой пакет начинается.

Это решение принимается до выдачи каких-либо команд, включая начальное USE. То есть, если пакет начинается в содержащей базе данных, но первая команда — USE в несодержащейся базе данных, то для пакета по-прежнему используется содержащее поведение сортировки. Учитывая этот сценарий, ссылка на переменную может иметь несколько возможных результатов:

• Ссылка может обнаружить ровно одно совпадение. В этом случае ссылка работает без ошибок.

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

• Ссылка может найти несколько совпадений, которые изначально были различными. Это также вызывает ошибку.

Иллюстрируем это с помощью нескольких примеров. В данном случае предполагается наличие частично вложенной базы данных, в которой параметр сортировки базы данных MyCDB установлен по умолчанию Latin1_General_100_CI_AS_WS_KS_SC. Предположим, что параметры сортировки экземпляра являются Latin1_General_100_CS_AS_WS_KS_SC. Эти два правила сортировки различаются только чувствительностью к регистру.

Пример 1

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

USE MyCDB;
GO

CREATE TABLE #a (x INT);

INSERT INTO #a VALUES (1);
GO

USE master;
GO

SELECT * FROM #a;
GO

Results:

Вот набор результатов.

x
-----------
1

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

Пример 2

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

USE MyCDB;
GO

CREATE TABLE #a (x INT);

INSERT INTO #A VALUES (1);
GO

Здесь #A привязывается к #a при сортировке по умолчанию без учета регистра, и операция вставки выполняется,

Вот набор результатов.

(1 row(s) affected)

Но если мы продолжим сценарий...

USE master;
GO

SELECT * FROM #A;
GO

Мы получаем ошибку при попытке выполнить привязку к #A в случае сопоставления экземпляра с учетом регистра;

Вот набор результатов.

Msg 208, Level 16, State 0, Line 2
Invalid object name '#A'.

Пример 3

В следующем примере показан вариант, где ссылка обнаруживает несколько соответствий, которые прежде были различными. Во-первых, мы переходим в tempdb (в котором используются те же параметры сортировки с учетом регистра, что и в нашем экземпляре) и выполняем следующие операторы.

USE tempdb;
GO

CREATE TABLE #a (x INT);
GO

CREATE TABLE #A (x INT);
GO

INSERT INTO #a VALUES (1);
GO

INSERT INTO #A VALUES (2);
GO

Этот запрос удачно выполнен, так как таблицы отличаются в этом сопоставлении.

Вот набор результатов.

(1 row(s) affected)
(1 row(s) affected)

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

USE MyCDB;
GO

SELECT * FROM #a;
GO

Вот набор результатов.

Msg 12800, Level 16, State 1, Line 2
The reference to temp table name #a is ambiguous and cannot be resolved. Possible candidates are #a and #A.

Связанный контент

• Изолированные базы данных