CLR テーブル値関数

[アーティクル]
05/30/2023

適用対象:SQL Server

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

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

注意

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

CLR Table-Valued関数の詳細については、MSSQLTips の CLR テーブル値関数の概要SQL Serverチェック。

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

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

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

テーブル値関数の実装

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

テーブル値パラメーター

テーブル値パラメーターとは、プロシージャや関数に渡されるユーザー定義のテーブル型です。テーブル値パラメーターを使用すると、複数行のデータを効率的にサーバーに渡すことができます。テーブル値パラメーターは、パラメーター配列と同様の機能を提供しますが、柔軟性が向上し、Transact-SQL との統合が近くなります。テーブル値パラメーターを使用するとパフォーマンスが向上する可能性もあります。さらに、サーバーへのラウンドトリップの回数を減らすのにも有用です。スカラーパラメーターのリストを使用するなどしてサーバーに複数の要求を送信する代わりに、データをテーブル値パラメーターとしてサーバーに送信できます。ユーザー定義テーブル型は、SQL Server プロセスで実行されるマネージドストアドプロシージャまたは関数に対して、テーブル値パラメーターとして渡したり、その型から返したりすることはできません。テーブル値パラメーターの詳細については、「テーブル値パラメーターの使用 (データベースエンジン)」を参照してください。

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

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

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

Transact-SQL のテーブル値関数の定義

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

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

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

select * from function();  
select * from tbl join function() f on tbl.col = f.col;  
select * from table t cross apply function(t.column);

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

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

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

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

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

サンプルコード

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 2005 (9.x) での実行ではサポートされていません。このようなデータベースオブジェクトには、テーブル値関数などがあります。

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

サンプル: SQL Server クエリの結果を返す

次の例は、SQL Server データベースに対してクエリを実行するテーブル値関数を示しています。このサンプルでは、SQL Server 2008 (10.0.x) の AdventureWorks Lightweight データベースを使用します。AdventureWorks サンプルデータベースを参照してください

ソースコードファイルに FindInvalidEmails.cs または 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 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 [AdventureWorksLT2008].[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 [AdventureWorksLT2008].[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 ClassImports 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 [AdventureWorksLT2008].[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 AdventureWorksLT2008;  
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 -- EXTERNAL_ACCESS;  
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 クエリの結果を返す

フィードバック

フィードバック

その他のリソース

サンプルコード