CLR-tabellvärdesfunktioner

Artikel
01/02/2025

gäller för:SQL Server

En tabellvärdesfunktion är en användardefinierad funktion som returnerar en tabell.

SQL Server utökar funktionerna i tabellvärdesfunktioner genom att du kan definiera en tabellvärdesfunktion på alla hanterade språk. Data returneras från en tabellvärdesfunktion via ett IEnumerable- eller IEnumerator-objekt.

För tabellvärdesfunktioner kan kolumnerna i returtabelltypen inte innehålla tidsstämpelkolumner eller kolumner av typen icke-Unicode-strängdata (till exempel tecken, varcharoch text). Villkoret NOT NULL stöds inte.

Skillnader mellan Transact-SQL- och CLR-tabellvärdesfunktioner

Transact-SQL tabellvärdesfunktioner materialiserar resultatet av att anropa funktionen till en mellanliggande tabell. Eftersom de använder en mellanliggande tabell kan de stödja begränsningar och unika index över resultaten. Dessa funktioner kan vara användbara när stora resultat returneras.

Däremot representerar CLR-tabellvärdesfunktioner (Common Language Runtime) ett alternativ för direktuppspelning. Det finns inget krav på att hela resultatuppsättningen materialiseras i en enda tabell. Det IEnumerable objekt som returneras av den hanterade funktionen anropas direkt av körningsplanen för frågan som anropar funktionen table-valued och resultatet förbrukas inkrementellt. Den här strömningsmodellen ser till att resultaten kan användas direkt efter att den första raden är tillgänglig, i stället för att vänta på att hela tabellen ska fyllas i. Det är också ett bättre alternativ om du har ett stort antal rader som returneras, eftersom de inte behöver materialiseras i minnet som helhet. Till exempel kan en hanterad tabellvärdesfunktion användas för att parsa en textfil och returnera varje rad som en rad.

Implementera tabellvärdesfunktioner

Implementera tabellvärdesfunktioner som metoder i en klass i en .NET Framework-sammansättning. Funktionskoden för tabellvärde måste implementera IEnumerable-gränssnittet. Gränssnittet IEnumerable definieras i .NET Framework. Typer som representerar matriser och samlingar i .NET Framework implementerar redan IEnumerable-gränssnittet. Detta gör det enkelt att skriva tabellvärdesfunktioner som konverterar en samling eller en matris till en resultatuppsättning.

Tabellvärdesparametrar

Tabellvärdesparametrar är användardefinierade tabelltyper som skickas till en procedur eller funktion och ger ett effektivt sätt att skicka flera rader med data till servern. Tabellvärdesparametrar ger liknande funktioner som parametermatriser, men ger större flexibilitet och närmare integrering med Transact-SQL. De ger också möjlighet till bättre prestanda.

Tabellvärdesparametrar bidrar också till att minska antalet tur- och returresor till servern. I stället för att skicka flera begäranden till servern, till exempel med en lista över skalära parametrar, kan data skickas till servern som en tabellvärdesparameter. En användardefinierad tabelltyp kan inte skickas som en tabellvärdeparameter till eller returneras från en hanterad lagrad procedur eller funktion som körs i SQL Server-processen. Mer information om tabellvärdesparametrar finns i Use table-valued parameters (Database Engine).

Utdataparametrar och tabellvärdesfunktioner

Information kan returneras från tabellvärdesfunktioner med hjälp av utdataparametrar. Motsvarande parameter i funktionen för tabellvärde för implementeringskod bör använda en parameter för direktreferens som argument. Visual Basic .NET stöder inte utdataparametrar på samma sätt som C#. Du måste ange parametern efter referens och använda attributet <Out()> för att representera en utdataparameter, som i följande exempel:

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

Definiera en tabellvärdesfunktion i Transact-SQL

Syntaxen för att definiera en CLR-tabellvärdesfunktion liknar den för en Transact-SQL tabellvärdesfunktion, med tillägget av EXTERNAL NAME-satsen. Till exempel:

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

