Регистрация определяемых пользователем типов в SQL Server

Инструкция CREATE ASSEMBLY регистрирует сборку в базе данных, в которой требуется использование определяемого пользователем типа. После регистрации сборки она не имеет зависимостей.

Создание нескольких версий одной сборки в одной базе данных не допускается. Однако возможно создание нескольких версий одной сборки, зависящих от культуры данной базы данных. SQL Server разделяет несколько культурных версий сборки по разным именам, зарегистрированным в экземпляре SQL Server. Дополнительные сведения см. в разделе «Создание и использование сборок со строгими именами» пакета .NET Framework SDK.

Если инструкция CREATE ASSEMBLY выполняется с наборами разрешений SAFE или EXTERNAL_ACCESS, сборка проверяется на совместимость и безопасность типа. Если набор разрешений не указан, предполагается набор разрешений SAFE. Код с набором разрешений UNSAFE не проверяется. Дополнительные сведения о наборах разрешений сборки см. в разделе Конструирование сборок.

Примеры

USE AdventureWorks2008R2;
CREATE ASSEMBLY Point
FROM '\\ShareName\Projects\Point\bin\Point.dll' 
WITH PERMISSION_SET = SAFE;

USE AdventureWorks2008R2;
CREATE ASSEMBLY Point
FROM 0xfeac4 … 21ac78;

После загрузки сборки в базу данных можно создать тип с помощью инструкции CREATE TYPE языка Transact-SQL. Она добавляет тип в список доступных типов для этой базы данных. Тип имеет область базы данных и может использоваться только в той базе данных, в которой он был создан. Если определяемый пользователем тип уже существует в базе данных, то инструкция CREATE TYPE завершится с ошибкой.

Примечание
Синтаксис CREATE TYPE также используется для создания собственных псевдонимов типов данных SQL Server и предназначен для замены процедуры sp_addtype при создании псевдонимов типов данных. Некоторые из дополнительных аргументов в синтаксисе CREATE TYPE служат для создания определяемых пользователем типов и неприменимы для создания псевдонимов типов данных (например базового типа).

Примечание

Начиная с версии SQL Server 2005 в базе данных SQL Server с уровнем совместимости 80 нельзя создавать управляемые определяемые пользователем типы, хранимые процедуры, функции, статистические функции и триггеры. Чтобы сделать эти функции интеграции со средой CLR доступными в SQL Server, необходимо при помощи хранимой процедуры sp_dbcmptlevel (Transact-SQL) задать для базы данных уровень совместимости 100.

Дополнительные сведения см. в разделе CREATE TYPE (Transact-SQL).

Примеры

CREATE TYPE dbo.Point 
EXTERNAL NAME Point.[Point];

Следующая инструкция Transact-SQL должна выполняться в следующем порядке. Сначала необходимо удалить таблицу, которая обращается к определяемому пользователем типу Point, затем тип, и, наконец, сборку.

DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;

Если есть зависимые объекты, например таблицы с определениями столбцов определяемых пользователем типов, то инструкция DROP TYPE завершится с ошибкой. Также она завершится с ошибкой, если есть функции, хранимые процедуры или триггеры, созданные в базе данных с помощью предложения WITH SCHEMABINDING, или эти процедуры используют переменные и параметры определяемого пользователем типа. Сначала необходимо удалить все зависимые объекты, а затем выполнить инструкцию DROP TYPE.

Следующий запрос Transact-SQL определяет все столбцы и параметры в базе данных База данных AdventureWorks2008R2, в которых используется определяемый пользователем тип.

USE Adventureworks2008R2;
SELECT o.name AS major_name, o.type_desc AS major_type_desc
     , c.name AS minor_name, c.type_desc AS minor_type_desc
     , at.assembly_class
  FROM (
        SELECT object_id, name, user_type_id, 'SQL_COLUMN' AS type_desc
          FROM sys.columns
     UNION ALL
        SELECT object_id, name, user_type_id, 'SQL_PROCEDURE_PARAMETER'
          FROM sys.parameters
     ) AS c;
  JOIN sys.objects AS o
    ON o.object_id = c.object_id;
  JOIN sys.assembly_types AS at
    ON at.user_type_id = c.user_type_id;

Инструкция ALTER ASSEMBLY используется после внесения изменений в исходный код сборки определяемого пользователем типа и ее повторной компиляции. Она копирует DLL-файл на сервер и выполняет повторную привязку к новой сборке. Полный синтаксис см. в разделе ALTER ASSEMBLY (Transact-SQL).

Следующая инструкция ALTER ASSEMBLY языка Transact-SQL повторно загружает сборку Point.dll из указанного места на диске.

