Funções de valor escalar CLR

Artigo
05/07/2013

Uma SVF (função de valor escalar) retorna um valor único como, por exemplo, uma cadeia de caracteres, um inteiro ou um valor de bit. Desde do SQL Server 2005, é possível criar funções de valor escalar definidas pelo usuário em código gerenciado usando qualquer linguagem de programação do .NET Framework. Essas funções são acessíveis a Transact-SQL ou outro código gerenciado. Para obter informações sobre as vantagens da integração CLR e a escolha entre o código gerenciado e Transact-SQL, consulte Visão geral da integração CLR.

Requisitos das funções de valor escalar do CLR

As SVFs do .NET Framework são implementadas como métodos em uma classe de um assembly do .NET Framework. Os parâmetros de entrada e o tipo retornado de uma SVF podem ser qualquer um dos tipos de dados escalares para os quais o SQL Server oferece suporte, exceto varchar, char, rowversion, text, ntext, image, timestamp, table ou cursor. As SVFs devem garantir uma correspondência entre o tipo de dados do SQL Server e o tipo de dados de retorno do método de implementação. Para obter mais informações sobre conversões de tipo, consulte Mapeando dados de parâmetro CLR.

Ao implementar uma SVF do .NET Framework em uma linguagem do .NET Framework, o atributo personalizado SqlFunction pode ser especificado para incluir informações adicionais sobre a função. O atributo SqlFunction indica se a função acessa ou modifica os dados, se é determinística e se envolve operações de ponto flutuante.

Funções de valor escalar definidas pelo usuário podem ser determinísticas ou não. Uma função determinística sempre retorna o mesmo resultado quando chamada com um conjunto de parâmetros de entrada específico. Uma função não determinística pode retornar resultados diferentes quando chamada com um conjunto de parâmetros de entrada específico.

Observação
Não marque uma função como determinística se ela sempre produzir os mesmos valores de saída, considerando os mesmos valores de entrada e o mesmo estado do banco de dados. A marcação de uma função como determinística, quando a função, de fato, não é pode resultar em exibições indexadas e colunas computadas danificadas. Você marca uma função como determinística definindo a propriedade IsDeterministic como true.

Não marque uma função como determinística se ela sempre produzir os mesmos valores de saída, considerando os mesmos valores de entrada e o mesmo estado do banco de dados. A marcação de uma função como determinística, quando a função, de fato, não é pode resultar em exibições indexadas e colunas computadas danificadas. Você marca uma função como determinística definindo a propriedade IsDeterministic como true.

Parâmetros com valor de tabela

Os TVPs (parâmetros com valor de tabela), ou seja, tipos de tabela definidos pelo usuário transmitidos para um procedimento ou uma função, oferecem uma maneira eficiente de passar várias linhas de dados para o servidor. Os TVPs proporcionam funcionalidade semelhante para matrizes de parâmetro, porém com maior flexibilidade e integração com Transact-SQL. Eles também fornecem o potencial para melhor desempenho. Os TVPs também ajudam a reduzir o número de viagens de ida e volta para o servidor. Em vez de enviar várias solicitações ao servidor, como com uma lista de parâmetros escalares, os dados podem ser enviados ao servidor como um TVP. Um tipo de tabela definido pelo usuário não pode ser passado como um parâmetro com valor de tabela para, ou ser retornado de, um procedimento armazenado ou uma função gerenciada(o) que é executada(o) no processo do SQL Server. Para obter mais informações sobre TVPs, consulte Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados).

Exemplo de uma função de valor escalar do CLR

Eis uma SVF simples que acessa dados e retorna um valor inteiro:

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public class T
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static int ReturnOrderCount()
    {
        using (SqlConnection conn 
            = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(
                "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);
            return (int)cmd.ExecuteScalar();
        }
    }
}

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient

Public Class T
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _
    Public Shared Function ReturnOrderCount() As Integer
        Using conn As New SqlConnection("context connection=true")
            conn.Open()
            Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)
            Return CType(cmd.ExecuteScalar(), Integer)
        End Using
    End Function
End Class

A primeira linha de código referencia Microsoft.SqlServer.Server para acessar atributos e System.Data.SqlClient para acessar o namespace do ADO.NET. (Esse namespace contém SqlClient, o provedor de dados .NET Framework para SQL Server.)

Em seguida, a função recebe o atributo personalizado SqlFunction, encontrado no namespace Microsoft.SqlServer.Server. O atributo personalizado indica se a UDF (função definida pelo usuário) usa ou não o provedor em processo para ler dados no servidor. O SQL Server não permite que as UDFs atualizem, insiram ou excluam dados. O SQL Server pode otimizar a execução de uma UDF que não usa o provedor em processo. Isso é indicado com a definição de DataAccessKind como DataAccessKind.None. Na próxima linha, o método de destino é uma estática pública (compartilhada no Visual Basic .NET).

Dessa forma, a classe SqlContext, localizada no namespace Microsoft.SqlServer.Server, pode acessar um objeto SqlCommand com uma conexão com a instância do SQL Server já configurada. Embora não seja usado aqui, o contexto de transação atual também está disponível por meio da API System.Transactions.

A maior parte das linhas de código no corpo da função deve parecer familiar aos desenvolvedores que já escreveram aplicativos cliente que usam os tipos encontrados no namespace System.Data.SqlClient.

[C#]

using(SqlConnection conn = new SqlConnection("context connection=true")) 
{
   conn.Open();
   SqlCommand cmd = new SqlCommand(
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);
   return (int) cmd.ExecuteScalar();
}

[Visual Basic]

Using conn As New SqlConnection("context connection=true")
   conn.Open()
   Dim cmd As New SqlCommand( _
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)
   Return CType(cmd.ExecuteScalar(), Integer)
End Using

O texto de comando apropriado é especificado com a inicialização do objeto SqlCommand. O exemplo anterior conta o número de linhas na tabela SalesOrderHeader. Em seguida, o método ExecuteScalar do objeto cmd é chamado. Isso retorna um valor do tipo int com base na consulta. Por fim, a contagem da ordem retorna ao chamador.

Caso seja salvo em um arquivo chamado FirstUdf.cs, esse código pode ser compilado como assembly da seguinte forma:

[C#]

csc.exe /t:library /out:FirstUdf.dll FirstUdf.cs

[Visual Basic]

vbc.exe /t:library /out:FirstUdf.dll FirstUdf.vb

Observação
/t:library indica que uma biblioteca, e não um executável, deve ser produzida. Os executáveis não podem ser registrados no SQL Server.

Observação
Não há suporte para objetos de banco de dados do Visual C++ compilados com /clr:pure para a execução no SQL Server. Por exemplo, entre esses objetos de banco de dados estão funções de valor escalar.

A consulta Transact-SQL e uma invocação de exemplo de registro do assembly e da UDF são:

CREATE ASSEMBLY FirstUdf FROM 'FirstUdf.dll';
GO

CREATE FUNCTION CountSalesOrderHeader() RETURNS INT 
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount; 
GO

SELECT dbo.CountSalesOrderHeader();
GO

Observe que o nome da função como exposto em Transact-SQL não precisa corresponder ao nome do método estático público de destino.