我知道你可以创建CLR类型,将程序集注册到SQL Server,然后普遍访问自定义类型。
你确定吗?用户定义类型是数据库级别对象,而不是服务器级别。唯一的“普遍”访问方式是在每个数据库中加载程序集并创建用户定义类型。MSDN文档
Registering User-Defined Types in 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中为表变量创建全局作用域?如果没有...
除了MSDN文档中提到的CLR UDT之外,表类型和用户定义类型都无法跨数据库访问。
2)如何在C# CLR中定义UDT,其中UDT本质上是“AS TABLE”的UDT?
您不能这样做,因为它们是两个不同的东西(即“类型”与“表类型”),而不仅仅是两种不同的实现方式(即T-SQL UDF / Stored Proc与SQLCLR UDF / Stored Proc)。
编辑:
从纯技术角度来看,可以使用类型(表类型和用户定义类型)跨数据库使用,但只能通过使用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