您能否创建 CLR UDT 以允许跨数据库共享表类型？

Question

如果我有这样的 SQL 语句：

CREATE TYPE [dbo].[typeRateLimitVariables] AS TABLE(
            [vchColumnName] [varchar](250) NULL,
            [decColumnValue] [decimal](25, 10) NULL
)

并且我将其用作数据库中 UDF 的表变量，那么我就有足够的范围。但是假设我想从同一服务器上的另一个数据库调用标量 UDF，那么我最终会遇到未知类型错误。

我尝试在调用数据库上创建类型，但是很明显。然后我得到类型不匹配，因为尽管每个 UDT 具有相同的名称，但它们具有不同的范围，因此是不同的类型。

我知道您可以创建 CLR 类型，将程序集注册到 SQL Server，然后通用访问自定义类型。

我的想法是创建一个“TABLE”类型的 CLR UDT，但是我看不出这是如何实现的，因为我知道它必须是“SqlDbType.Structured”的 CLR 类型；

我的问题是：

1. 有没有一种方法可以不使用 CLR 在 SQL 2008 R2 中为表变量创建全局作用域，如果没有...
2. 我如何在 C# CLR 中定义 UDT，其中 UDT 本质上是一个 UDT ”如表”

Answer 1

我知道您可以创建 CLR 类型，将程序集注册到 SQL Server，
然后通用访问自定义类型。

你确定吗？用户定义类型是数据库级别的对象，而不是服务器级别的。 “通用”访问它们的唯一方法是将程序集加载到每个数据库中并在每个数据库中创建用户定义类型。 MSDN 文档在 SQL Server 中注册用户定义类型：

跨数据库使用 UDT
根据定义，UDT 的范围仅限于单个
数据库。因此，在一个数据库中定义的 UDT 不能在另一个数据库中使用
另一个数据库中的列定义。为了在以下位置使用 UDT：
多个数据库，必须执行 CREATE ASSEMBLY 和 CREATE
每个数据库中相同程序集的 TYPE 语句。组件
如果它们具有相同的名称、强名称、
文化、版本、权限集和二进制内容。

一旦 UDT 在两个数据库中注册并可访问，您就可以
将一个数据库中的 UDT 值转换为在另一数据库中使用。完全相同的
UDT可以在以下场景中跨数据库使用：

• 调用不同数据库中定义的存储过程。
• 查询不同数据库中定义的表。
• 从一个数据库表 UDT 列中选择 UDT 数据并
  将其插入具有相同 UDT 列的第二个数据库中。

在这些情况下，会发生服务器所需的任何转换
自动地。您无法显式执行转换
使用 Transact-SQL CAST 或 CONVERT 函数。

回答您的具体问题：

1) 有没有一种方法可以不使用 CLR 在 SQL 2008 R2 中为表变量创建全局作用域，如果没有...

表类型和用户定义类型都不能跨数据库访问，在 CLR UDT 的一种情况下接受如 MSDN 文档中所述。

2) 如何在 C# CLR 中定义 UDT，其中 UDT 本质上是一个 UDT“AS TABLE”

您不能，因为它们是两个独立的事物（即“类型”与“表类型”），而不是仅仅两种不同的实现方式（即 T-SQL UDF / Stored Proc 与 SQLCLR UDF / Stored Proc）。

编辑：

在纯粹的技术层面上，可以跨数据库使用类型（表类型和用户定义类型），但只能通过< code>USE 命令仅在即席/动态 SQL 中可用。因此，这种用法在实际层面上的适用性有限，但仍然是可能的，如下例所示：

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO

USE [msdb];
GO

PRINT 'Creating [GlobalTableDef] Table Type in [msdb]...';
CREATE TYPE dbo.GlobalTableDef
AS TABLE
(
    [ID] INT NOT NULL IDENTITY(17, 22),
    [CreateDate] DATETIME NOT NULL DEFAULT (GETDATE()),
    [Something] NVARCHAR(2000) NULL
);
GO

PRINT 'Creating [TotalBytes] Function in [msdb]...';
GO
CREATE FUNCTION dbo.TotalBytes
(
    @TableToSummarize dbo.GlobalTableDef READONLY
)
RETURNS INT
AS
BEGIN
    DECLARE @TotalBytes INT = 0;

SELECT  @TotalBytes += (4 + 8 + DATALENGTH(COALESCE(tmp.Something, '')))
    FROM    @TableToSummarize tmp;

    RETURN @TotalBytes;
END;
GO

PRINT 'Testing the Table Type and Function...';
DECLARE @TmpTable dbo.GlobalTableDef;
INSERT INTO @TmpTable (Something) VALUES (N'this is a test');
INSERT INTO @TmpTable (Something) VALUES (NULL);
INSERT INTO @TmpTable (Something) VALUES (N'still seems to be a test');

SELECT * FROM @TmpTable;

SELECT dbo.TotalBytes(@TmpTable) AS [TotalBytesUsed];
GO

USE [tempdb];
GO
PRINT 'Creating [TypeTest] Proc in [tempdb]...';
GO

CREATE PROCEDURE dbo.TypeTest
AS
SET NOCOUNT ON;

    SELECT 1 AS [Step], DB_NAME() AS [CurrentDB];

    EXEC('
        SELECT 2 AS [Step], DB_NAME() AS [CurrentDB];
        USE [msdb];
        SELECT 3 AS [Step], DB_NAME() AS [CurrentDB];
        DECLARE @TmpTable dbo.GlobalTableDef;
        USE [tempdb];
        SELECT 4 AS [Step], DB_NAME() AS [CurrentDB];

        -- local query to prove context is tempdb
        SELECT TOP 5 * FROM sys.objects;

        INSERT INTO @TmpTable (Something) VALUES (N''this is a new test'');
        INSERT INTO @TmpTable (Something) VALUES (NULL);
        INSERT INTO @TmpTable (Something) VALUES (N''non-empty value'');
        INSERT INTO @TmpTable (Something) VALUES (NULL);
        INSERT INTO @TmpTable (Something) VALUES (N''woo-hoo!!!!!!!!!!!!!!!'');
        SELECT * FROM @TmpTable;

        SELECT [msdb].dbo.TotalBytes(@TmpTable) AS [TotalBytesUsed];
    ');

GO

USE [master];
GO

SELECT 5 AS [Step], DB_NAME() AS [CurrentDB];
EXEC tempdb.dbo.TypeTest;

--------------------------------

USE [tempdb];
GO
IF (OBJECT_ID(N'tempdb.dbo.TypeTest') IS NOT NULL)
BEGIN
    PRINT 'Dropping [TypeTest] Proc from [tempdb]...';
    DROP PROCEDURE dbo.TypeTest;
END;
GO

USE [msdb];
GO
IF (OBJECT_ID(N'dbo.TotalBytes') IS NOT NULL)
BEGIN
    PRINT 'Dropping [TotalBytes] Function from [msdb]...';
    DROP FUNCTION dbo.TotalBytes;
END;
GO

IF (EXISTS(
        SELECT  *
        FROM    sys.table_types stt
        WHERE   stt.name = N'GlobalTableDef'
    ))
BEGIN
    PRINT 'Dropping [GlobalTableDef] Table Type from [msdb]...';
    DROP TYPE dbo.GlobalTableDef;
END;
GO