Funções de valor escalar do CLR

Aplica-se a: SQL Server

Uma função com valor escalar (SVF) retorna um único valor, como uma cadeia de caracteres, um número inteiro ou um valor de bit. Você pode criar funções definidas pelo usuário com valor escalar em código gerenciado usando qualquer linguagem de programação do .NET Framework. Essas funções podem ser acessadas pelo Transact-SQL ou outro código gerenciado. Para obter informações sobre as vantagens da integração CLR e escolher entre 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 um SVF podem ser qualquer um dos tipos de dados escalares compatíveis com SQL Server, 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.

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. As TVPs fornecem funcionalidade semelhante às matrizes de parâmetros, mas oferecem maior flexibilidade e integração mais próxima com o 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 ou ser retornado de um procedimento armazenado gerenciado ou de uma função em execuçã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. (Este namespace contém SqlClient, o provedor de dados do .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).

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 que já está 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á criaram 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  

A consulta Transact-SQL e uma invocação de exemplo para registrar o assembly e a 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, conforme exposto no Transact-SQL, não precisa corresponder ao nome do método estático público de destino.

Confira também

Mapeando dados de parâmetro CLR
Visão geral dos atributos personalizados da integração CLR
Funções definidas pelo usuário
Acesso aos dados dos objetos de banco de dados CLR