您能否创建一个 CLR UDT 以允许跨数据库共享表类型? [英] Can you create a CLR UDT to allow for a shared Table type across databases?

问题描述

如果我有这样的 SQL 语句:

If I had a SQL statement such as this:

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

我将它用作数据库中 UDF 的表变量，我有足够的范围.但是假设我想从同一台服务器上的另一个数据库调用标量 UDF，然后我会得到一个未知的类型错误.

And I used it as a table variable to a UDF in a database, I'd have sufficient scope. BUt let's say I wanted to call the scalar UDF from another database on the same server, then I'd end up with an unknown type error.

我已经尝试在调用数据库上创建类型，但是 obv.然后我得到一个类型不匹配，因为虽然每个 UDT 具有相同的名称，但它们具有不同的范围，因此是不同的类型.

I've tried creating the type on the calling DB, but obv. then I get a type mismatch because although each of the UDTs have the same name, they have different scopes and therefore are different types.

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

I know you can create CLR types, register the assembly to SQL Server, and then access the custom type universally.

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

My idea is to create a CLR UDT of type "TABLE", however I can't see how this can be implemented, as I know it must be of CLR type "SqlDbType.Structured";

我的问题是:

1. 有没有办法不使用 CLR 在 SQL 2008 R2 中为表变量创建全局范围，如果没有...
2. 如何在 C# CLR 中定义 UDT，其中 UDT 本质上是一个 UDTAS TABLE"

推荐答案

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

I know you can create CLR types, register the assembly to SQL Server, and then access the custom type universally.

你确定吗?用户定义类型是数据库级对象，而不是服务器级.通用"访问它们的唯一方法是将程序集加载到每个数据库中并在每个数据库中创建用户定义类型.关于 在 SQL Server 中注册用户定义的类型:

Are you sure about this? User-Defined Types are database-level objects, not server-level. The only way to access them "universally" is by loading the Assembly into each of the databases and creating the User-Defined Type in each database. This much is stated in the MSDN documentation for Registering User-Defined Types in SQL Server:

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

Using UDTs Across Databases
UDTs are by definition scoped to a single database. Therefore, a UDT defined in one database cannot be used in a column definition in another database. In order to use UDTs in multiple databases, you must execute the CREATE ASSEMBLY and CREATE TYPE statements in each database on identical assemblies. Assemblies are considered identical if they have the same name, strong name, culture, version, permission set, and binary contents.

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

Once the UDT is registered and accessible in both databases, you can convert a UDT value from one database for use in another. Identical UDTs can be used across databases in the following scenarios:

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

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

In these situations, any conversion required by the server occurs automatically. You are not able to perform the conversions explicitly using the Transact-SQL CAST or CONVERT functions.

回答您的具体问题:

1) 有没有办法不使用 CLR 在 SQL 2008 R2 中为表变量创建全局范围，如果没有...

1) Is there a way without using the CLR to create global scope in SQL 2008 R2 for a table variable, and if not...

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

Neither Table Types nor User-Defined Types are accessible across databases, accept in the one case for CLR UDTs as noted above in the MSDN documenation.

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

2) How can I define a UDT in C# CLR, in which the UDT is essentially a UDT "AS TABLE"

你不能，因为它们是两个独立的东西(即类型"与表类型")，而不仅仅是两种不同的实现方式(即 T-SQL UDF/存储过程 vs SQLCLR UDF/存储过程).

You cannot as those are two separate things (i.e. a "Type" vs a "Table Type") as opposed to being just two different means of implementation (i.e. T-SQL UDF / Stored Proc vs SQLCLR UDF / Stored Proc).

在纯技术层面上，可以跨数据库使用类型(表类型和用户定义类型)，但只能通过 USE 命令，该命令仅可用于即席/动态 SQL.因此，这种用法在实际层面上的适用性有限，但仍然可以如以下示例所示:

On a purely technical level, it is possible to use Types (Table Types and User-Defined Types) across databases, but only by switching the current context via the USE command which is only usable in ad hoc / dynamic SQL. Hence, this usage has limited applicability on a practical level, but nonetheless it is still possible as the following example shows:

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

这篇关于您能否创建一个 CLR UDT 以允许跨数据库共享表类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！