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

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

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

在权限集设置为 SAFE 或 EXTERNAL_ACCESS 的情况下执行 CREATE ASSEMBLY 时，将检查程序集，确保其可验证并且是类型安全的。如果未指定权限集，则假定为 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;

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

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

注意
从 SQL Server 2005 开始，在兼容级别为“80”的 SQL Server 数据库中，不能创建托管用户定义类型、存储过程、函数、聚合或触发器。若要利用 SQL Server 的这些 CLR 集成功能，必须使用 sp_dbcmptlevel (Transact-SQL) 存储过程将数据库兼容级别设置为“100”。

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

示例

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

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

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

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

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

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;

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

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

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

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 函数将 content 列的内容转换为可读文本。以下查询将 Point.cs 文件的内容转换为可读文本，查询中使用 WHERE 子句中的名称将结果集限定为一行。

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

如果将结果复制并粘贴到某一文本编辑器中，您将看到原件中的换行符和空格均得到保留。

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

通过安装公共语言运行时 (CLR) 示例可以找到 Currency UDT 和 ConvertCurrency 函数的代码列表。有关详细信息，请参阅安装 SQL Server 示例和示例数据库的注意事项。

根据定义，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。