适用对象:yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库 若要在使用用户定义类型 (UDT) Microsoft SQL Server,必须将其注册。 注册一个 UDT 涉及两个步骤:在要使用它的数据库中注册程序集和创建该类型。 UDT 的作用域仅限单个数据库,不能在多个数据库中使用,除非在各个数据库中注册相同的程序集和 UDT。 一旦注册 UDT 程序集并创建了该类型,即可在 Transact-SQL 和客户端代码中使用 UDT。 有关详细信息,请参阅 CLR 用户定义类型。
使用 Visual Studio 部署 UDT
部署 UDT 的最简单方法是使用 Microsoft Visual Studio。 不过,对于更为复杂的部署方案,同时为尽量提高灵活性,应使用本主题下文介绍的 Transact-SQL。
遵照以下步骤使用 Visual Studio 创建和部署 UDT:
-
创建一个新数据库项目中Visual Basic或Visual C# 语言节点。
-
向将要包含该 UDT 的 SQL Server 数据库添加引用。
-
添加用户定义类型类。
-
编写用于实现该 UDT 的代码。
-
从构建菜单中,选择部署。 这样即可在 SQL Server 数据库中注册该程序集并创建该类型。
使用 Transact-SQL 部署 UDT
使用 Transact-SQL CREATE ASSEMBLY 语法可以在您希望使用 UDT 的数据库中注册程序集。 该程序集存储在内部的数据库系统表中,而不是存储在外部的文件系统中。 如果 UDT 依赖于外部程序集,则必须将这些程序集也加载到数据库中。 使用 CREATE TYPE 语句可以在要使用 UDT 的数据库中创建该 UDT。 有关详细信息,请参阅创建程序集(TRANSACT-SQL) 和CREATE TYPE (-)。
使用 ASSEMBLY
使用 CREATE ASSEMBLY 语法可以在要使用 UDT 的数据库中注册程序集。 经过注册的程序集不具有任何依赖关系。
不允许在给定数据库中创建同一程序集的多个版本。 不过,可以基于给定数据库的区域性创建同一程序集的多个版本。 SQL Server 通过在 SQL Server 实例中注册的不同程序集名称来区分程序集的多个区域性版本。 有关详细信息,请参阅 .NET Framework SDK 中的“创建和使用具有强名称的程序集”。
在权限集设置为 SAFE 或 EXTERNAL_ACCESS 的情况下执行 CREATE ASSEMBLY 时,将检查程序集,确保其可验证并且是类型安全的。 如果未指定权限集,则假定为 SAFE。 不检查权限集为 UNSAFE 的代码。 有关程序集的权限集的详细信息,请参阅设计程序集。
示例
以下Transact-SQL语句注册中的点程序集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 语句创建该类型。 这样即可将该类型添加到该数据库的可用类型列表中。 该类型的作用域为数据库,并且只能在创建该类型的数据库中使用。 如果数据库中已存在 UDT,CREATE TYPE 语句将失败,并且生成错误。
Note:
CREATE TYPE 语法还用于创建本机SQL Server别名数据类型,以及用来取代sp_addtype作为一种创建别名数据类型。 CREATE TYPE 语法中的某些可选参数与创建 UDT 有关,不适用于创建别名数据类型(如基类型)。
有关详细信息,请参阅CREATE TYPE (TRANSACT-SQL)。
示例
以下Transact-SQL语句将创建点类型。 使用的两部分命名语法指定外部名称AssemblyName。UDTName。
CREATE TYPE dbo.Point
EXTERNAL NAME Point.[Point];
从数据库中删除 UDT
使用 DROP TYPE 语句从当前数据库中删除 UDT。 删除了 UDT 之后,可使用 DROP ASSEMBLY 语句从数据库中删除程序集。
在以下情况下不执行 DROP TYPE 语句。
-
数据库中的表包含使用 UDT 定义的列。
-
在数据库中使用 WITH SCHEMABINDING 子句创建了使用 UDT 变量或参数的函数、存储过程或触发器。
示例
以下 Transact-SQL 必须按以下顺序执行。 第一个表引用点必须删除 UDT,然后的类型,最后将程序集。
DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;
查找 UDT 依赖关系
如果存在依赖对象,如具有 UDT 列定义的表,DROP TYPE 语句将失败。 如果存在在数据库中用 WITH SCHEMABINDING 子句创建的函数、存储过程或触发器,且这些例程使用用户定义类型的变量或参数,该语句也将失败。 在执行 DROP TYPE 语句之前,首先必须删除所有依赖对象。
以下Transact-SQL查询找到的所有列和参数使用 UDT 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;
维护 UDT
尽管可以更改 UDT 所基于的程序集,但是一旦在 SQL Server 数据库中创建了该 UDT 就无法再修改它。 多数情况下,必须使用 Transact-SQL DROP TYPE 语句从数据库中删除 UDT、更改基础程序集,然后使用 ALTER ASSEMBLY 语句重新加载它。 随后还需要重新创建该 UDT 和所有依赖对象。
示例
在更改 UDT 程序集中的源代码并且重新编译该程序集后,可以使用 ALTER ASSEMBLY 语句。 该语句将 .dll 文件复制到服务器,并且重新绑定到新程序集。 有关完整语法,请参阅ALTER ASSEMBLY (TRANSACT-SQL)。
以下 Transact-SQL ALTER ASSEMBLY 语句从指定的磁盘位置重新加载 Point.dll 程序集。
ALTER ASSEMBLY Point
FROM '\\Projects\Point\bin\Point.dll'
使用 ALTER ASSEMBLY 添加源代码
CREATE ASSEMBLY 中不存在 ALTER ASSEMBLY 语法中的 ADD FILE 子句。 您可以使用该子句来添加源代码或与程序集关联的任何其他文件。 这些文件将从其原始位置复制并存储到数据库的系统表中。 如果您需要重新创建或记录 UDT 的当前版本,这样可确保源代码或其他文件随时备用。
以下Transact-SQLALTER ASSEMBLY 语句将添加的 Point.cs 类源代码点UDT。 这会复制 Point.cs 文件中包含的文本并用名称“PointSource”将其存储在数据库中。
ALTER ASSEMBLY Point
ADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;
程序集信息存储在sys.assembly_files已经安装该程序集在数据库中的表。 Sys.assembly_files表包含以下列。
assembly_id
为程序集定义的标识符。 此编号分配到与同一程序集相关的所有对象。
名称
对象的名称。
file_id
一个数字,指明每个对象,与关联的第一个对象给定assembly_id该值为 1。 如果有多个对象具有相同关联assembly_id,然后每个后续file_id值加 1。
内容
程序集或文件的十六进制表示形式。
可以使用 CAST 或 CONVERT 函数将转换的内容内容为可读文本的列。 以下查询将 Point.cs 文件的内容转换为可读文本,查询中使用 WHERE 子句中的名称将结果集限定为一行。
SELECT CAST(content AS varchar(8000))
FROM sys.assembly_files
WHERE name='PointSource';
如果将结果复制并粘贴到某一文本编辑器中,您将看到原件中的换行符和空格均得到保留。
管理 UDT 和程序集
对 UDT 的实现进行计划时,应考虑 UDT 程序集本身所需的方法,以及应该在单独的程序集中创建并作为用户定义函数或存储过程实现的方法。 通过将方法分开放入不同的程序集,您可以更新代码,而不会影响可能存储在表的 UDT 列中的数据。 仅当新定义可以读取以前值并且类型签名未更改时,才可以修改 UDT 程序集,而无需删除 UDT 列和其他依赖对象。
将可能发生更改的过程代码与实现 UDT 所需的代码分开,可以极大地简化维护工作。 仅包括启用 UDT 所需的代码并且尽量简化 UDT 定义,这将降低出于代码修订或修复错误的目的可能需要从数据库中删除 UDT 本身的风险。
Currency UDT 和货币转换函数
货币中的 UDT AdventureWorks示例数据库提供了有用的建议方法为构建 UDT 及其关联的函数的示例。 货币UDT 用于处理基于特定区域性的货币体系的货币,并且允许存储不同的货币类型,如美元、 欧元等。 UDT 类将区域性名称公开为一个字符串,并作为货币金额十进制数据类型。 所有必需的序列化方法都包括在定义该类的程序集中。 实现到另一个区域性的货币转换函数作为名为的外部函数实现ConvertCurrency,并且此函数位于单独的程序集。 ConvertCurrency函数执行其工作,通过从表中检索汇率AdventureWorks数据库。 如果发生变化的转换率的源,或如果应该对现有代码的任何其他更改,该程序集可以轻松地修改而不会影响货币UDT。
代码清单货币UDT 并ConvertCurrency函数可通过安装公共语言运行时 (CLR) 示例。
跨数据库使用 UDT
根据定义,UDT 的作用域为单个数据库。 因此,在一个数据库中定义的 UDT 不能用在另一个数据库的列定义中。 要在多个数据库中使用 UDT,必须对各个数据库中的相同程序集执行 CREATE ASSEMBLY 和 CREATE TYPE 语句。 如果各个程序集具有相同的名称、强名称、区域性、版本、权限集和二进制内容,即可将它们视为相同。
一旦在两个数据库中注册了 UDT 并且均可从中访问它,即可将一个数据库的 UDT 值进行转换,用于另一个数据库。 可以在以下情况下跨数据库使用相同的 UDT:
-
调用不同数据库中定义的存储过程。
-
查询不同数据库中定义的表。
-
选择一个数据库表 UDT 列中的 UDT 数据,然后将其插入另一个具有相同 UDT 列的数据库。
在上述情况下,自动执行服务器所需的任何转换。 您无法使用 Transact-SQL CAST 或 CONVERT 函数显式执行转换。
请注意,不需要采取任何操作即可使用 Udt 时SQL Server 数据库引擎中创建工作表tempdb系统数据库。 这包括处理游标、 表变量和用户定义表值函数,包括 Udt 并且透明地使利用tempdb。 但是,如果显式创建中的临时表tempdb ,用于定义 UDT 列,则必须在注册 UDT tempdb用户数据库一样。
请参阅
历史资料。此文对应 2018-11-30 的中文文档快照,适用产品版本和命令行为以原文为准。
来源与署名:Microsoft 和文档贡献者(原始作者元数据 rothja)。固定版本原文。文档采用 CC BY 4.0,示例代码采用 MIT 授权。本站展开固定版本仓库内的引用内容,将 Markdown 转为 HTML,规范化链接,移除外部媒体、发布器占位标签和导航标记。
