March 12, 2013

CLR Functions in SQL Server – A Tutorial

Post by: Chris Miller

Introduction

In every version of SQL Server since SQL Server 2005, the ability to write stored procedures and functions in languages besides T-SQL has been present. Functions, Stored Procedures, and even custom aggregation functions can be written in C# or Visual Basic.

Although T-SQL is highly efficient at accessing data, it is not (by design) a full programming language, and therefore, may not necessarily be the appropriate choice for every programming task. However, since T-SQL is the natural language of choice for most SQL developers, T-SQL is often chosen over other options – sometimes at the expense of readability and performance.

The “Split” function is one simple example. In almost any programming project you will eventually need to split a delimited string into its separate elements. In the Business Intelligence world, this may occur when passing multi-valued parameters from an SSRS report to a stored procedure. However, since T-SQL lacks a built-in Split function, developers are required to write their own version – usually in T-SQL.

A quick internet search for “T-SQL Split Function” will yield a wide variety of highly complex T-SQL implementations. These range from a dozen lines of SQL code to sometimes more than one hundred. While these solutions are often very clever and well written, they are limited to the T-SQL language subset and therefore end up being more complex than their equivalent versions in other programming languages.

CLR Function Tutorial

In this tutorial we will use Visual Studio 2010 and SQL Server 2012 to create a simple Common Language Runtime (CLR) Split function – written in C#. This article assumes some familiarity with Visual Studio and the C# programming language.

1. To begin, launch Visual Studio. Then select “New Project”, and choose “Visual C#” and then “Class Library”. Name your library “CLRDemo”…

 2. By Default, Visual studio will create an empty class named “class.cs”. Rename this file to “CLRFunctions.cs” by right clicking on the file and selecting rename… 

3. Double click on the “CLRFunctions.cs” file (the file you just renamed).  Replace the text with the code shown below. The code is commented if you wish to understand a bit more about what is going on in the code…

using System;
using System.Collections;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
 
namespace CLRDemo
{
    public static class CLRFunctions
    {
 
        //SQL Functions require an additional "SqlFunction" Attribute.
        //This attribute provides SQL server with additional meta data information it needs
        //regarding our custom function. In this example we are not accessing any data, and our
        //function is deterministic. So we let SQL know those facts about our function with
        //the DataAccess and IsDeterministic parameters of our attribute.
        //Additionally, SQL needs to know the name of a function it can defer to when it needs
        //to convert the object we have returned from our function into a structure that SQL
        //can understand. This is provided by the "FillRowMethodName" shown below.
        [SqlFunction(
            DataAccess = DataAccessKind.None,
            FillRowMethodName = "MyFillRowMethod"
            ,IsDeterministic=true)
        ]
        //SQL Functions must be declared as Static. Table Valued functions must also
        //return a class that implements the IEnumerable interface. Most built in
        //.NET collections and arrays already implement this interface.
        public static IEnumerable Split(string stringToSplit, string delimiters)
        {
            //One line of C# code splits our string on one or more delimiters...
            //A string array is one of many objects that are returnable from
            //a SQL CLR function - as it implements the required IEnumerable interface.
            string[] elements = stringToSplit.Split(delimiters.ToCharArray());
            return elements;
        }
        //SQL needs to defer to user code to translate the an IEnumerable item into something
        //SQL Server can understand. In this case we convert our string to a SqlChar object...
        public static void MyFillRowMethod(Object theItem, out SqlChars results)
        {
            results = new SqlChars(theItem.ToString());
        }
 
    }
}

4. From the Build menu, select “Build Solution”…

 Once the project is compiled, locate your bin directory, and copy the newly created “CLRDemo.dll” file to a location accessible by the SQL server. In our example we will assume you have deployed the file to a directory named C:\CLRLibraries on the SQL server itself.

5. Now launch SQL Server Management Tools and connect to the SQL server where you have just deployed the DLL.

6. By default, CLR integration is disabled on SQL Server. If necessary, execute the following commands to enable CLR integration…

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'clr enabled', 1
RECONFIGURE
GO
sp_configure 'show advanced options', 0
RECONFIGURE
GO

7. Now we have to tell SQL Server about the presence of our new dll…

Create Assembly CLRDemo from 'c:\CLRLibraries\CLRDemo.dll' with Permission_set = SAFE
GO

8. We also have to tell SQL Server about the function itself. This syntax is similar to creating a standard function, except that we use the “External” keyword to defer the actual program logic to our new dll…

Create Function Split(@StringToSplit nvarchar(max), @splitOnChars nvarchar(max) )
       returns Table (
              Results nvarchar(max)
       )
       AS
              External name CLRDemo.[[CLRDemo.CLRFunctions]].Split
       GO

9. Finally, you can test the new split function…

select * from dbo.Split('1,2,3,4,5:6:7~8~9',',:~')

Results: 

Why Use CLR Functions?

There are, of course, reasons both for and against using CLR functions in your SQL project. The disadvantages may include unfamiliarity and the additional complexity in creating a CLR function. There is also overhead in deferring to the .dll from T-SQL. For this reason calling T-SQL functions for simple tasks will be more efficient than making a call to the .NET framework. For example, when calling our new split function with very small strings, it performs rather modestly when compared to a similar T-SQL implementation. However as the size of the strings supplied to the function increases, the relative impact of this overhead diminishes and the simplicity of our CLR version combined with the powerful performance of .NET results in a faster, more efficient result.

Additionally .NET can simply provide for many capabilities simply not available in T-SQL. It can perform complex string manipulations and access web services with ease. It also offers object oriented programming capabilities like polymorphism and inheritance which allow for cleaner more organized code. It is also has robust security features and can also offer a more secure alternative than Extended Stored Procedures.

The next time you find yourself trying to figure out how to implement a complex piece of logic in a T-SQL function or stored procedure, you may wish to consider the .NET CLR as a simple and efficient alternative.

Relevant Insights

Are You Falling into the Cloud Conversation Gap?

Most conversations about migrating services to the cloud focus on migration of application workloads and data. The vision is often...

How Aggregation Tables Improve Performance for Power BI Reports

In this blog, Marcus Radue, Solution Architect, offers high-level guidance in the advantages of aggregation tables in Power BI. For a...

Getting Started with Azure DevOps – Views

Like most Microsoft products, there are many ways to look at or perform the same activities. Azure DevOps is no...
X