Funciones escalares de CLR

Se aplica a: SQL Server

Una función con valores escalares (SVF) devuelve un valor único, como una cadena, un entero o un valor de bits. Puede crear funciones escalares definidas por el usuario en código administrado mediante cualquier lenguaje de programación de .NET Framework. Estas funciones son accesibles para Transact-SQL u otro código administrado. Para obtener información sobre las ventajas de la integración clR y elegir entre código administrado y Transact-SQL, consulte Introducción a la integración clR.

Requisitos de las funciones escalares de CLR

Las SVF de .NET Framework se implementan como métodos en una clase de un ensamblado de .NET Framework. Los parámetros de entrada y el tipo devuelto desde un SVF pueden ser cualquiera de los tipos de datos escalares admitidos por SQL Server, excepto varchar, char, rowversion, text, ntext, image, timestamp, table o cursor. Las SVFs deben garantizar una coincidencia entre el tipo de datos de SQL Server y el tipo de datos devuelto del método de implementación. Para obtener más información sobre las conversiones de tipos, vea Asignación de datos de parámetros CLR.

Al implementar una SVF de .NET Framework SVF en un lenguaje .NET Framework, el atributo personalizado SqlFunction se puede especificar para incluir la información adicional de la función. El atributo SqlFunction indica tanto si la función obtiene acceso o modifica los datos como si no, si es determinista y si la función implica las operaciones de coma flotante.

Las funciones escalares definidas por el usuario pueden ser deterministas o no deterministas. Una función determinista siempre devuelve el mismo resultado cuando se llama con un conjunto concreto de parámetros de entrada. Una función no determinista puede devolver resultados distintos cuando se llama con un conjunto concreto de parámetros de entrada.

Nota:

No marque una función como determinista si ésta no siempre genera los mismos valores de salida, dados los mismos valores de entrada y el mismo estado de la base de datos. Al marcar una función como determinista cuando la función no es verdaderamente determinista puede producir vistas indizadas dañadas y columnas calculadas. Marque una función como determinista estableciendo la propiedad IsDeterministic en true.

Parámetros con valores de tabla

Los parámetros con valores de tabla (TVP), tipos de tabla definidos por el usuario que se pasan a un procedimiento o función, proporcionan un modo eficaz de pasar varias filas de datos al servidor. Los TVP proporcionan una funcionalidad similar a las matrices de parámetros, pero ofrecen mayor flexibilidad e integración más estrecha con Transact-SQL. También proporcionan la posibilidad de obtener mayor rendimiento. Además, los TVP ayudan a reducir el número de ciclos de ida y vuelta al servidor. En lugar de enviar varias solicitudes al servidor, como con una lista de parámetros escalares, los datos pueden enviarse al servidor como un TVP. Un tipo de tabla definido por el usuario no se puede pasar como un parámetro con valores de tabla a un procedimiento almacenado administrado o una función que se ejecuta en el proceso de SQL Server. Para obtener más información sobre los TVP, vea Usar parámetros con valores de tabla (Motor de base de datos) .

Ejemplo de una función escalar de CLR

A continuación se muestra una SVF simple que tiene acceso a datos y devuelve un valor entero:

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  

La primera línea de código hace referencia a Microsoft.SqlServer.Server para tener acceso a los atributos y a System.Data.SqlClient para tener acceso al espacio de nombres de ADO.NET. (Este espacio de nombres contiene SqlClient, el proveedor de datos de .NET Framework para SQL Server).

Después, la función recibe el atributo personalizado SqlFunction , que se encuentra en el espacio de nombres Microsoft.SqlServer.Server . El atributo personalizado indica si la función definida por el usuario (UDF) utiliza o no el proveedor en proceso para leer los datos en el servidor. SQL Server no permite que las UDF actualicen, inserte o eliminen datos. SQL Server puede optimizar la ejecución de una UDF que no usa el proveedor en proceso. Esto se indica estableciendo DataAccessKind en DataAccessKind.None. En la línea siguiente, el método de destino es una estática pública (se comparte en Visual Basic .NET).

La clase SqlContext , ubicada en el espacio de nombres Microsoft.SqlServer.Server , puede tener acceso a un objeto SqlCommand con una conexión a la instancia de SQL Server que ya está configurada. Aunque no se usa aquí, el contexto de transacción actual también está disponible a través de la interfaz de programación de aplicaciones (API) System.Transactions .

Los desarrolladores que han escrito las aplicaciones cliente que usan los tipos situados en el espacio de nombres System.Data.SqlClient , deberían estar familiarizados con la mayoría de las líneas de código en el cuerpo de la función.

[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  

El texto de comando adecuado se especifica inicializando el objeto SqlCommand . En el ejemplo anterior se cuenta el número de filas en la tabla SalesOrderHeader. Después, se llama al método ExecuteScalar del objeto cmd . Esto devuelve un valor de tipo int basado en la consulta. Por último, se devuelve Order Count al autor de la llamada.

Si este código se guarda en un archivo denominado FirstUdf.cs, puede estar compilado en un ensamblado como se muestra a continuación:

[C#]

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

[Visual Basic]

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

Nota:

/t:library indica que se debe generar una biblioteca, en lugar de un ejecutable. Los ejecutables no se pueden registrar en SQL Server.

Nota:

Los objetos de base de datos de Visual C++ compilados con /clr:pure no se admiten para su ejecución en SQL Server. Por ejemplo, esos objetos de base de datos incluyen funciones escalares.

La consulta transact-SQL y una invocación de ejemplo para registrar el ensamblado y la UDF son:

CREATE ASSEMBLY FirstUdf FROM 'FirstUdf.dll';  
GO  
  
CREATE FUNCTION CountSalesOrderHeader() RETURNS INT   
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount;   
GO  
  
SELECT dbo.CountSalesOrderHeader();  
GO  
  

Tenga en cuenta que el nombre de la función tal como se expone en Transact-SQL no necesita coincidir con el nombre del método estático público de destino.

Consulte también

Asignar datos de parámetros CLR
Información general de los atributos personalizados de la integración CLR
Funciones definidas por el usuario
Acceso a datos de objetos de base de datos de CLR