CLR テーブル値関数

[アーティクル]
12/30/2024

適用対象:SQL Server

テーブル値関数とは、テーブルを返すユーザー定義関数です。

SQL Server は、任意のマネージド言語でテーブル値関数を定義できるようにすることで、テーブル値関数の機能を拡張します。データは、IEnumerable または IEnumerator オブジェクトを介してテーブル値関数から返されます。

テーブル値関数の場合、戻り値のテーブル型の列には、タイムスタンプ列または Unicode 以外の文字列データ型の列 (char、varchar、テキストなど) を含めることはできません。 NOT NULL 制約はサポートされていません。

Transact-SQL 関数と CLR テーブル値関数の違い

Transact-SQL テーブル値関数は、関数を中間テーブルに呼び出した結果を具体化します。 TVF では中間テーブルを使用するため、結果に対する制約や一意インデックスがサポートされます。これらの機能は、大きな結果が返される場合に役立ちます。

これに対し、共通言語ランタイム (CLR) のテーブル値関数は、ストリーミングの代替手段を表します。結果のセット全体を 1 つのテーブルに具体化する必要はありません。マネージド関数によって返される IEnumerable オブジェクトは、テーブル値関数を呼び出すクエリの実行プランによって直接呼び出され、結果は増分的に使用されます。このストリーミングモデルでは、テーブル全体に値が格納されるまで待たなくても、最初の行が生成された直後から結果を使用できます。また、返される行の数が多い場合は、全体としてメモリ内で具体化する必要がないため、より良い代替手段です。たとえば、マネージドテーブル値関数を使用して、テキストファイルを解析し、テキストの各行を 1 つのテーブル行にして返すことができます。

テーブル値関数を実装する

テーブル値関数を .NET Framework アセンブリ内のクラスのメソッドとして実装します。テーブル値関数コードは、IEnumerable インターフェイスを実装する必要があります。 IEnumerable インターフェイスは.NET Framework で定義されています。 .NET Framework の配列とコレクションを表す型は、IEnumerable インターフェイスを既に実装しています。このため、コレクションまたは配列を結果セットに変換するテーブル値関数を簡単に記述できます。

テーブル値パラメーター

テーブル値パラメーターとは、プロシージャや関数に渡されるユーザー定義のテーブル型です。テーブル値パラメーターを使用すると、複数行のデータを効率的にサーバーに渡すことができます。テーブル値パラメーターは、パラメーター配列と同様の機能を提供しますが、Transact-SQL との柔軟性が向上し、より緊密に統合できます。テーブル値パラメーターを使用するとパフォーマンスが向上する可能性もあります。

さらに、サーバーへのラウンドトリップの回数を減らすのにも有用です。スカラーパラメーターのリストを使用するなどしてサーバーに複数の要求を送信する代わりに、データをテーブル値パラメーターとしてサーバーに送信できます。ユーザー定義テーブル型は、SQL Server プロセスで実行されるマネージドストアドプロシージャまたは関数に対して、テーブル値パラメーターとして渡したり、そのパラメーターから返したりすることはできません。テーブル値パラメーターの詳細については、「テーブル値パラメーターの使用 (データベースエンジン)を参照してください。

出力パラメーターとテーブル値関数

出力パラメーターを使用して、テーブル値関数から情報が返される場合があります。実装コードのテーブル値関数の対応するパラメーターは、引数として参照渡しのパラメーターを使用する必要があります。 Visual Basic .NET では、C# と同じ方法で出力パラメーターがサポートされていません。次の例のように、パラメーターを参照で指定し、<Out()> 属性を適用して出力パラメーターを表す必要があります。

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

Transact-SQL でテーブル値関数を定義する

CLR テーブル値関数を定義するための構文は、Transact-SQL テーブル値関数の構文と似ていますが、EXTERNAL NAME 句が追加されています。次に例を示します。

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

テーブル値関数を使用して、クエリで追加処理を行うリレーショナル形式のデータを表現できます。次に例を示します。

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

テーブル値関数は、次の場合にテーブルを返すことができます。

スカラー値の入力引数から作成された場合。たとえば、数値をコンマで区切った文字列をピボットしてテーブルにするテーブル値関数などです。
外部データから生成した場合。たとえば、イベントログを読み取り、テーブルとして公開するテーブル値関数などです。

Note

テーブル値関数は、FillRow メソッドではなく、InitMethod メソッドの Transact-SQL クエリを介してのみデータアクセスを実行できます。 Transact-SQL クエリを実行する場合、InitMethod は SqlFunction.DataAccess.Read 属性プロパティでマークする必要があります。

テーブル値関数のサンプル

次のテーブル値関数は、システムイベントログから情報を返します。読み取るイベントログの名前を含んだ文字列引数を 1 つ受け取ります。

サンプルコード

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

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

テーブル値関数のサンプルを宣言して使用する

サンプルのテーブル値関数をコンパイルした後は、次のように 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

/clr:pure でコンパイルされた Visual C++ データベースオブジェクトは、SQL Server での実行ではサポートされていません。このようなデータベースオブジェクトには、テーブル値関数などがあります。

サンプルをテストするには、次の 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

例

この記事の Transact-SQL コードサンプルでは、AdventureWorks2022 または AdventureWorksDW2022 サンプルデータベースを使用します。このサンプルデータベースは、Microsoft SQL Server サンプルとコミュニティプロジェクトホームページからダウンロードできます。

ある。 SQL Server クエリの結果を返す

次の例は、SQL Server データベースに対してクエリを実行するテーブル値関数を示しています。

ソースコードファイルに FindInvalidEmails.cs または 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

ソースコードをコンパイルして DLL を生成し、DLL を C ドライブのルートディレクトリにコピーします。次に、次の 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

次の方法で共有

CLR テーブル値関数

Transact-SQL 関数と CLR テーブル値関数の違い

テーブル値関数を実装する

テーブル値パラメーター

出力パラメーターとテーブル値関数

Transact-SQL でテーブル値関数を定義する

テーブル値関数のサンプル

サンプルコード

テーブル値関数のサンプルを宣言して使用する

例

ある。 SQL Server クエリの結果を返す

フィードバック

その他のリソース

次の方法で共有

CLR テーブル値関数

Transact-SQL 関数と CLR テーブル値関数の違い

テーブル値関数を実装する

テーブル値パラメーター

出力パラメーターとテーブル値関数

Transact-SQL でテーブル値関数を定義する

テーブル値関数のサンプル

サンプル コード

テーブル値関数のサンプルを宣言して使用する

例

ある。 SQL Server クエリの結果を返す

フィードバック

その他のリソース

サンプルコード