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

Различные свойства влияют на порядок сортировки и семантику равенства текстовых данных, включая чувствительность к регистру, чувствительность к акцентам и основной язык, который используется. Эти качества выражаются в 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   
JOIN #T2   
    ON T1.T1_txt = #T2.T2_txt  

Результирующий набор:

Msg 468, Level 16, State 9, Line 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   
JOIN #T2   
    ON T1.T1_txt = #T2.T2_txt ;  

Теперь это работает без ошибок.

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

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

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

Автономные базы данных

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

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

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

Результирующий набор:

Сообщение 208, Уровень 16, Состояние 0, Строка 2

Недопустимое имя объекта "#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, уровень 16, состояние 1, строка 2

Ссылка на имя временной таблицы "#a" является неоднозначной и не может быть устранена. Возможные кандидаты : "#a" и "#A".

Заключение

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

См. также

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