通过

在 SQL Server 中注册用户定义的类型

适用范围：SQL Server

若要在 SQL Server 中使用用户定义的类型（UDT），必须注册它。 注册一个 UDT 涉及两个步骤：在要使用它的数据库中注册程序集和创建该类型。 UDT 的范围限定为单个数据库，不能在多个数据库中使用，除非向每个数据库注册相同的程序集和 UDT。 注册 UDT 程序集并创建类型后，可以在 Transact-SQL 和客户端代码中使用 UDT。 有关详细信息，请参阅 CLR 用户定义的类型。

使用 Visual Studio 部署 UDT

部署 UDT 的最简单方法是使用 Visual Studio。 但是，对于更复杂的部署方案以及最大的灵活性，请使用本文后面的 Transact-SQL。

遵照以下步骤使用 Visual Studio 创建和部署 UDT：

  1. 在 Visual Basic 或 Visual C# 语言节点中创建新的数据库项目。

  2. 添加对将包含 UDT 的 SQL Server 数据库的引用。

  3. 添加用户定义的类型类。

  4. 编写用于实现该 UDT 的代码。

  5. 在“生成”菜单中，选择“部署”。 这会注册程序集并在 SQL Server 数据库中创建类型。

使用 Transact-SQL 部署 UDT

Transact-SQL CREATE ASSEMBLY 语法用于在想要在其中使用 UDT 的数据库中注册程序集。 它内部存储在数据库系统表中，而不是在文件系统的外部存储。 如果 UDT 依赖于外部程序集，则必须将这些程序集也加载到数据库中。 CREATE TYPE 语句用于在要使用的数据库中创建 UDT。 有关详细信息，请参阅 CREATE ASSEMBLY 和 CREATE TYPE。

使用创建程序集

CREATE ASSEMBLY 语法在想要在其中使用 UDT 的数据库中注册程序集。 经过注册的程序集不具有任何依赖关系。

不允许在给定数据库中创建同一程序集的多个版本。 但是，可以根据给定数据库中的区域性创建同一程序集的多个版本。 SQL Server 按在 SQL Server 实例中注册的不同名称区分程序集的多个区域性版本。 有关详细信息，请参阅 创建和使用强名称程序集。

使用 SAFE 或 EXTERNAL_ACCESS 权限集执行 CREATE ASSEMBLY 时，将检查程序集以确保其可验证且类型安全。 如果省略指定权限集，则假定 SAFE。 未选中具有 UNSAFE 权限集的代码。 有关程序集权限集的详细信息，请参阅 设计程序集。

示例

以下 Transact-SQL 语句在 AdventureWorks2022 数据库中的 SQL Server 中注册 Point 程序集，并具有 SAFE 权限集。 如果省略 WITH PERMISSION_SET 子句，则程序集注册到 SAFE 权限集。

USE AdventureWorks2022;

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

以下 Transact-SQL 语句在 FROM 子句中使用 <assembly_bits> 参数注册程序集。 此 varbinary 值将文件表示为字节流。

USE AdventureWorks2022;
CREATE ASSEMBLY Point
FROM 0xfeac4 ... 21ac78

使用创建类型

将程序集加载到数据库中后，即可使用 Transact-SQL CREATE TYPE 语句创建类型。 这样即可将该类型添加到该数据库的可用类型列表中。 该类型的作用域为数据库，并且只能在创建该类型的数据库中使用。 如果数据库中已存在 UDT，则 CREATE TYPE 语句将失败并出现错误。

注意

CREATE TYPE 语法还用于创建本机 SQL Server 别名数据类型，旨在将 sp_addtype 替换为创建别名数据类型的方法。 CREATE TYPE 语法中的一些可选参数是指创建 UDT，不适用于创建别名数据类型（如基类型）。

有关详细信息，请参阅 CREATE TYPE。

示例

以下 Transact-SQL 语句创建 Point 类型。 EXTERNAL NAME 是使用 <assembly_name>.<udt_name>的两部分命名语法指定的。

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

从数据库中删除 UDT

DROP TYPE 语句从当前数据库中删除 UDT。 删除 UDT 后，可以使用 DROP ASSEMBLY 语句从数据库中删除程序集。

在以下情况下，DROP TYPE 语句不会执行：

  • 数据库中的表包含使用 UDT 定义的列。

  • 使用 WITH SCHEMABINDING 子句在数据库中创建的 UDT 变量或参数的函数、存储过程或触发器。

示例

以下 Transact-SQL 必须按以下顺序执行。 首先，必须删除引用 Point UDT 的表，然后删除类型，最后删除程序集。

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

查找 UDT 依赖项

如果存在依赖对象（如具有 UDT 列定义的表），则 DROP TYPE 语句将失败。 如果使用 WITH SCHEMABINDING 子句在数据库中创建的函数、存储过程或触发器，则这些例程使用用户定义的类型的变量或参数，则它还会失败。 必须先删除所有依赖对象，然后执行 DROP TYPE 语句。

以下 Transact-SQL 查询查找 AdventureWorks2022 数据库中使用 UDT 的所有列和参数。

