Funkcje wartości tabeli CLR

Artykuł
01/02/2025

Funkcja z wartością tabeli jest funkcją zdefiniowaną przez użytkownika, która zwraca tabelę.

Program SQL Server rozszerza funkcjonalność funkcji wartości tabeli, umożliwiając definiowanie funkcji wartości tabeli w dowolnym języku zarządzanym. Dane są zwracane z funkcji wartości tabeli za pośrednictwem obiektu IEnumerable lub IEnumerator.

W przypadku funkcji wartości tabeli kolumny zwracanego typu tabeli nie mogą zawierać kolumn sygnatury czasowej ani kolumn typu danych ciągów innych niż Unicode (takich jak char, varchari tekst). Ograniczenie NOT NULL nie jest obsługiwane.

Różnice między funkcjami Transact-SQL i CLR z wartościami tabel

Transact-SQL funkcje wartości tabeli materializować wyniki wywoływania funkcji do tabeli pośredniej. Ponieważ używają tabeli pośredniej, mogą obsługiwać ograniczenia i unikatowe indeksy w wynikach. Te funkcje mogą być przydatne w przypadku zwracania dużych wyników.

Natomiast funkcje środowiska uruchomieniowego języka wspólnego (CLR) o wartości tabeli reprezentują alternatywę przesyłania strumieniowego. Nie ma potrzeby, aby cały zestaw wyników został zmaterializowany w jednej tabeli. Obiekt IEnumerable zwracany przez funkcję zarządzaną jest wywoływany bezpośrednio przez plan wykonywania zapytania, który wywołuje funkcję z wartością tabeli, a wyniki są używane w sposób przyrostowy. Ten model przesyłania strumieniowego zapewnia, że wyniki mogą być używane natychmiast po udostępnieniu pierwszego wiersza, zamiast czekać na wypełnienie całej tabeli. Jest to również lepsza alternatywa, jeśli zwracana jest duża liczba wierszy, ponieważ nie muszą być zmaterializowane w pamięci jako całości. Na przykład zarządzana funkcja o wartości tabeli może służyć do analizowania pliku tekstowego i zwracania każdego wiersza jako wiersza.

Implementowanie funkcji wartości tabeli

Zaimplementuj funkcje wartości tabeli jako metody w klasie w zestawie .NET Framework. Kod funkcji o wartości tabeli musi implementować interfejs IEnumerable. Interfejs IEnumerable jest zdefiniowany w programie .NET Framework. Typy reprezentujące tablice i kolekcje w programie .NET Framework już implementują interfejs IEnumerable. Ułatwia to pisanie funkcji wartości tabeli, które konwertują kolekcję lub tablicę na zestaw wyników.

Parametry wartości tabeli

Parametry wartości tabeli to typy tabel zdefiniowane przez użytkownika, które są przekazywane do procedury lub funkcji i zapewniają wydajny sposób przekazywania wielu wierszy danych do serwera. Parametry wartości tabeli zapewniają podobne funkcje do tablic parametrów, ale oferują większą elastyczność i bliższą integrację z językiem Transact-SQL. Zapewniają one również potencjał lepszej wydajności.

Parametry wartości tabeli pomagają również zmniejszyć liczbę rund na serwer. Zamiast wysyłać wiele żądań do serwera, takich jak lista parametrów skalarnych, dane można wysyłać do serwera jako parametr wartości tabeli. Nie można przekazać typu tabeli zdefiniowanego przez użytkownika jako parametru o wartości tabeli lub być zwracany z zarządzanej procedury składowanej lub funkcji wykonywanej w procesie programu SQL Server. Aby uzyskać więcej informacji na temat parametrów wartości tabeli, zobacz Use table-valued parameters (Database Engine).

Parametry wyjściowe i funkcje wartości tabeli

Informacje mogą być zwracane z funkcji wartości tabeli przy użyciu parametrów wyjściowych. Odpowiedni parametr w funkcji tabeli kod implementacji powinien używać parametru pass-by-reference jako argumentu. Platforma .NET w języku Visual Basic nie obsługuje parametrów wyjściowych w taki sam sposób, jak w języku C#. Należy określić parametr według odwołania i zastosować atrybut <Out()> do reprezentowania parametru wyjściowego, jak w poniższym przykładzie:

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

Definiowanie funkcji wartości tabeli w Transact-SQL

Składnia definiująca funkcję wartości tabeli CLR jest podobna do funkcji Transact-SQL wartości tabeli z dodatkiem klauzuli EXTERNAL NAME. Na przykład:

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

Funkcje wartości tabeli służą do reprezentowania danych w formie relacyjnej w celu dalszego przetwarzania zapytań, takich jak:

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

Funkcje wartości tabeli mogą zwracać tabelę, gdy:

Utworzone na podstawie argumentów wejściowych skalarnych. Na przykład funkcja z wartością tabeli, która przyjmuje rozdzielany przecinkami ciąg liczb i przełącza je do tabeli.
Generowane na podstawie danych zewnętrznych. Na przykład funkcja z wartością tabeli, która odczytuje dziennik zdarzeń i uwidacznia ją jako tabelę.

Nuta

Funkcja z wartością tabeli może wykonywać dostęp do danych tylko za pośrednictwem zapytania Transact-SQL w metodzie InitMethod, a nie w metodzie FillRow. InitMethod należy oznaczyć właściwością atrybutu SqlFunction.DataAccess.Read, jeśli zostanie wykonane zapytanie Transact-SQL.

Przykładowa funkcja z wartością tabeli

Poniższa funkcja o wartości tabeli zwraca informacje z dziennika zdarzeń systemowych. Funkcja przyjmuje jeden argument ciągu zawierający nazwę dziennika zdarzeń do odczytania.

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

Deklarowanie i używanie przykładowej funkcji z wartością tabeli

Po skompilowaniu przykładowej funkcji o wartości tabeli można ją zadeklarować w Transact-SQL w następujący sposób:

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

Obiekty bazy danych Visual C++ skompilowane przy użyciu /clr:pure nie są obsługiwane do wykonywania w programie SQL Server. Na przykład takie obiekty bazy danych obejmują funkcje z wartościami tabel.

Aby przetestować przykład, wypróbuj następujący kod 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

Przykłady

Przykłady kodu Transact-SQL w tym artykule korzystają z przykładowej bazy danych AdventureWorks2022 lub AdventureWorksDW2022, którą można pobrać ze strony głównej Przykłady programu Microsoft SQL Server i projekty społeczności.

A. Zwracanie wyników zapytania programu SQL Server

W poniższym przykładzie przedstawiono funkcję o wartości tabeli, która wysyła zapytanie do bazy danych programu SQL Server.

Nadaj plikowi kodu źródłowego nazwę FindInvalidEmails.cs lub 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

Skompiluj kod źródłowy do biblioteki DLL i skopiuj bibliotekę DLL do katalogu głównego dysku C. Następnie wykonaj następujące zapytanie 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

Udostępnij za pośrednictwem