Регистрация определяемых пользователем типов в SQL Server
Применимо к:
SQL Server
Чтобы использовать определяемый пользователем тип (UDT) в Microsoft SQL Server, его необходимо зарегистрировать. Регистрация определяемого пользователем типа включает регистрацию сборки и создание типа в базе данных, в которой его нужно использовать. Определяемые пользователем типы находятся в одной базе данных и не могут использоваться в нескольких базах данных, пока идентичная сборка и определяемый пользователем тип не будут зарегистрированы в каждой базе данных. После регистрации сборки определяемого пользователем типа и создания типа можно использовать определяемый пользователем тип в Transact-SQL и в клиентском коде. Дополнительные сведения об определяемых пользователем типах данных CLR см. в разделе Определяемые пользователем типы данных CLR.
Использование среды Visual Studio для развертывания определяемых пользователем типов
Самый простой способ развернуть определяемый пользователем тип — использовать Microsoft Visual Studio. Однако для более сложных сценариев развертывания и максимальной гибкости используйте Transact-SQL, как описано далее в этом разделе.
Для создания и развертывания определяемых пользователем типов с помощью среды Visual Studio выполните следующие шаги.
Создайте проект базы данных в узлах языка Visual Basic или Visual C# .
Добавьте ссылку на базу данных SQL Server, которая будет содержать определяемый пользователем тип.
Добавьте класс определяемого пользователем типа .
Напишите код для реализации определяемого пользователем типа.
В меню Сборка выберите Развернуть. При этом сборка регистрируется и создается тип в базе данных SQL Server.
Использование Transact-SQL для развертывания определяемых пользователем типов
Синтаксис Transact-SQL CREATE ASSEMBLY используется для регистрации сборки в базе данных, в которой вы хотите использовать определяемый пользователем тип. Он хранится внутри системных таблиц базы данных, а во внешней файловой системе. Если определяемый пользователем тип зависит от внешних сборок, их тоже необходимо загрузить в базу данных. Инструкция CREATE TYPE используется для создания определяемого пользователем типа в базе данных, в которой он будет использоваться. Дополнительные сведения см. в разделах CREATE ASSEMBLY (Transact-SQL) и CREATE TYPE (Transact-SQL).
Использование инструкции CREATE ASSEMBLY
Инструкция CREATE ASSEMBLY регистрирует сборку в базе данных, в которой требуется использование определяемого пользователем типа. После регистрации сборки она не имеет зависимостей.
Создание нескольких версий одной сборки в одной базе данных не допускается. Однако возможно создание нескольких версий одной сборки, зависящих от культуры данной базы данных. SQL Server различает несколько версий сборки и региональных параметров по разным именам, зарегистрированным в экземпляре SQL Server. Дополнительные сведения см. в разделе «Создание и использование сборок со строгими именами» пакета .NET Framework SDK.
Если инструкция CREATE ASSEMBLY выполняется с наборами разрешений SAFE или EXTERNAL_ACCESS, сборка проверяется на совместимость и безопасность типа. Если набор разрешений не указан, предполагается набор разрешений SAFE. Код с набором разрешений UNSAFE не проверяется. Дополнительные сведения о наборах разрешений сборки см. в разделе Проектирование сборок.
Пример
Следующая инструкция Transact-SQL регистрирует сборку Point в SQL Server в базе данных AdventureWorks с набором разрешений SAFE. Если предложение WITH PERMISSION_SET не указано, сборка регистрируется с набором разрешений SAFE.
USE AdventureWorks;
CREATE ASSEMBLY Point
FROM '\\ShareName\Projects\Point\bin\Point.dll'
WITH PERMISSION_SET = SAFE;
Следующая инструкция Transact-SQL регистрирует сборку с помощью <аргумента assembly_bits> в предложении FROM. Это значение varbinary представляет файл в виде потока байтов.
USE AdventureWorks;
CREATE ASSEMBLY Point
FROM 0xfeac4 ... 21ac78
Использование инструкции CREATE TYPE
После загрузки сборки в базу данных можно создать тип с помощью инструкции Transact-SQL CREATE TYPE. Она добавляет тип в список доступных типов для этой базы данных. Тип имеет область базы данных и может использоваться только в той базе данных, в которой он был создан. Если определяемый пользователем тип уже существует в базе данных, то инструкция CREATE TYPE завершится с ошибкой.
Примечание
Синтаксис CREATE TYPE также используется для создания собственных SQL Server псевдонимов типов данных и предназначен для замены sp_addtype в качестве средства создания псевдонимов типов данных. Некоторые из дополнительных аргументов в синтаксисе CREATE TYPE служат для создания определяемых пользователем типов и неприменимы для создания псевдонимов типов данных (например базового типа).
Дополнительные сведения см. в разделе CREATE TYPE (Transact-SQL).
Пример
Следующая инструкция Transact-SQL создает тип Point . Внешнее имя указывается с помощью двухкомпонентного синтаксиса именования AssemblyName. UDTName.
CREATE TYPE dbo.Point
EXTERNAL NAME Point.[Point];
Удаление определяемого пользователем типа из базы данных
Инструкция DROP TYPE удаляет определяемый пользователем тип из текущей базы данных. После удаления определяемого пользователем типа можно инструкцией DROP ASSEMBLY удалить сборку из базы данных.
Инструкция DROP TYPE не выполняется в следующих ситуациях.
Таблицы в базе данных, которые содержат столбцы, определенные с помощью определяемого пользователем типа.
Функции, хранимые процедуры или триггеры, которые используют переменные или параметры определяемого пользователем типа и созданы в базе данных с помощью предложения WITH SCHEMABINDING.
Пример
Следующая инструкция Transact-SQL должна выполняться в следующем порядке. Сначала необходимо удалить таблицу, которая ссылается на определяемый пользователем пользовательский тип, затем тип и, наконец, сборку.
DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;
Поиск зависимостей определяемого пользователем типа
Если есть зависимые объекты, например таблицы с определениями столбцов определяемых пользователем типов, то инструкция DROP TYPE завершится с ошибкой. Также она завершится с ошибкой, если есть функции, хранимые процедуры или триггеры, созданные в базе данных с помощью предложения WITH SCHEMABINDING, или эти процедуры используют переменные и параметры определяемого пользователем типа. Сначала необходимо удалить все зависимые объекты, а затем выполнить инструкцию DROP TYPE.
Следующий запрос Transact-SQL находит все столбцы и параметры, использующие определяемый пользователем тип в базе данных AdventureWorks .
USE Adventureworks;
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;
Обслуживание определяемых пользователем типов
Вы не можете изменить определяемый пользователем тип после его создания в базе данных SQL Server, хотя можно изменить сборку, на которой основан тип. В большинстве случаев необходимо удалить определяемый пользователем тип из базы данных с помощью инструкции Transact-SQL DROP TYPE, внести изменения в базовую сборку и перезагрузить ее с помощью инструкции ALTER ASSEMBLY. Затем необходимо повторно создать определяемый пользователем тип и зависимые объекты.
Пример
Инструкция ALTER ASSEMBLY используется после внесения изменений в исходный код сборки определяемого пользователем типа и ее повторной компиляции. Она копирует DLL-файл на сервер и выполняет повторную привязку к новой сборке. Полный синтаксис см. в разделе ALTER ASSEMBLY (Transact-SQL).
Следующая инструкция Transact-SQL ALTER ASSEMBLY перезагружает сборку Point.dll из указанного расположения на диске.
ALTER ASSEMBLY Point
FROM '\\Projects\Point\bin\Point.dll'
Использование инструкции ALTER ASSEMBLY для добавления исходного кода
Предложение ADD FILE в синтаксисе инструкции ALTER ASSEMBLY отсутствует в инструкции CREATE ASSEMBLY. Оно обеспечивает возможность добавления исходного кода или любых других файлов, связанных со сборкой. Файлы копируются из исходных расположений и сохраняются в системных таблицах базы данных. Это обеспечивает постоянную доступность исходного кода или других файлов на тот случай, если возникнет необходимость повторного создания или документирования текущей версии определяемого пользователем типа.
Следующая инструкция Transact-SQL ALTER ASSEMBLY добавляет исходный код класса 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
Шестнадцатеричное представление сборки или файла.
Для преобразования содержимого столбца содержимого в удобочитаемый текст можно использовать функцию CAST или CONVERT. Следующий запрос преобразует содержимое файла Point.cs в доступный для чтения текст, используя для ограничения результирующего набора до одной строки имя в предложении WHERE.
SELECT CAST(content AS varchar(8000))
FROM sys.assembly_files
WHERE name='PointSource';
При копировании и вставке результатов в текстовый редактор видно, что разделители строк и пробелы, существовавшие в исходном тексте, сохранились.
Управление определяемыми пользователем типами и сборками
При планировании реализации определяемых пользователем типов обдумайте, какие методы нужны в самой сборке определяемого пользователем типа, а какие нужно создать в отдельных сборках и реализовать в виде определяемых пользователем функций или хранимых процедур. Выделение методов в отдельные сборки позволяет обновлять код, не затрагивая данные, которые могут храниться в столбце определяемого пользователем типа таблицы. Сборки определяемого пользователем типа можно изменять без удаления столбцов и других зависимых объектов только в случае, если новое определение может считывать предыдущие значения, а подпись типа не изменена.
Выделение процедурного кода, который может отличаться от кода, требуемого для реализации определяемого пользователем типа, значительно упрощает обслуживание. Включение кода, необходимого для функционирования определяемого пользователем типа, и максимально возможное упрощение определений определяемого пользователем типа снижает риск необходимости удаления этого типа из базы данных для изменения кода или исправления ошибок.
Определяемый пользователем тип Currency и функция конвертации валюты
Определяемый пользователем тип валюты в образце базы данных AdventureWorks предоставляет полезный пример рекомендуемого способа структурирования определяемого пользователем типа и связанных с ним функций. Определяемый пользователем тип валюты используется для обработки денег на основе денежной системы определенной культуры и позволяет хранить различные типы валют, такие как доллары, евро и т. д. Определяемый пользователем класс предоставляет имя языка и региональных параметров в виде строки, а сумму денег — в виде десятичного типа данных. Все необходимые методы сериализации содержатся внутри сборки, определяющей класс. Функция, реализующая преобразование валют из одного языка и региональных параметров в другой, реализуется как внешняя функция с именем ConvertCurrency, и эта функция находится в отдельной сборке. Функция ConvertCurrency выполняет свою работу, извлекая скорость преобразования из таблицы в базе данных AdventureWorks . Если источник коэффициентов конверсии когда-либо должен измениться или в существующем коде должны быть какие-либо другие изменения, сборку можно легко изменить, не влияя на определяемый пользователем тип валюты .
Список кода для функций Currency UDT и ConvertCurrency можно найти, установив примеры среды CLR.
Использование определяемых пользователем типов в нескольких базах данных
Определяемые пользователем типы по определению находятся в одной базе данных. Таким образом, определяемый пользователем тип, созданный в одной базе данных, нельзя использовать в определении столбца другой базы данных. Чтобы использовать определяемые пользователем типы в нескольких базах данных, в каждой базе данных необходимо выполнить инструкции CREATE ASSEMBLY и CREATE TYPE для тех же сборок. Сборки считаются одинаковыми, если имеют одинаковое имя, строгое имя, культуру, версию, набор разрешений и двоичное содержимое.
После того как определяемый пользователем тип зарегистрирован и доступен в обеих базах данных, можно преобразовать значение данного типа из одной базы данных для использования в другой. Одинаковые определяемые пользователем типы могут использоваться в нескольких базах данных в следующих сценариях.
Вызываемые хранимые процедуры определены в различных базах данных.
Запрашиваемые таблицы определены в различных базах данных.
Выбор данных определяемого пользователем типа из столбца одной базы данных и вставка в столбец такого же типа другой базы данных.
В этих ситуациях любое преобразование, требуемое сервером, происходит автоматически. Вы не можете выполнить преобразования явным образом с помощью функций Transact-SQL CAST или CONVERT.
Обратите внимание, что не нужно предпринимать никаких действий для использования определяемых пользователем типов, когда SQL Server компонент Database Engine создает рабочие таблицы в системной базе данных tempdb. Сюда входит обработка курсоров, табличных переменных и определяемых пользователем функций с табличным значением, которые включают определяемые пользователем типы и прозрачно используют tempdb. Однако если вы явно создаете временную таблицу в базе данных tempdb , которая определяет столбец определяемого пользователем типа, то определяемый пользователем столбец должен быть зарегистрирован в базе данных tempdb так же, как и для пользовательской базы данных.
См. также:
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по