Compartir a través de

Funciones con valores de tabla clR

  • Artículo

Se aplica a:SQL Server

Una función con valores de tabla es una función definida por el usuario que devuelve una tabla.

SQL Server amplía la funcionalidad de las funciones con valores de tabla al permitirle definir una función con valores de tabla en cualquier lenguaje administrado. Los datos se devuelven desde una función con valores de tabla a través de un objeto IEnumerable o IEnumerator.

Para las funciones con valores de tabla, las columnas del tipo de tabla de retorno no pueden incluir columnas de marca de tiempo ni columnas de tipo de datos de cadena no Unicode (como char, varchary texto). No se admite la restricción NOT NULL.

Diferencias entre las funciones con valores de tabla de Transact-SQL y CLR

Las funciones con valores de tabla de Transact-SQL materializan los resultados de llamar a la función en una tabla intermedia. Puesto que utilizan una tabla intermedia, pueden admitir restricciones e índices únicos en los resultados. Estas características pueden ser útiles cuando se devuelven resultados grandes.

En cambio, las funciones con valores de tabla de Common Language Runtime (CLR) representan una alternativa de streaming. No es necesario que todo el conjunto de resultados se materialice en una sola tabla. El plan de ejecución de la consulta llama directamente al objeto IEnumerable devuelto por la función administrada que llama a la función con valores de tabla y los resultados se consumen de forma incremental. Este modelo de transmisión por secuencias se asegura de que los resultados puedan consumirse inmediatamente después de que la primera fila esté disponible, en lugar de tener que esperar a que se rellene toda la tabla. También es una alternativa mejor si tiene un gran número de filas devueltas, ya que no tienen que materializarse en memoria en su conjunto. Por ejemplo, puede utilizarse una función con valores de tabla administrada para analizar un archivo de texto y devolver cada línea como una fila.

Implementación de funciones con valores de tabla

Implemente funciones con valores de tabla como métodos en una clase en un ensamblado de .NET Framework. El código de función con valores de tabla debe implementar la interfaz IEnumerable. La interfaz IEnumerable se define en .NET Framework. Los tipos que representan matrices y colecciones en .NET Framework ya implementan la interfaz IEnumerable. De esta forma resulta más fácil escribir funciones con valores de tabla que conviertan una colección o una matriz en un conjunto de resultados.

Parámetros con valores de tabla

Los parámetros con valores de tabla son tipos de tabla definidos por el usuario que se pasan a un procedimiento o función, y proporcionan un modo eficaz de pasar varias filas de datos al servidor. Los parámetros con valores de tabla 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.

Los parámetros con valores de tabla también ayudan a reducir el número de viajes de ida y vuelta (round trip) al servidor. En lugar de enviar varias solicitudes al servidor, como en el caso de una lista de parámetros escalares, los datos pueden enviarse al servidor como un parámetro con valores de tabla. 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 parámetros con valores de tabla, vea Usar parámetros con valores de tabla (motor de base de datos).

Parámetros de salida y funciones con valores de tabla

Es posible que la información se devuelva de funciones con valores de tabla mediante parámetros de salida. El parámetro correspondiente en el código de implementación de la función con valores de tabla debe usar un parámetro de paso por referencia como argumento. Visual Basic .NET no admite parámetros de salida de la misma manera que C#. Debe especificar el parámetro por referencia y aplicar el atributo <Out()> para representar un parámetro de salida, como en el ejemplo siguiente:

Imports System.Runtime.InteropServices
...
Public Shared Sub FillRow ( <Out()> ByRef value As SqlInt32)

Definición de una función con valores de tabla en Transact-SQL

La sintaxis para definir una función con valores de tabla CLR es similar a la de una función con valores de tabla de Transact-SQL, con la adición de la cláusula EXTERNAL NAME. Por ejemplo:

CREATE FUNCTION GetEmpFirstLastNames()
RETURNS TABLE (
    FirstName NVARCHAR (4000),
    LastName NVARCHAR (4000)
)
AS EXTERNAL NAME MyDotNETAssembly.[MyNamespace.MyClassname].GetEmpFirstLastNames;

Las funciones con valores de tabla se utilizan para representar datos en formato relacional y procesarlos posteriormente en consultas como:

SELECT *
FROM func();

SELECT *
FROM tbl
     INNER JOIN func() AS f
         ON tbl.col = f.col;

SELECT *
FROM tbl AS t
CROSS APPLY func(t.col);

Las funciones con valores de tabla pueden devolver una tabla cuando:

  • Se crean a partir de argumentos de entrada escalares. Por ejemplo, una función con valores de tabla que toma una cadena de números delimitados por comas y los dinamiza en una tabla.

  • Se genera a partir de datos externos. Por ejemplo, una función con valores de tabla que lee el registro de eventos y lo expone como una tabla.

Nota:

Una función con valores de tabla solo puede realizar el acceso a datos a través de una consulta de Transact-SQL en el método InitMethod y no en el método FillRow. El InitMethod debe marcarse con la propiedad de atributo SqlFunction.DataAccess.Read si se realiza una consulta Transact-SQL.

Una función con valores de tabla de ejemplo

La siguiente función con valores de tabla devuelve información del registro de eventos del sistema. La función toma un único argumento de cadena que contiene el nombre del registro de eventos que va a leerse.

Código de ejemplo

  • de C#
  • de Visual Basic para .NET 
using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;

public class TabularEventLog
{
    [SqlFunction(FillRowMethodName = "FillRow")]
    public static IEnumerable InitMethod(String logname)
    {
        return new EventLog(logname).Entries;
    }

