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

  • 项目

适用于：SQL Server

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

使用 Visual Studio 部署 UDT

部署 UDT 的最简单方法是使用 Microsoft Visual Studio。 但是，对于更复杂的部署方案和最大的灵活性，请使用本主题后面部分所述的 Transact-SQL。

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

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

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

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

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

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

使用 Transact-SQL 部署 UDT

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

使用 ASSEMBLY

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

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

在权限集设置为 SAFE 或 EXTERNAL_ACCESS 的情况下执行 CREATE ASSEMBLY 时，将检查程序集，确保其可验证并且是类型安全的。 如果未指定权限集，则假定为 SAFE。 不检查权限集为 UNSAFE 的代码。 有关程序集的权限集的详细信息，请参阅设计程序集。

示例

以下 Transact-SQL 语句使用 SAFE 权限集在 AdventureWorks 数据库的 SQL Server 中注册 Point 程序集。 如果省略 WITH PERMISSION_SET 子句，将使用 SAFE 权限集注册该程序集。

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

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

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

使用 CREATE TYPE

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

注意

CREATE TYPE 语法还用于创建本机SQL Server别名数据类型，旨在取代sp_addtype作为创建别名数据类型的一种方式。 CREATE TYPE 语法中的某些可选参数与创建 UDT 有关，不适用于创建别名数据类型（如基类型）。

有关详细信息，请参阅 CREATE TYPE (Transact-SQL)。

示例

以下 Transact-SQL 语句创建 Point 类型。 EXTERNAL NAME 是使用 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 必须按以下顺序执行。 首先必须删除引用 Point UDT 的表，然后删除类型，最后删除程序集。

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

查找 UDT 依赖关系

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

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

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

在 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-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 函数将 内容 列的内容转换为可读文本。 以下查询将 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 和货币转换函数

AdventureWorks 示例数据库中的 Currency UDT 提供了构建 UDT 及其关联函数的建议方法的有用示例。 货币 UDT 用于根据特定文化的货币系统处理货币，并允许存储不同货币类型，例如美元、欧元等。 UDT 类将区域性名称公开为字符串，将金额公开为 十进制 数据类型。 所有必需的序列化方法都包括在定义该类的程序集中。 实现从一个区域性到另一个区域性的货币换算的函数作为名为 ConvertCurrency 的外部函数实现，此函数位于单独的程序集中。 ConvertCurrency 函数通过从 AdventureWorks 数据库中的表中检索转换率完成其工作。 如果转换率的源应更改，或者应该对现有代码进行任何其他更改，则可以轻松修改程序集，而不会影响 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。 但是，如果在 tempdb 中显式创建定义 UDT 列的临时表，则必须以与用户数据库相同的方式在 tempdb 中注册 UDT。

另请参阅

CLR 用户定义类型