Export (0) Print
Expand All

CLR Table-Valued Functions

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

Beginning with SQL Server 2005, SQL Server extends the functionality of table-valued functions by allowing you to define a table-valued function in any managed language. Data is returned from a table-valued function through an IEnumerable or IEnumerator object.

NoteNote

For table-valued functions, 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.

Transact-SQL table-valued functions 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 table-valued functions 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 table-valued function, 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 table-valued function could be used to parse a text file and return each line as a row.

Implement table-valued functions as methods on a class in a Microsoft .NET Framework assembly. Your table-valued function 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 table-valued functions that convert a collection or an array into a result set.

Table-valued parameters are user-defined table types that are passed into a procedure or function and provide an efficient way to pass multiple rows of data to the server. Table-valued parameters provide similar functionality to parameter arrays, but offer greater flexibility and closer integration with Transact-SQL. They also provide the potential for better performance. Table-valued parameters also help reduce the number of round trips to the server. Instead of sending multiple requests to the server, such as with a list of scalar parameters, data can be sent to the server as a table-valued parameter. A user-defined table type cannot be passed as a table-valued parameter to, or be returned from, a managed stored procedure or function executing in the SQL Server process. For more information about table-valued parameters, see Table-Valued Parameters (Database Engine).

Information may be returned from table-valued functions using output parameters. The corresponding parameter in the implementation code table-valued function should use a pass-by-reference parameter as the argument. Note that Visual Basic does not support 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:

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

Defining a Table-Valued Function in Transact-SQL

The syntax for defining a CLR table-valued function is similar to that of a Transact-SQL table-valued function, 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;

Table-valued functions 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);

Table-valued functions can return a table when:

  • Created from scalar input arguments. For example, a table-valued function that takes a comma-delimited string of numbers and pivots them into a table.

  • Generated from external data. For example, a table-valued function that reads the event log and exposes it as a table.

Note   A table-valued function 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.

The following table-valued function 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

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

Declaring and Using the Sample Table-Valued Function

After the sample table-valued function has been compiled, it can be declared in Transact-SQL like this:

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

Beginning with SQL Server 2005, on a SQL Server 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, you must use the sp_dbcmptlevel stored procedure to set the database compatibility level to "100".

Visual C++ database objects compiled with /clr:pure 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

The following sample shows a table-valued function that queries a SQL Server database. This sample uses the AdventureWorks Light database from SQL Server 2008. See http://www.codeplex.com/sqlserversamples for more information on downloading AdventureWorks.

Name your source code file FindInvalidEmails.cs or 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

Compile the source code to a DLL and copy the DLL to the root directory of your C drive. Then, execute the following Transact-SQL query.

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

Updated content

Added a sample that shows a SQL Server query.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft