Enhance SQL Server With a CLR Function

Posted on July 22, 2015

Sometimes you can’t easily do what you need in T-SQL; fortunately you can create new functionality in a .NET project and invoke it from your stored procedures or SQL. You can build a Visual Studio project that you can publish to your SQL Server database and use like a normal function or stored proocedure.

In principal, it is not too hard to extend SQL Server, but there are several frustrating issues which I explain in the post below. You will save your own frustration if you learn from my efforts! For example, projects that won’t build but have no error messages, fixing apparent damage to Visual Studio, and explicitly turning-on CLR in SQL Server.

My Business Need: Enhanced String Comparison

I’m importing legacy data and discovered some data that should be identical but isn’t, due to similar punctuation marks! For example ASCII as at least three different single quote marks: a ‘straight’ single quote, an open single-quote and a close single-quote. Like the following:

‘`´

Also, there are a couple different ASCII hyphens: a normal version, and an extra-wide one. Presumably there are other punctuation marks that look similar to humans but aren’t the same to SQL Server

Names the Same Except for Punctuation

Normally I don’t care about the punctuation, but I have some key fields with different punctuation, such as:

• Joe’s Bar and Grill (straight quote)
• Joe´s Bar and Grill (close quote)

The problem arises when I try to prevent duplicates: the two names above are the same to me, but SQL Server says they are different, because of the differing punctuation.

At first, I thought I could avoid the issue by specifying a collate clause in my SQL, but that that is actually designed for comparisons in different languages and doesn’t help here.

.NET to the Rescue!

Fortunately, .NET has a CompareOptions object which allows you to compare strings with a ‘IgnoreSymbols’ setting. Not perfect, because it only ignores punctuation; it isn’t smart enough to compare the three kinds of single quotes against each other but not against other punctuation. However, I judged it good enough for my purposes. Now, I will use that capability  to build a “SQL CLR C# User Defined Function in SQL SERVER”.

Start by creating a SQL Server Database Project

After starting your project, make sure you target the correct version of SQL Server, in my case, SQL Server 2012.

Now, choose the type of functionality you wish to add to SQL Server, in my case, a User-Defined Function. Add a new item to your project and choose the template indicated in the screen shot below.

Now, Write Some Code!

You will get a stub function; you should fill-in the functionality to meet your needs. In my case, I will return a 0 if two strings are equivalent, a -1 if the first string is smaller, and a 1 if the first string is bigger.

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

public partial class UserDefinedFunctions
{
  [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, SystemDataAccess = SystemDataAccessKind.None, IsPrecise = true)]
  public static SqlInt16 PunctuationInsensitiveComparison(SqlString s1, SqlString s2)
  {
    try
    {
      if (s1.IsNull && s2.IsNull)
        return 0;
      else if (s1.IsNull)
        return -1;
      else if (s2.IsNull)
        return 1;
      else
      {
        CompareOptions myOpts = CompareOptions.IgnoreSymbols | CompareOptions.IgnoreWidth | CompareOptions.IgnoreCase | CompareOptions.IgnoreNonSpace;
CompareInfo ci = CompareInfo.GetCompareInfo("en-US");
        SqlInt16 compareResult = (SqlInt16)ci.Compare((string)s1, (string)s2, myOpts);
        return compareResult;
      }
    }
    catch (System.Exception ex)
    {
      return -1;
    }
  }

Highlights

• The tag decorating the start of my function tells SQL Server what kind of usage to expect, I believe it is used for optimization

  [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, SystemDataAccess = SystemDataAccessKind.None, IsPrecise = true)]

• Note the use of SQL Server data types, such as SqlString and SqlInt16. This is important for optimzation
• Also note that I took pains to check for nulls, for example:

   else if (s1.IsNull)

• This will work when String.IsNullOrEmpty fails!

Avoid this Gotcha!

At this point, you can try to build, but, even though the syntax is correct, it failed for me. In fact, it failed without an error message! After some research, I discovered I needed to install SQL Server Database Tooling, which you can get at this url: https://msdn.microsoft.com/en-us/dn864412.

Next Problem

Installing this extension may mess-up your instance of Visual Studio, it certainly did mine! You may get an error ‘No exports were found that match the constraint contract name‘ After some research, I found the solution was to rename or delete the following folder:

• C:\Users\YourUserName\AppData\Local\Microsoft\VisualStudio\12.0\ComponentModelCache

Now Publish Your Solution

Right-click on your project and choose ‘Publish’ from the context menu.

You will have to browse for your server/database by clicking the ‘edit’ button; afterwards, you can either generate a script or else click the publish button. If you generate a script, you can tweak it, afterwards you will need to run it by clicking the button indicated in the following screen shot.

After publishing, your new function will appear in SQL Server!

One Last Step Before You Can Use it

The last thing to do is enable CLR functionality in your database instance, by running the following two commands:

sp_configure 'clr enabled', 1
Reconfigure

Now, you can use your new function in SQL, like the following simple example:

if dbo.PunctuationInsensitiveCompare('Jim''s Bar', 'Jim´s Br') = 0
   Print 'Same'
Else
   Print 'Different'

Microsoft documentation states that CLR functions can be more efficient than the equivalent in a normal Scalar-valued function, and my test confirmed that statement. However, if you aren’t careful about null checking, use the wrong data types, or generate errors, it can be much slower!

Summary

If you have a task that is hard in SQL but easy in .NET, you can build a CLR function and publish it to SQL Server. Things like string manipulation come to mind. You will build a SQL Server Database project, add an item ‘SQL CLR C# User-Defined Function’ and publish it to your database. There are several gotchas involved, so be careful and be sure to use System Restore Points and other backup techniques.