Getting Started With SQL Server 2012 CLR User Defined Functions

Introduction

When I’m on a data project such as using SSIS (SQL Server Integration Services) to perform ETL tasks I try to keep my code in SQL as much as possible because typically that’s the strongest skillset of someone that works with SSIS however sometimes that is not good enough.  If you run across something that is just not practical to do in SQL Server via straight T-SQL one solution can be using a CLR user defined function, problem with this is it is something that you probably don’t do very often so you may forget the specifics on how this works.  Here is a quick tutorial on how to do implement CLR user defined functions that you can bookmark to hopefully save you from searching the web next time you need to do this.

Background

A User Defined Function (or UDF for short) is exactly what it says, some function callable in SQL that is not part of the system functions.  Great examples of this are when you are implementing system functionality that may exist in another database server to yours.  Or if you have some complex algorithm that you want to re-use throughout the database, 

Environment

• SQL Server 2012
• Visual Studio 2012 Premium
• SQL Server 2012 Data Tools – Business Intelligence for Visual Studio 2012
• Windows 8

Setup

First we want to create a new SQL Server Database Project, for this example I’ll call it simply “UDF_Blog”

Once you have a project lets create two new SQL CLR C# User Defined Functions at the root of the project:

• ScalarUDF.cs
• TableUDF.cs

That’s it! we have a new C# file for each of the two examples that I will be explaining.

UDF Examples

Example 1: Scalar-valued UDF

A Scalar valued UDF is a fancy name for a function that returns a single value, they can return any value that can be cast into a SQL Server datatype (int, bit, varchar, etc.), for this example we’ll return a simple string that will be cast as an nvarchar.  Not a very exciting example but you can see where you can use any C# code to do any complex processing to create the string that is returned.  Only thing unique here is the [SqlFunction] attribute which identifies that this static method defines a UDF.

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

public partial class UserDefinedFunctions
{
    [SqlFunction]
    public static SqlString ScalarUDF()
    {
        return new SqlString ("Hello World!");
    }
}

Example 2: Table-valued UDF

Another common type of UDF is a Table valued UDF which is simply a function that generates a table of data.  Obviously it goes without saying that returning a grid of data is more complex than a single value and as a result the code to accomplish it.

using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    private class TableType
    {
        public SqlInt32 Sequence { get; set; }
        public SqlString Text { get; set; }
    }

    public void TableUDF_FillRow(
        object tableTypeObject, 
        out SqlInt32 sequence, 
        out SqlString text)
    {
        var tableType = (TableType)tableTypeObject;

        sequence = tableType.Sequence;
        text = tableType.Text;
    }

    [SqlFunction(
        DataAccess = DataAccessKind.Read, 
        TableDefinition = "Sequence int, Text nvarchar(100)", 
        FillRowMethodName = "TableUDF_FillRow")]
    public static IEnumerable TableUDF()
    {
        var tableResults = new ArrayList
        {
            new TableType {Sequence = 1, Text = "Hello"},
            new TableType {Sequence = 2, Text = "World"},
            new TableType {Sequence = 3, Text = "!"}
        };

        return tableResults;
    }
}

There are three key components of this one.

• First create a private class that defines a Sql type for every “column” that will be in the table that you return
• Second a FillRow method that SQL Server will use to take the returned IEnumerable into the various SQL column types that the table is made of one row at a time
• Third the actual method where all of the UDF logic goes.  Take whatever action you want to create an IEnumerable of TableTypes that will make up the returned data

This is just a quick overview to get you started so you’ll need to check MSDN for details. 

Deployment

Now that we have two bare bone UDFs lets compile them and deploy them to SQL Server.  After compiling the project and getting the DLL run the following from within SQL Server.

-- Enable CLR 
EXEC sp_configure 'show advanced options' , '1'
GO
RECONFIGURE
GO
EXEC sp_configure 'clr enabled' , '1'
GO
RECONFIGURE
GO
EXEC sp_configure 'show advanced options' , '0';
GO
-- Install Assembly
CREATE ASSEMBLY UDF_Blog FROM 'C:\UDF_Blog.dll'
GO
-- Create ScalarUDF
CREATE FUNCTION [dbo].[ScalarUDF]()
RETURNS nvarchar(100)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME UDF_Blog.UserDefinedFunctions.ScalarUDF;
GO
-- Create TableUDF
CREATE FUNCTION [dbo].[TableUDF]()
RETURNS table 
(
    Sequence INT,
    Text NVARCHAR(100)
) 
AS
EXTERNAL NAME UDF_Blog.UserDefinedFunctions.TableUDF;
GO

The comments really speak for themselves.  If this is the first CLR function that has been deployed to the server you need to enable that functionality, install the assembly that contains the compiled code, and install the functions one by one so they can be used.  When all of this is done you can execute them just like you would any other pure SQL UDF.

Closing Thoughts

Obviously these are very simplistic examples of what you can do but hopefully you can see the added flexibility that this option provides.  I will caution you to not overuse this, even though it is great to have this option it does add considerable complexity to your system.  if you can do in two lines of SQL what you are able to do in one line of C# it is not worth it, However for more complex things that you can do in maybe ten lines of C# compared with a hundred in SQL the trade off is more than worth it.