Tabellvärdesfunktioner används för att representera data i relationsform för vidare bearbetning i frågor som:

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

Tabellvärdesfunktioner kan returnera en tabell när:

Skapad från skalära indataargument. Till exempel en tabellvärdesfunktion som tar en kommaavgränsad sträng med tal och pivoterar dem till en tabell.
Genereras från externa data. Till exempel en tabellvärdesfunktion som läser händelseloggen och exponerar den som en tabell.

Not

En tabellvärdesfunktion kan bara utföra dataåtkomst via en Transact-SQL fråga i metoden InitMethod och inte i metoden FillRow. InitMethod ska markeras med egenskapen SqlFunction.DataAccess.Read attribut om en Transact-SQL fråga utförs.

En exempeltabellvärdesfunktion

Följande tabellvärdesfunktion returnerar information från systemhändelseloggen. Funktionen tar ett argument med en sträng som innehåller namnet på händelseloggen för att läsa.

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

Imports System
Imports System.Data.Sql
Imports Microsoft.SqlServer.Server
Imports System.Collections
Imports System.Data.SqlTypes
Imports System.Diagnostics
Imports System.Runtime.InteropServices

Public Class TabularEventLog
    <SqlFunction(FillRowMethodName:="FillRow")> _
    Public Shared Function InitMethod(ByVal logname As String) As IEnumerable
        Return New EventLog(logname).Entries
    End Function

    Public Shared Sub FillRow(ByVal obj As Object, <Out()> ByRef timeWritten As SqlDateTime, <Out()> ByRef message As SqlChars, <Out()> ByRef category As SqlChars, <Out()> ByRef instanceId As Long)
        Dim eventLogEnTry As EventLogEntry = CType(obj, EventLogEntry)
        timeWritten = New SqlDateTime(eventLogEnTry.TimeWritten)
        message = New SqlChars(eventLogEnTry.Message)
        category = New SqlChars(eventLogEnTry.Category)
        instanceId = eventLogEnTry.InstanceId
    End Sub
End Class

Deklarera och använda exempeltabellvärdesfunktionen

När exempeltabellvärdesfunktionen har kompilerats kan den deklareras i Transact-SQL så här:

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

Visuella C++-databasobjekt som kompilerats med /clr:pure stöds inte för körning på SQL Server. Sådana databasobjekt innehåller till exempel tabellvärdesfunktioner.

Testa exemplet genom att prova följande Transact-SQL kod:

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

Exempel

I Transact-SQL kodexempel i den här artikeln används AdventureWorks2022- eller AdventureWorksDW2022-exempeldatabasen, som du kan ladda ned från Microsoft SQL Server-exempel och Community Projects startsida.

A. Returnera resultatet av en SQL Server-fråga

Följande exempel visar en tabellvärdesfunktion som frågar en SQL Server-databas.

Ge källkodsfilen namnet FindInvalidEmails.cs eller FindInvalidEmails.vb.

