SQL Server 2012 CLR User Defined Functions: Get Started

Author: JJ Bussert Posted In: Custom Development, Digital Transformation

Oct 142013

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. Typically this is 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 to use a CLR user defined function. The problem with this is that it is something 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 implement CLR user defined functions. You can bookmark this 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. The only unique thing 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!");
    }
}

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

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

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, let’s 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.

Business Intelliigence CLR SQL Server 2012 SQL Server Integration Services User Defined Functions Visual Studio 2012 Windows 8

Who We Work With

SQL Server 2012 CLR User Defined Functions: Get Started

Background

Environment

Setup

UDF Examples

Example 1: Scalar-valued UDF

Example 2: Table-valued UDF

Deployment

Closing Thoughts

Capabilities

Contact Us

Careers

Who We Work With

SQL Server 2012 CLR User Defined Functions: Get Started

Background

Environment

Setup

UDF Examples

Example 1: Scalar-valued UDF

Example 2: Table-valued UDF

Deployment

Closing Thoughts

You're on a roll! Try these next:

Capabilities

Contact Us

Careers