CLR User-Defined Functions

Article
11/03/2006

User-defined functions are routines that can take parameters, perform calculations or other actions, and return a result. In Microsoft SQL Server 2005, you can write user-defined functions in any Microsoft .NET Framework programming language, such as Microsoft Visual Basic .NET or Microsoft Visual C#.

CLR Scalar-Valued Functions

A scalar-valued function returns a single value, such as a string, integer, or bit value. In SQL Server 2005, you can create scalar-valued user-defined functions in managed code using any Microsoft .NET Framework programming language. These functions are accessible to Transact-SQL or other managed code. .NET Framework scalar-valued functions are implemented as methods on a class in a .NET Framework assembly. The input parameters and the type returned from a scalar-valued function can be any of the scalar data types supported by SQL Server, except rowversion, text, ntext, image, timestamp, table, or cursor. Scalar-valued functions must ensure a match between the SQL Server data type and the return data type of the implementation method.

When implementing a .NET Framework scalar-valued function in a .NET Framework language, you can include additional information about the function by specifying the SqlFunction custom attribute. This attribute indicates whether or not the function accesses or modifies data, if it is deterministic, and if the function involves floating point operations.

Scalar-valued user-defined functions may be deterministic or non-deterministic. A deterministic function always returns the same result when called with a specific set of input parameters. A non-deterministic function may return different results when called with a specific set of input parameters.

Here is a simple scalar-valued function that accesses data and returns an integer value:

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public class T
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static int ReturnOrderCount()
    {
        using (SqlConnection conn 
            = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(
                "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);
            return (int)cmd.ExecuteScalar();
        }
    }
}

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient

Public Class T
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _
    Public Shared Function ReturnOrderCount() As Integer
        Using conn As New SqlConnection("context connection=true")
            conn.Open()
            Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)
            Return CType(cmd.ExecuteScalar(), Integer)
        End Using
    End Function
End Class

The Transact-SQL query and a sample invocation to register the assembly and UDF are:

CREATE ASSEMBLY FirstUdf FROM 'FirstUdf.dll'
CREATE FUNCTION CountSalesOrderHeader() RETURNS INT 
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount
SELECT dbo.CountSalesOrderHeader()

Note that the function name as exposed in Transact-SQL does not need to match the name of the target public static method.

CLR Table-Valued Functions

A table-valued function (TVF) is a user-defined function that returns a table. Transact-SQL TVFs materialize the results of calling the function into an intermediate table, which can support constraints and unique indexes over the results. These features can be extremely useful when large results are returned. Common language runtime (CLR) TVFs represent a streaming alternative. 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.

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.

