Delen via

CLR-tabelwaardefuncties

  • Artikel

van toepassing op:SQL Server-

Een tabelwaardefunctie is een door de gebruiker gedefinieerde functie die een tabel retourneert.

SQL Server breidt de functionaliteit van tabelwaardefuncties uit door u in elke beheerde taal een tabelwaardefunctie te laten definiëren. Gegevens worden geretourneerd vanuit een tabelwaardefunctie via een IEnumerable- of IEnumerator-object.

Voor tabelwaardefuncties kunnen de kolommen van het retourtabeltype geen tijdstempelkolommen of niet-Unicode-gegevenstypekolommen bevatten (zoals tekenreeks, varchar-en tekst). De NOT NULL beperking wordt niet ondersteund.

Verschillen tussen Transact-SQL- en CLR-tabelwaardefuncties

Transact-SQL tabelwaardefuncties worden de resultaten van het aanroepen van de functie in een tussenliggende tabel gerealiseerd. Omdat ze een tussenliggende tabel gebruiken, kunnen ze beperkingen en unieke indexen ondersteunen voor de resultaten. Deze functies kunnen handig zijn wanneer grote resultaten worden geretourneerd.

Common Language Runtime (CLR)-functies met tabelwaarden vertegenwoordigen daarentegen een alternatief voor streaming. Er is geen vereiste dat de volledige set resultaten in één tabel wordt gerealiseerd. Het IEnumerable object dat door de beheerde functie wordt geretourneerd, wordt rechtstreeks aangeroepen door het uitvoeringsplan van de query waarmee de tabelwaardefunctie wordt aangeroepen en de resultaten worden op een incrementele manier gebruikt. Dit streamingmodel zorgt ervoor dat de resultaten direct nadat de eerste rij beschikbaar is, kunnen worden gebruikt in plaats van te wachten tot de hele tabel is ingevuld. Het is ook een beter alternatief als u grote aantallen rijen hebt geretourneerd, omdat ze niet als geheel in het geheugen hoeven te worden gerealiseerd. Een beheerde tabelwaardefunctie kan bijvoorbeeld worden gebruikt om een tekstbestand te parseren en elke regel als een rij te retourneren.

Tabelwaardefuncties implementeren

Implementeer tabelfuncties als methoden voor een klasse in een .NET Framework-assembly. De functiecode van de tabelwaarde moet de IEnumerable-interface implementeren. De IEnumerable-interface wordt gedefinieerd in .NET Framework. Typen die matrices en verzamelingen in .NET Framework vertegenwoordigen, implementeren al de IEnumerable interface. Dit maakt het eenvoudig voor het schrijven van tabelwaardefuncties die een verzameling of een matrix converteren naar een resultatenset.

Parameters met tabelwaarde

Tabelwaardeparameters zijn door de gebruiker gedefinieerde tabeltypen die worden doorgegeven aan een procedure of functie en bieden een efficiënte manier om meerdere rijen met gegevens door te geven aan de server. Parameters met tabelwaarde bieden vergelijkbare functionaliteit als parametermatrices, maar bieden meer flexibiliteit en een betere integratie met Transact-SQL. Ze bieden ook het potentieel voor betere prestaties.

Parameters met tabelwaarden helpen ook het aantal retouren naar de server te verminderen. In plaats van meerdere aanvragen naar de server te verzenden, zoals met een lijst met scalaire parameters, kunnen gegevens als een tabelwaardeparameter naar de server worden verzonden. Een door de gebruiker gedefinieerd tabeltype kan niet worden doorgegeven als een tabelwaardeparameter aan of worden geretourneerd door een beheerde opgeslagen procedure of functie die wordt uitgevoerd in het SQL Server-proces. Zie Tabelwaardeparameters (Database Engine) gebruikenvoor meer informatie over parameters met tabelwaarden.

Uitvoerparameters en tabelwaardefuncties

Gegevens kunnen worden geretourneerd vanuit tabelwaardefuncties met behulp van uitvoerparameters. De bijbehorende parameter in de tabelwaarde van de implementatiecode moet een pass-by-reference-parameter als argument gebruiken. Visual Basic .NET biedt geen ondersteuning voor uitvoerparameters op dezelfde manier als C# doet. U moet de parameter op basis van verwijzing opgeven en het kenmerk <Out()> toepassen om een uitvoerparameter weer te geven, zoals in het volgende voorbeeld:

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

Een tabelwaardefunctie definiëren in Transact-SQL

De syntaxis voor het definiëren van een clr-tabelwaardefunctie is vergelijkbaar met die van een Transact-SQL tabelwaardefunctie, met de toevoeging van de EXTERNAL NAME-component. Bijvoorbeeld:

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

Tabelwaardefuncties worden gebruikt om gegevens in relationele vorm weer te geven voor verdere verwerking in query's, zoals:

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);

Tabelwaardefuncties kunnen een tabel retourneren wanneer:

  • Gemaakt op basis van scalaire invoerargumenten. Een tabelwaardefunctie die bijvoorbeeld een door komma's gescheiden reeks getallen gebruikt en deze draait in een tabel.

  • Gegenereerd op basis van externe gegevens. Bijvoorbeeld een tabelwaardefunctie die het gebeurtenislogboek leest en beschikbaar maakt als een tabel.

Notitie

Een tabelwaardefunctie kan alleen gegevenstoegang uitvoeren via een Transact-SQL query in de InitMethod methode en niet in de FillRow methode. De InitMethod moet worden gemarkeerd met de eigenschap SqlFunction.DataAccess.Read kenmerk als er een Transact-SQL query wordt uitgevoerd.

Een voorbeeld van een tabelwaardefunctie

De volgende tabelwaardefunctie retourneert informatie uit het gebeurtenislogboek van het systeem. De functie gebruikt één tekenreeksargument met de naam van het gebeurtenislogboek dat moet worden gelezen.

Voorbeeldcode

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;
    }
}

Declareren en gebruiken van de voorbeeldfunctie met tabelwaarde

Nadat de voorbeeldtabelwaarde is gecompileerd, kan deze als volgt worden gedeclareerd in Transact-SQL:

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

Visual C++ databaseobjecten die zijn gecompileerd met /clr:pure worden niet ondersteund voor uitvoering op SQL Server. Dergelijke databaseobjecten bevatten bijvoorbeeld tabelwaardefuncties.

Als u het voorbeeld wilt testen, probeert u de volgende Transact-SQL code:

-- 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

Voorbeelden

De Transact-SQL codevoorbeelden in dit artikel gebruiken de AdventureWorks2022 of AdventureWorksDW2022 voorbeelddatabase, die u kunt downloaden van de Microsoft SQL Server-voorbeelden en communityprojecten startpagina.

Een. De resultaten van een SQL Server-query retourneren

In het volgende voorbeeld ziet u een tabelwaardefunctie waarmee een query wordt uitgevoerd op een SQL Server-database.

Geef het broncodebestand een naam FindInvalidEmails.cs of FindInvalidEmails.vb.

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 EmailAddress;

      public EmailResult(SqlInt32 customerId, SqlString emailAddress) {
         CustomerId = customerId;
         EmailAddress = emailAddress;
      }
   }

   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 emailAddress) {
      EmailResult emailResult = (EmailResult)emailResultObj;

      customerId = emailResult.CustomerId;
      emailAddress = emailResult.EmailAddress;
   }
};

Compileer de broncode naar een DLL en kopieer de DLL naar de hoofdmap van uw C-station. Voer vervolgens de volgende Transact-SQL-query uit.

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