CLR 테이블 반환 함수

아티클
07/04/2024

적용 대상: SQL Server

테이블 반환 함수는 테이블을 반환하는 사용자 정의 함수입니다.

SQL Server 2005(9.x)부터 SQL Server는 관리되는 모든 언어로 테이블 반환 함수를 정의할 수 있도록 하여 테이블 반환 함수의 기능을 확장합니다. 데이터는 IEnumerable 또는 IEnumerator 개체를 통해 테이블 반환 함수에서 반환됩니다.

참고 항목

테이블 반환 함수의 경우 반환 테이블 형식의 열에는 타임스탬프 열 또는 유니코드가 아닌 문자열 데이터 형식 열(예: char, varchar 및 text)이 포함될 수 없습니다. NOT NULL 제약 조건은 지원되지 않습니다.

CLR 테이블 반환 함수에 대한 자세한 내용은 MSSQLTips의 SQL Server CLR 테이블 반환 함수 소개를 확인하세요.

Transact-SQL과 CLR 테이블 반환 함수 간의 차이점

Transact-SQL 테이블 반환 함수는 함수를 중간 테이블로 호출한 결과를 구체화합니다. 중간 테이블을 사용하므로 결과에 대한 제약 조건 및 고유 인덱스를 지원할 수 있습니다. 이러한 기능은 큰 결과가 반환될 때 매우 유용할 수 있습니다.

반면 CLR 테이블 반환 함수는 스트리밍 방식을 사용합니다. 단일 테이블에서 전체 결과 집합을 구체화할 필요는 없습니다. 관리되는 함수에서 반환된 IEnumerable 개체는 테이블 반환 함수를 호출하는 쿼리의 실행 계획에서 직접 호출되며 결과는 증분 방식으로 사용됩니다. 이 스트리밍 모델은 전체 테이블이 채워질 때까지 기다리지 않고 첫 번째 행을 사용할 수 있게 된 직후부터 결과를 사용할 수 있도록 합니다. 반환되는 행의 수가 매우 많은 경우에도 이러한 행을 메모리에서 전체적으로 구체화할 필요가 없는 이 방법이 더 효율적입니다. 예를 들어 관리되는 테이블 반환 함수를 사용하여 텍스트 파일을 구문 분석하고 각 줄을 행으로 반환할 수 있습니다.

테이블 반환 함수 구현

Microsoft .NET Framework 어셈블리의 클래스에서 테이블 반환 함수를 메서드로 구현합니다. 테이블 반환 함수 코드는 IEnumerable 인터페이스를 구현해야 합니다. IEnumerable 인터페이스는 .NET Framework에 정의되어 있습니다. .NET Framework의 배열 및 컬렉션을 나타내는 형식은 이미 IEnumerable 인터페이스를 구현합니다. 이렇게 하면 컬렉션 또는 배열을 결과 집합으로 변환하는 테이블 반환 함수를 쉽게 작성할 수 있습니다.

테이블 반환 매개 변수

테이블 반환 매개 변수는 프로시저 또는 함수에 전달되고 여러 행의 데이터를 서버에 전달하는 효율적인 방법을 제공하는 사용자 정의 테이블 형식입니다. 테이블 반환 매개 변수는 매개 변수 배열과 유사한 기능을 제공하지만 Transact-SQL과 더 유연하고 긴밀한 통합을 제공합니다. 또한 성능도 향상될 수 있습니다. 테이블 반환 매개 변수는 서버 왕복 횟수를 줄이는 데도 도움이 될 수 있습니다. 스칼라 매개 변수 목록과 같이 여러 요청을 서버에 보내는 대신 데이터를 테이블 반환 매개 변수로 서버에 보낼 수 있습니다. 사용자 정의 테이블 형식은 SQL Server 프로세스에서 실행되는 관리되는 저장 프로시저 또는 함수에 테이블 반환 매개 변수로 전달되거나 반환될 수 없습니다. 테이블 반환 매개 변수에 관한 자세한 내용은 테이블 반환 매개 변수 사용(데이터베이스 엔진)을 참조하세요.

출력 매개 변수 및 테이블 반환 함수

출력 매개 변수를 사용하여 테이블 반환 함수에서 정보를 반환할 수 있습니다. 구현 코드의 테이블 반환 함수에 있는 해당 매개 변수는 참조 전달(pass-by-reference) 매개 변수를 인수로 사용해야 합니다. 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 특성 속성으로 표시되어야 합니다.

샘플 테이블 반환 함수

다음 테이블 반환 함수는 시스템 이벤트 로그의 정보를 반환합니다. 함수는 읽을 이벤트 로그의 이름을 포함하는 단일 문자열 인수를 사용합니다.

예제 코드

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로 컴파일한 다음 C 드라이브의 루트 디렉터리에 이 DLL을 복사한 후 그런 다음, 다음 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

다음을 통해 공유