    public static void FillRow(Object obj, out SqlDateTime timeWritten, out SqlChars message, out SqlChars category, out long instanceId)
    {
        EventLogEntry eventLogEntry = (EventLogEntry)obj;
        timeWritten = new SqlDateTime(eventLogEntry.TimeWritten);
        message = new SqlChars(eventLogEntry.Message);
        category = new SqlChars(eventLogEntry.Category);
        instanceId = eventLogEntry.InstanceId;
    }
}

Declarar y usar la función con valores de tabla de ejemplo

Una vez compilada la función con valores de tabla de ejemplo, se puede declarar en Transact-SQL de la siguiente manera:

USE master;
-- Replace SQL_Server_logon with your SQL Server user credentials.

GRANT EXTERNAL ACCESS ASSEMBLY TO [SQL_Server_logon];

-- Modify the following line to specify a different database.
ALTER DATABASE master
SET TRUSTWORTHY ON;

-- Modify the next line to use the appropriate database.
CREATE ASSEMBLY tvfEventLog
    FROM 'D:\assemblies\tvfEventLog\tvfeventlog.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION ReadEventLog
(@logname NVARCHAR (100))
RETURNS TABLE (
    logTime DATETIME,
    Message NVARCHAR (4000),
    Category NVARCHAR (4000),
    InstanceId BIGINT)
AS EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod;
GO

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, estos objetos de base de datos incluyen las funciones con valores de tabla.

Para probar el ejemplo, pruebe el siguiente código de Transact-SQL:

-- Select the top 100 events,
SELECT TOP 100 *
FROM dbo.ReadEventLog(N'Security') AS T;
GO

-- Select the last 10 login events.
SELECT TOP 10 T.logTime,
              T.Message,
              T.InstanceId
FROM dbo.ReadEventLog(N'Security') AS T
WHERE T.Category = N'Logon/Logoff';
GO

Ejemplos

Los ejemplos de código de Transact-SQL de este artículo usan la base de datos de ejemplo de AdventureWorks2022 o AdventureWorksDW2022, que puede descargar de la página principal ejemplos de Microsoft SQL Server y proyectos de comunidad.

Un. Devolver los resultados de una consulta de SQL Server

En el ejemplo siguiente se muestra una función con valores de tabla que consulta una base de datos de SQL Server.

Asigne a su archivo de código fuente el nombre FindInvalidEmails.cs o FindInvalidEmails.vb.

  • de C#
  • de Visual Basic para .NET 
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions {
   private class EmailResult {
      public SqlInt32 CustomerId;
      public SqlString EmailAdress;

      public EmailResult(SqlInt32 customerId, SqlString emailAdress) {
         CustomerId = customerId;
         EmailAdress = emailAdress;
      }
   }

   public static bool ValidateEmail(SqlString emailAddress) {
      if (emailAddress.IsNull)
         return false;

      if (!emailAddress.Value.EndsWith("@adventure-works.com"))
         return false;

      // Validate the address. Put any more rules here.
      return true;
   }

   [SqlFunction(
       DataAccess = DataAccessKind.Read,
       FillRowMethodName = "FindInvalidEmails_FillRow",
       TableDefinition="CustomerId int, EmailAddress nvarchar(4000)")]
   public static IEnumerable FindInvalidEmails(SqlDateTime modifiedSince) {
      ArrayList resultCollection = new ArrayList();

      using (SqlConnection connection = new SqlConnection("context connection=true")) {
         connection.Open();

         using (SqlCommand selectEmails = new SqlCommand(
             "SELECT " +
             "[CustomerID], [EmailAddress] " +
             "FROM [AdventureWorksLT2022].[SalesLT].[Customer] " +
             "WHERE [ModifiedDate] >= @modifiedSince",
             connection)) {
            SqlParameter modifiedSinceParam = selectEmails.Parameters.Add(
                "@modifiedSince",
                SqlDbType.DateTime);
            modifiedSinceParam.Value = modifiedSince;

            using (SqlDataReader emailsReader = selectEmails.ExecuteReader()) {
               while (emailsReader.Read()) {
                  SqlString emailAddress = emailsReader.GetSqlString(1);
                  if (ValidateEmail(emailAddress)) {
                     resultCollection.Add(new EmailResult(
                         emailsReader.GetSqlInt32(0),
                         emailAddress));
                  }
               }
            }
         }
      }

      return resultCollection;
   }

   public static void FindInvalidEmails_FillRow(
       object emailResultObj,
       out SqlInt32 customerId,
       out SqlString emailAdress) {
      EmailResult emailResult = (EmailResult)emailResultObj;

      customerId = emailResult.CustomerId;
      emailAdress = emailResult.EmailAdress;
   }
};

Compile el código fuente en una DLL y copie la DLL en el directorio raíz de su unidad C. A continuación, ejecute la siguiente consulta de Transact-SQL.

USE AdventureWorksLT2022;
GO

IF EXISTS (SELECT name
           FROM sysobjects
           WHERE name = 'FindInvalidEmails')
    DROP FUNCTION FindInvalidEmails;
GO

IF EXISTS (SELECT name
           FROM sys.assemblies
           WHERE name = 'MyClrCode')
    DROP ASSEMBLY MyClrCode;
GO

CREATE ASSEMBLY MyClrCode
    FROM 'C:\FindInvalidEmails.dll'
    WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION FindInvalidEmails
(@ModifiedSince DATETIME)
RETURNS TABLE (
    CustomerId INT,
    EmailAddress NVARCHAR (4000))
AS EXTERNAL NAME MyClrCode.UserDefinedFunctions.[FindInvalidEmails];
GO

SELECT *
FROM FindInvalidEmails('2000-01-01');
GO