USE AdventureWorks2022;

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
     INNER JOIN sys.objects AS o
         ON o.object_id = c.object_id
     INNER JOIN sys.assembly_types AS at
         ON at.user_type_id = c.user_type_id;

维护 UDT

在 SQL Server 数据库中创建 UDT 后，无法修改 UDT，尽管可以更改类型所基于的程序集。 在大多数情况下，必须使用 Transact-SQL DROP TYPE 语句从数据库中删除 UDT，对基础程序集进行更改，并使用 ALTER ASSEMBLY 语句重新加载它。 随后还需要重新创建该 UDT 和所有依赖对象。

示例

对 UDT 程序集中的源代码进行更改并重新编译后，将使用 ALTER ASSEMBLY 语句。 该语句将 .dll 文件复制到服务器，并且重新绑定到新程序集。 有关完整语法，请参阅 ALTER ASSEMBLY。

以下 Transact-SQL ALTER ASSEMBLY 语句从磁盘上的指定位置重新加载 Point.dll 程序集。

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

使用更改程序集添加源代码

ALTER ASSEMBLY 语法中的 ADD FILE 子句不存在于 CREATE ASSEMBLY中。 您可以使用该子句来添加源代码或与程序集关联的任何其他文件。 这些文件将从其原始位置复制并存储到数据库的系统表中。 如果您需要重新创建或记录 UDT 的当前版本，这样可确保源代码或其他文件随时备用。

以下 Transact-SQL ALTER ASSEMBLY 语句为 Point UDT 添加Point.cs类源代码。 这会复制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 函数将 content 列的内容转换为可读文本。 以下查询使用 WHERE 子句中的名称将 Point.cs 文件的内容转换为可读文本，以将结果集限制为单个行。

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

如果将结果复制并粘贴到文本编辑器中，则会看到原始文本编辑器中存在的换行符和空格将保留。

管理 UDT 和程序集

对 UDT 的实现进行计划时，应考虑 UDT 程序集本身所需的方法，以及应该在单独的程序集中创建并作为用户定义函数或存储过程实现的方法。 将方法分离为单独的程序集可以更新代码，而不会影响表的 UDT 列中可能存储的数据。 只有在新定义可以读取以前的值并且类型的签名不会更改时，才能修改 UDT 程序集，而无需删除 UDT 列和其他依赖对象。

将可能从实现 UDT 所需的代码中更改的过程代码分离可以大大简化维护。 仅包括 UDT 正常运行所需的代码，并尽可能简单地保留 UDT 定义，可降低 UDT 本身可能需要从数据库中删除代码修订或 bug 修复的风险。

货币 UDT 和货币换算函数

AdventureWorks2022 示例数据库中的 Currency UDT 提供了一个有用的示例，说明构建 UDT 及其关联函数的建议方法。 Currency UDT 用于根据特定文化的货币系统处理货币，并允许存储不同的货币类型，如美元、欧元等。 UDT 类将区域性名称公开为字符串，并将金额作为 十进制 数据类型公开。 所有必需的序列化方法都包括在定义该类的程序集中。 实现从一种区域性到另一种区域性的货币转换的函数作为名为 ConvertCurrency的外部函数实现，此函数位于单独的程序集中。 ConvertCurrency 函数通过从 AdventureWorks2022 数据库中的表检索转换率来执行其工作。 如果转换率的源应发生更改，或者如果对现有代码进行任何其他更改，则可以轻松修改程序集，而不会影响 Currency UDT。

可以通过安装公共语言运行时 （CLR） 示例找到 Currency UDT 和 ConvertCurrency 函数的代码列表。

跨数据库使用 UDT

根据定义，UDT 的作用域为单个数据库。 因此，在一个数据库中定义的 UDT 不能在另一个数据库的列定义中使用。 若要在多个数据库中使用 UDT，必须在相同的程序集上在每个数据库中执行 CREATE ASSEMBLY 和 CREATE TYPE 语句。 如果各个程序集具有相同的名称、强名称、区域性、版本、权限集和二进制内容，即可将它们视为相同。

一旦在两个数据库中注册了 UDT 并且均可从中访问它，即可将一个数据库的 UDT 值进行转换，用于另一个数据库。 可以在以下情况下跨数据库使用相同的 UDT：

  • 调用不同数据库中定义的存储过程。

  • 查询不同数据库中定义的表。

  • 选择一个数据库表 UDT 列中的 UDT 数据，然后将其插入另一个具有相同 UDT 列的数据库。

在上述情况下，自动执行服务器所需的任何转换。 无法使用 Transact-SQL CAST 或 CONVERT 函数显式执行转换。

当 SQL Server 数据库引擎在 tempdb 系统数据库中创建工作表时，无需执行任何使用 UDT 的操作。 这包括处理游标、表变量和用户定义的表值函数，这些函数包括 UDT，并透明地使用 tempdb。 但是，如果在定义 UDT 列的 tempdb 中显式创建临时表，则必须以与用户数据库相同的方式在 tempdb 注册 UDT。

相关内容

  • CLR 用户定义类型