Блог

Если бы у меня была инструкция SQL, подобная этой:

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

И я использовал его в качестве табличной переменной для UDF в базе данных, у меня был бы достаточный объем. Но, допустим, я хотел вызвать скалярный UDF из другой базы данных на том же сервере, тогда я бы получил ошибку неизвестного типа.

Я попытался создать тип в вызывающей базе данных, но obv. тогда я получаю несоответствие типов, потому что, хотя у каждого из UDT одинаковое имя, они имеют разные области и, следовательно, разные типы.

Я знаю, что вы можете создавать типы CLR, регистрировать сборку на SQL Server, а затем получать универсальный доступ к пользовательскому типу.

Моя идея состоит в том, чтобы создать CLR UDT типа «ТАБЛИЦА», однако я не вижу, как это можно реализовать, поскольку я знаю, что он должен иметь тип CLR «SqlDbType.Structured»;

Мои вопросы:

1. Есть ли способ без использования CLR создать глобальную область видимости в SQL 2008 R2 для табличной переменной, и если нет…
2. Как я могу определить UDT в C # CLR, в котором UDT по сути является UDT «КАК ТАБЛИЦА»

Я знаю, что вы можете создавать типы 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) Как я могу определить UDT в C # CLR, в котором UDT по сути является UDT «КАК ТАБЛИЦА»

Вы не можете, поскольку это две отдельные вещи (т. Е. «Тип» и «Тип таблицы»), а не просто два разных средства реализации (т. Е. T-SQL UDF / хранимый процесс и SQLCLR UDF / хранимый процесс).

Редактировать:

На чисто техническом уровне можно использовать типы (типы таблиц и определяемые пользователем типы) для разных баз данных, но только путем переключения текущего контекста с помощью USE команды, которая используется только в ad hoc / dynamic 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