[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

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 = UNSAFE
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 User-Defined Aggregates

Aggregate functions perform a calculation on a set of values and return a single value. Traditionally Microsoft SQL Server has supported only built-in aggregate functions, such as SUM or MAX, that operate on a set of input scalar values and generate a single aggregate value from that set. SQL Server common language runtime (CLR) integration now allows developers to create custom aggregate functions in managed code, which can be orders of magnitude faster than using cursors, and is comparable to using SQL Server built-in aggregate functions. These functions are also accessible to Transact-SQL or other managed code.

You can register a type in a CLR assembly as a user-defined aggregate function, as long as it implements the required aggregation contract. This contract consists of the SqlUserDefinedAggregate attribute and the aggregation contract methods. The aggregation contract includes the mechanism to save the intermediate state of the aggregation, and the mechanism to accumulate new values, which consists of four methods: Init, Accumulate, Merge, and Terminate.

The Init method is invoked once for each group that is being aggregated. It should perform any clean-up as necessary from previous uses of this instance and enable it to restart a new aggregate computation.

The Accumulate method is invoked once for each value in the group that is being aggregated. It updates the state of the instance to reflect the accumulation of the argument value being passed in.

The Merge method can be used to merge another instance of this aggregate class with the current instance.

The Terminate method completes the aggregate computation and returns the result of the aggregation.

Here is an example of a user-defined aggregate function that concatenates a set of string values taken from a column in a table:

[C#]

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined, //use custom serialization to serialize the intermediate result
    IsInvariantToNulls = true, //optimizer property
    IsInvariantToDuplicates = false, //optimizer property
    IsInvariantToOrder = false, //optimizer property
    MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
    /// <summary>
    /// The variable that holds the intermediate result of the concatenation
    /// </summary>
    private StringBuilder intermediateResult;
    /// <summary>
    /// Initialize the internal data structures
    /// </summary>
    public void Init()
    {
        this.intermediateResult = new StringBuilder();
    }
    /// <summary>
    /// Accumulate the next value, not if the value is null
    /// </summary>
    /// <param name="value"></param>
    public void Accumulate(SqlString value)
    {
        if (value.IsNull)
        {
            return;
        }

        this.intermediateResult.Append(value.Value).Append(',');
    }

    /// <summary>
    /// Merge the partially computed aggregate with this aggregate.
    /// </summary>
    /// <param name="other"></param>
    public void Merge(Concatenate other)
    {
        this.intermediateResult.Append(other.intermediateResult);
    }

    /// <summary>
    /// Called at the end of aggregation, to return the results of the aggregation.
    /// </summary>
    /// <returns></returns>
    public SqlString Terminate()
    {
        string output = string.Empty;
        //delete the trailing comma, if any
        if (this.intermediateResult != null
            && this.intermediateResult.Length > 0)
        {
            output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
        }

        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
        intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.intermediateResult.ToString());
    }
}

[Visual Basic]

Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.IO
Imports System.Text

<Serializable(), SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls:=True, IsInvariantToDuplicates:=False, IsInvariantToOrder:=False, MaxByteSize:=8000)> _
Public Class Concatenate
    Implements IBinarySerialize

    ''' <summary>
    ''' The variable that holds the intermediate result of the concatenation
    ''' </summary>
    Private intermediateResult As StringBuilder

    ''' <summary>
    ''' Initialize the internal data structures
    ''' </summary>
    Public Sub Init()
        Me.intermediateResult = New StringBuilder()
    End Sub

    ''' <summary>
    ''' Accumulate the next value, not if the value is null
    ''' </summary>
    ''' <param name="value"></param>
    Public Sub Accumulate(ByVal value As SqlString)
        If value.IsNull Then
            Return
        End If

        Me.intermediateResult.Append(value.Value).Append(","c)
    End Sub
    ''' <summary>
    ''' Merge the partially computed aggregate with this aggregate.
    ''' </summary>
    ''' <param name="other"></param>
    Public Sub Merge(ByVal other As Concatenate)
        Me.intermediateResult.Append(other.intermediateResult)
    End Sub

    ''' <summary>
    ''' Called at the end of aggregation, to return the results of the aggregation.
    ''' </summary>
    ''' <returns></returns>
    Public Function Terminate() As SqlString
        Dim output As String = String.Empty

        'delete the trailing comma, if any
        If Not (Me.intermediateResult Is Nothing) AndAlso Me.intermediateResult.Length > 0 Then
            output = Me.intermediateResult.ToString(0, Me.intermediateResult.Length - 1)
        End If

        Return New SqlString(output)
    End Function

    Public Sub Read(ByVal r As BinaryReader) Implements IBinarySerialize.Read
        intermediateResult = New StringBuilder(r.ReadString())
    End Sub

    Public Sub Write(ByVal w As BinaryWriter) Implements IBinarySerialize.Write
        w.Write(Me.intermediateResult.ToString())
    End Sub
End Class

Once you compile the code into MyAgg.dll, you can register the aggregate in SQL Server as follows:

CREATE ASSEMBLY MyAgg FROM 'C:\MyAgg.dll'
GO
CREATE AGGREGATE MyAgg (@input nvarchar(200)) RETURNS nvarchar(max)

EXTERNAL NAME MyAgg.Concatenate

Share via

CLR User-Defined Functions

CLR Scalar-Valued Functions

CLR Table-Valued Functions

CLR User-Defined Aggregates

Additional resources