Jaa

CLR Table-Valued Functions

  • Artikkeli

A table-valued function (TVF) is a user-defined function that returns a table.

Microsoft SQL Server 2005 extends the functionality of TVFs by allowing you to define a TVF in any managed language. Data is returned from a TVF through an IEnumberable or IEnumerator object.

Note

For TVFs, the columns of the return table type cannot include timestamp columns or non-Unicode string data type columns (such as char, varchar, and text). The NOT NULL constraint is not supported.

Differences Between Transact-SQL and CLR Table-Valued Functions

Transact-SQL TVFs materialize the results of calling the function into an intermediate table. Since they use an intermediate table, they can support constraints and unique indexes over the results. These features can be extremely useful when large results are returned.

In contrast, CLR TVFs represent a streaming alternative. There is no requirement that the entire set of results be materialized in a single table. The IEnumerable object returned by the managed function is directly called by the execution plan of the query that calls the TVF, and the results are consumed in an incremental manner. This streaming model ensures that results can be consumed immediately after the first row is available, instead of waiting for the entire table to be populated. It is also a better alternative if you have very large numbers of rows returned, because they do not have to be materialized in memory as a whole. For example, a managed TVF could be used to parse a text file and return each line as a row.

Implementing Table-Valued Functions

Implement TVFs as methods on a class in a Microsoft .NET Framework assembly. Your TVF code must implement the IEnumerable interface. The IEnumerable interface is defined in the .NET Framework. Types representing arrays and collections in the .NET Framework already implement the IEnumerable interface. This makes it easy for writing TVFs that convert a collection or an array into a result set.

Output Parameters and Table-Valued Functions

Information may be returned from TVFs using output parameters. The corresponding parameter in the implementation code TVF should use a pass-by-reference parameter as the argument. Note that Visual Basic does not suport output parameters in the same way that Visual C# does. You must specifiy the parameter by reference and apply the <Out()> attribute to represent an output parameter, as in the following:

Visual Basic

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

Defining a TVF in Transact-SQL

The syntax for defining a CLR TVF is similar to that of a Transact-SQL TVF, with the addition of the EXTERNAL NAME clause. For example:

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

TVFs are used to represent data in relational form for further processing in queries such as:

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

TVFs can return a table when:

  • Created from scalar input arguments. For example, a TVF that takes a comma-delimited string of numbers and pivots them into a table.
  • Generated from external data. For example, a TVF that reads the event log and exposes it as a table.

Note   A TVF can only perform data access through a Transact-SQL query in the InitMethod method, and not in the FillRow method. The InitMethod should be marked with the SqlFunction.DataAccess.Read attribute property if a Transact-SQL query is performed.

A Sample Table-Valued Function

The following TVF returns information from the system event log. The function takes a single string argument containing the name of the event log to read.

Sample Code

[C#]

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, Environment.MachineName).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;
    }
}

[Visual Basic]

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, Environment.MachineName).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
Declaring and Using the Sample TVF

Once the sample TVF has been compiled, it may be declared in Transact-SQL like this:

CREATE ASSEMBLY tvfEventLog 
FROM'D:\assemblies\tvfEventLog\tvfeventlog.dll' 
WITH PERMISSION_SET = SAFE
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

Note

On a SQL Server 2005 database with a compatibility level of "80," you cannot create managed user-defined types, stored procedures, functions, aggregates, or triggers. To take advantage of these CLR integration features of SQL Server 2005, you must use the sp_dbcmptlevel (Transact-SQL) stored procedure to set the database compatibility level to "90.".

Note

Managed Visual C++ database objects that have been compiled with the /clr:pure Visual C++ compiler option are not supported for execution on SQL Server 2005. For example, such database objects include table-valued functions.

To test the sample, try the following Transact-SQL code:

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

See Also

Other Resources

Database Compatibility Level Option

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Added note on database compatibility level.
Updated content:
  • Changed permission set for TVF sample registration from UNSAFE to SAFE.
Updated content:
  • Added note about managed Visual C++ routines compiled with the /clr:pure option.