[アーティクル]
09/28/2010

CLR テーブル値関数

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

SQL Server 2005 以降の SQL Server では、テーブル値関数の機能が拡張され、テーブル値関数をどのマネージ言語でも定義できるようになりました。テーブル値関数からは IEnumerable オブジェクトまたは IEnumerator オブジェクトを経由してデータが返されます。

注意
テーブル値関数で返されるテーブル型の列には、timestamp 型の列および Unicode 以外の文字列データ型の列 (char、varchar、text など) を含めることはできません。 NOT NULL 制約はサポートされません。

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

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

SQL Server 2005 以降、互換性レベルが "80" の SQL Server データベースでは、マネージコードでユーザー定義型、ストアドプロシージャ、関数、集計、またはトリガーを作成することはできません。 SQL Server のこれらの CLR 統合機能を使用するには、sp_dbcmptlevel ストアドプロシージャを使用してデータベースの互換性レベルを "100" に設定する必要があります。

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

サンプルをテストするには、次の 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 の AdventureWorks Light データベースを使用します。 AdventureWorks のダウンロードについては、https://www.codeplex.com/sqlserversamples を参照してください。

ソースコードファイルに 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 Microsoft.SqlServer.Server
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices

Public Class UserDefinedFunctions
   <SqlFunction(DataAccess:=DataAccessKind.Read, FillRowMethodName:="FindInvalidEmails_FillRow"), TableDefinition="CustomerId int, EmailAddress nvarchar(4000)")> _
   Public Shared Function FindInvalidEmails(ByVal modifiedSince As SqlDateTime) As IEnumerable
      Dim resultCollection As New ArrayList
      Using connection As SqlConnection = New SqlConnection("context connection=true")
         connection.Open()
         Using selectEmails As SqlCommand = New SqlCommand("SELECT [CustomerID], [EmailAddress] FROM [AdventureWorksLT2008].[SalesLT].[Customer] WHERE [ModifiedDate] >= @modifiedSince", connection)
            selectEmails.Parameters.Add("@modifiedSince", SqlDbType.DateTime).Value = modifiedSince
            Using emailsReader As SqlDataReader = selectEmails.ExecuteReader
               Do While emailsReader.Read
                  Dim emailAddress As SqlString = emailsReader.GetSqlString(1)
                  If UserDefinedFunctions.ValidateEmail(emailAddress) Then
                     resultCollection.Add(New EmailResult(emailsReader.GetSqlInt32(0), emailAddress))
                  End If
               Loop
            End Using
            Return resultCollection
         End Using
      End Using
   End Function

   Public Shared Sub FindInvalidEmails_FillRow(ByVal emailResultObj As Object, <Out()> ByRef customerId As SqlInt32, <Out()> ByRef emailAdress As SqlString)
      Dim emailResult As EmailResult = DirectCast(emailResultObj, EmailResult)
      customerId = emailResult.CustomerId
      emailAdress = emailResult.EmailAdress
   End Sub

   Public Shared Function ValidateEmail(ByVal 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
      Return True
   End Function

   Private Class EmailResult
      Public Sub New(ByVal customerId As SqlInt32, ByVal emailAdress As SqlString)
         Me.CustomerId = customerId
         Me.EmailAdress = emailAdress
      End Sub

      Public CustomerId As SqlInt32
      Public EmailAdress As SqlString
   End Class
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

変更履歴

変更内容
SQL Server クエリのサンプルを追加しました。

次の方法で共有

CLR テーブル値関数

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

テーブル値関数の実装

テーブル値パラメーター

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

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

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

サンプルコード

サンプルのテーブル値関数の宣言と使用

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

関連項目

概念

変更履歴

その他のリソース

次の方法で共有

CLR テーブル値関数

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

テーブル値関数の実装

テーブル値パラメーター

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

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

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

サンプル コード

サンプルのテーブル値関数の宣言と使用

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

関連項目

概念

変更履歴

その他のリソース

サンプルコード