C#
Visual Basic .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;
   }
};

Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Public Partial Class UserDefinedFunctions
   Private Class EmailResult
      Public CustomerId As SqlInt32
      Public EmailAdress As SqlString

      Public Sub New(customerId__1 As SqlInt32, emailAdress__2 As SqlString)
         CustomerId = customerId__1
         EmailAdress = emailAdress__2
      End Sub
   End Class

   Public Shared Function ValidateEmail(emailAddress As SqlString) As Boolean
      If emailAddress.IsNull Then
         Return False
      End If

      If Not emailAddress.Value.EndsWith("@adventure-works.com") Then
         Return False
      End If

      ' Validate the address. Put any more rules here.
      Return True
   End Function

   <SqlFunction(DataAccess := DataAccessKind.Read, FillRowMethodName := "FindInvalidEmails_FillRow", TableDefinition := "CustomerId int, EmailAddress nvarchar(4000)")> _
   Public Shared Function FindInvalidEmails(modifiedSince As SqlDateTime) As IEnumerable
      Dim resultCollection As New ArrayList()

      Using connection As New SqlConnection("context connection=true")
         connection.Open()

         Using selectEmails As New SqlCommand("SELECT " & "[CustomerID], [EmailAddress] " & "FROM [AdventureWorksLT2022].[SalesLT].[Customer] " & "WHERE [ModifiedDate] >= @modifiedSince", connection)
            Dim modifiedSinceParam As SqlParameter = selectEmails.Parameters.Add("@modifiedSince", SqlDbType.DateTime)
            modifiedSinceParam.Value = modifiedSince

            Using emailsReader As SqlDataReader = selectEmails.ExecuteReader()
               While emailsReader.Read()
                  Dim emailAddress As SqlString = emailsReader.GetSqlString(1)
                  If ValidateEmail(emailAddress) Then
                     resultCollection.Add(New EmailResult(emailsReader.GetSqlInt32(0), emailAddress))
                  End If
               End While
            End Using
         End Using
      End Using

      Return resultCollection
   End Function

   Public Shared Sub FindInvalidEmails_FillRow(emailResultObj As Object, ByRef customerId As SqlInt32, ByRef emailAdress As SqlString)
      Dim emailResult As EmailResult = DirectCast(emailResultObj, EmailResult)

      customerId = emailResult.CustomerId
      emailAdress = emailResult.EmailAdress
   End Sub
End Class

Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Public Partial Class UserDefinedFunctions
   Private Class EmailResult
      Public CustomerId As SqlInt32
      Public EmailAdress As SqlString

      Public Sub New(customerId__1 As SqlInt32, emailAdress__2 As SqlString)
         CustomerId = customerId__1
         EmailAdress = emailAdress__2
      End Sub
   End Class

   Public Shared Function ValidateEmail(emailAddress As SqlString) As Boolean
      If emailAddress.IsNull Then
         Return False
      End If

      If Not emailAddress.Value.EndsWith("@adventure-works.com") Then
         Return False
      End If

      ' Validate the address. Put any more rules here.
      Return True
   End Function

   <SqlFunction(DataAccess := DataAccessKind.Read, FillRowMethodName := "FindInvalidEmails_FillRow", TableDefinition := "CustomerId int, EmailAddress nvarchar(4000)")> _
   Public Shared Function FindInvalidEmails(modifiedSince As SqlDateTime) As IEnumerable
      Dim resultCollection As New ArrayList()

      Using connection As New SqlConnection("context connection=true")
         connection.Open()

         Using selectEmails As New SqlCommand("SELECT " & "[CustomerID], [EmailAddress] " & "FROM [AdventureWorksLT2022].[SalesLT].[Customer] " & "WHERE [ModifiedDate] >= @modifiedSince", connection)
            Dim modifiedSinceParam As SqlParameter = selectEmails.Parameters.Add("@modifiedSince", SqlDbType.DateTime)
            modifiedSinceParam.Value = modifiedSince

            Using emailsReader As SqlDataReader = selectEmails.ExecuteReader()
               While emailsReader.Read()
                  Dim emailAddress As SqlString = emailsReader.GetSqlString(1)
                  If ValidateEmail(emailAddress) Then
                     resultCollection.Add(New EmailResult(emailsReader.GetSqlInt32(0), emailAddress))
                  End If
               End While
            End Using
         End Using
      End Using

      Return resultCollection
   End Function

   Public Shared Sub FindInvalidEmails_FillRow(emailResultObj As Object, customerId As SqlInt32, emailAdress As SqlString)
      Dim emailResult As EmailResult = DirectCast(emailResultObj, EmailResult)

      customerId = emailResult.CustomerId
      emailAdress = emailResult.EmailAdress
   End Sub
End Class

Kompilera källkoden till en DLL och kopiera DLL:en till rotkatalogen på C-enheten. Kör sedan följande Transact-SQL fråga.

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

Dela via