ALTER ASSEMBLY Point
FROM '\\Projects\Point\bin\Point.dll';

Предложение ADD FILE в синтаксисе инструкции ALTER ASSEMBLY отсутствует в инструкции CREATE ASSEMBLY. Оно обеспечивает возможность добавления исходного кода или любых других файлов, связанных со сборкой. Файлы копируются из исходных расположений и сохраняются в системных таблицах базы данных. Это обеспечивает постоянную доступность исходного кода или других файлов на тот случай, если возникнет необходимость повторного создания или документирования текущей версии определяемого пользователем типа.

Следующая инструкция ALTER ASSEMBLY языка Transact-SQL добавляет исходный код класса Point.cs для определяемого пользователем типа Point. В результате этого текст, содержащийся в файле Point.cs, будет скопирован и сохранен в базе данных с именем PointSource.

ALTER ASSEMBLY Point
ADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;

Сведения о сборке хранятся в таблице sys.assembly_files базы данных, в которой установлена сборка. Таблица sys.assembly_files содержит следующие столбцы.

• assembly_id
  Идентификатор, определенный для сборки. Это число назначается всем объектам, относящимся к одной сборке.

• name
  Имя объекта.

• file_id
  Идентификационный номер каждого из объектов. Первый объект, связанный с данным идентификатором сборки assembly_id, получает номер 1. Если существует несколько объектов, связанных с тем же идентификатором assembly_id, то каждое последующее значение file_id увеличивается на 1.

• content
  Шестнадцатеричное представление сборки или файла.

Для преобразования содержимого столбца content в доступный для чтения текст используется функция CAST или CONVERT. Следующий запрос преобразует содержимое файла Point.cs в доступный для чтения текст, используя для ограничения результирующего набора до одной строки имя в предложении WHERE.

SELECT CAST(content AS varchar(8000)) 
  FROM sys.assembly_files 
  WHERE name='PointSource';

При копировании и вставке результатов в текстовый редактор видно, что разделители строк и пробелы, существовавшие в исходном тексте, сохранились.

Определяемый пользователем тип Currency в образце базы данных База данных AdventureWorks2008R2 предоставляет полезный пример рекомендуемого способа структурирования определяемого пользователем типа и связанных с ним функций. Определяемый пользователем тип Currency используется для обработки денег на основе денежной системы определенной культуры и позволяет хранить различные типы валют, например: доллары, евро и т. п. Класс определяемого пользователем типа содержит культуру в виде строки, а количество денег — в виде типа данных decimal. Все необходимые методы сериализации содержатся внутри сборки, определяющей класс. Функция, реализующая конверсию валюты одной культуры в другую, внедряется в виде внешней функции с именем ConvertCurrency и располагается в отдельной сборке. Функция ConvertCurrency работает, получая курс конверсии из таблицы базы данных База данных AdventureWorks2008R2. Если изменится источник коэффициентов конверсии или в существующий код будут внесены другие изменения, сборку можно будет легко изменить, не затрагивая определяемый пользователем тип Currency.

Листинг кода для определяемого пользователем типа Currency и функции ConvertCurrency можно просмотреть, установив образцы среды CLR. Дополнительные сведения см. в разделе Вопросы установки образцов кода и образцов баз данных SQL Server.

Определяемые пользователем типы по определению находятся в одной базе данных. Таким образом, определяемый пользователем тип, созданный в одной базе данных, нельзя использовать в определении столбца другой базы данных. Чтобы использовать определяемые пользователем типы в нескольких базах данных, в каждой базе данных необходимо выполнить инструкции CREATE ASSEMBLY и CREATE TYPE для тех же сборок. Сборки считаются одинаковыми, если имеют одинаковое имя, строгое имя, культуру, версию, набор разрешений и двоичное содержимое.

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

• Вызываемые хранимые процедуры определены в различных базах данных.

• Запрашиваемые таблицы определены в различных базах данных.

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

В этих ситуациях любое преобразование, требуемое сервером, происходит автоматически. Эти преобразования нельзя выполнить явным образом с помощью функций CAST или CONVERT языка Transact-SQL.

Обратите внимание, что для использования определяемых пользователем типов при создании компонентом SQL Server Database Engine рабочих таблиц в системной базе данных tempdb не требуется предпринимать каких-либо действий. Это включает обработку курсоров, переменные таблицы и определяемые пользователем возвращающие табличное значение функции, которые включают определяемые пользователем типы и явно используют системную базу данных tempdb. Однако в случае явного создания временной таблицы, содержащей столбец определяемого пользователем типа, в системной базе данных tempdb данный тип должен быть зарегистрирован в системной базе данных tempdb способом, аналогичным пользовательской базе данных.