Funciones escalares 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 de Common Language Runtime (CLR) y elegir entre código administrado y Transact-SQL, consulte introducción a la integración de CLR.
Requisitos para funciones escalares 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, texto, ntext, imagen, marca de tiempo, tablao 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 Map CLR parameter data.
Al implementar un SVF de .NET Framework en un lenguaje de .NET Framework, puede especificar el atributo personalizado SqlFunction para incluir información adicional sobre la función. El atributo SqlFunction indica si la función accede o no a los datos, si es determinista y si la función implica operaciones de punto flotante.
Las funciones definidas por el usuario con valores escalares pueden ser deterministas o no deterministas. Una función determinista siempre devuelve el mismo resultado cuando se llama a con un conjunto específico de parámetros de entrada. Una función no determinista puede devolver resultados diferentes cuando se llama a con un conjunto específico de parámetros de entrada.
Nota:
No marque una función como determinista si la función no siempre genera los mismos valores de salida, dados los mismos valores de entrada y el mismo estado de 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. Para marcar una función como determinista, establezca 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 con valores CLR
Este es un SVF sencillo que accede a los 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();
}
}
}
La primera línea de código hace referencia Microsoft.SqlServer.Server para acceder a atributos y System.Data.SqlClient para acceder al espacio de nombres ADO.NET. (Este espacio de nombres contiene SqlClient, el proveedor de datos de .NET Framework para SQL Server).
A continuación, la función recibe el SqlFunction atributo personalizado, 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 ni 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) de System.Transactions.
La mayoría de las líneas de código del cuerpo de la función deben ser familiares para los desarrolladores que escriben aplicaciones cliente que usan los tipos que se encuentran en el espacio de nombres System.Data.SqlClient.
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();
}
El texto del comando adecuado se especifica inicializando el objeto SqlCommand. En el ejemplo anterior se cuenta el número de filas de la tabla SalesOrderHeader. A continuación, 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:
/t:library indica que se debe generar una biblioteca, en lugar de un ejecutable. Los ejecutables no se pueden registrar en SQL Server.
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
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.