Implementing a CLR User Defined Function (UDF) in SQL 2008 R2 is very easy.  Note that this code is not limited to 2008 R2 or UDFs, and implementing CLR stored procedures is just as simple.

The steps are as follows:

1.  Create you class with all desired methods / functionality. Note that your classes and methods should be static!

2.  Add appropriate attributes to the methods you want to utilize as CLR objects.  For a UDF, you add the [SqlFunction] attribute.  For a CLR stored procedure, you would use [SqlProcedure].  Note that these attributes are in the Microsoft.SqlServer.Server namespace.

3.  It’s considered best practice to install register your assembly with SQL Server using Partial Trust.  To that end, you must Allow Partially Trusted Callers in your assembly’s AssemblyInfo class.  Do this my simply adding [assembly: AllowPartiallyTrustedCallers].

   1: using System;

   2: using System.Collections.Generic;

   3: using System.Linq;

   4: using System.Text;

   5: using Microsoft.SqlServer.Server;

   6:  

   7: namespace Demo.ClrUdf

   8: {

   9:     public static class MyClrUdfClass

  10:     {

  11:         [SqlFunction]

  12:         public static string HelloWorld()

  13:         {

  14:             return "giddyup";

  15:         }

  16:     }

  17: }

   1: using System.Reflection;

   2: using System.Runtime.CompilerServices;

   3: using System.Runtime.InteropServices;

   4:  

   5: // General Information about an assembly is controlled through the following 

   6: // set of attributes. Change these attribute values to modify the information

   7: // associated with an assembly.

   8: [assembly: AssemblyTitle("Demo.ClrUdf")]

   9: [assembly: AssemblyDescription("")]

  10: [assembly: AssemblyConfiguration("")]

  11: [assembly: AssemblyCompany("Microsoft")]

  12: [assembly: AssemblyProduct("Demo.ClrUdf")]

  13: [assembly: AssemblyCopyright("Copyright © Microsoft 2011")]

  14: [assembly: AssemblyTrademark("")]

  15: [assembly: AssemblyCulture("")]

  16:  

  17: // Used becuase we're not giving the assemby full trust in SQL

  18: [assembly: AllowPartiallyTrustedCallers]

  19:  

  20:  

  21: // Setting ComVisible to false makes the types in this assembly not visible 

  22: // to COM components.  If you need to access a type in this assembly from 

  23: // COM, set the ComVisible attribute to true on that type.

  24: [assembly: ComVisible(false)]

  25:  

  26: // The following GUID is for the ID of the typelib if this project is exposed to COM

  27: [assembly: Guid("63b93754-c707-4610-b1f4-c6e5949c0a99")]

  28:  

  29: // Version information for an assembly consists of the following four values:

  30: //

  31: //      Major Version

  32: //      Minor Version 

  33: //      Build Number

  34: //      Revision

  35: //

  36: // You can specify all the values or you can default the Build and Revision Numbers 

  37: // by using the '*' as shown below:

  38: // [assembly: AssemblyVersion("1.0.*")]

  39: [assembly: AssemblyVersion("1.0.0.0")]

  40: [assembly: AssemblyFileVersion("1.0.0.0")]

4.  Sign your assembly.  The easiest way to do this is to use a new string key name file by right clicking on your project, click on the Signing Tab, and click Sign the assembly.  This will create a PFX certificate in your project folder that is used to create the signature.

5.  You’re done with the assembly.  Now on to SQL Server.  First, you need to make sure that the CLR is enabled on the server.

   1: -- First, run this to enable the CLR.  This only has to be done once.

   2: sp_configure 'show advanced options', 1;

   3: GO

   4: RECONFIGURE;

   5: GO

   6: sp_configure 'clr enabled', 1;

   7: GO

   8: RECONFIGURE;

   9: GO

  10:  

  11:  

  12:  

  13:  

6.  Next, you must install / register your assembly with SQL Server.  This is simple.

   1: -- Run this, changing the names and paths appropriately (path is relative to the server)

   2: -- Note the Safe permission set- this is best practice

   3: -- Thus, you must set "[assembly: AllowPartiallyTrustedCallers]" in your AssemblyInfo.cs (and all referenced assemblies

   4: -- Note that this will automatically register dependent / referenced assemblies

   5:  

   6: USE [MyDatabase]

   7:  

   8: CREATE ASSEMBLY DemoUserDefinedFunctions

   9: FROM 'D:\Demo\Demo.ClrUdf.dll'

  10: WITH PERMISSION_SET = SAFE

  11:  

  12:  

  13: --DROP ASSEMBLY DemoUserDefinedFunctions

  14:  

  15: --SELECT * FROM sys.assemblies

7.  Finally, Create the CLR UDF or CLR Stored Procedure.  Here’s an example of creating a UDF with the assembly we just registered.  I used a simple Demo class for this blog, but I left one my of implementation of a real UDF in this sample so you can see how to pass parameters, etc.  Note, the only thing that might trick you up a big is the return type of the methods.  For strings, it has to support Unicode, so use nvarchar().

   1: -- Enable the CLR if it isn't already done

   2: -- Register the assembly

   3: -- Change the names and parameters of the UDF appropriatley

   4: -- Input and output must match the method

   5: -- Note the SQL to CLR mappings are obviously important- string only maps to unicode so we'll have to convert in our packages

   6:  

   7: USE [MyDatabase]

   8:  

   9: SET ANSI_NULLS ON

  10: GO

  11: SET QUOTED_IDENTIFIER ON

  12: GO

  13:  

  14: --CREATE FUNCTION EncryptUsernamePassword(@clearText nvarchar(255)) RETURNS nvarchar(255) 

  15: --AS EXTERNAL NAME XXXUserDefinedFunctions.[XXX.Database.Clr.UserDefinedFunctions.XXXCryptography].EncryptUsernamePassword

  16: --GO

  17:  

  18: CREATE FUNCTION HelloWorld() RETURNS nvarchar(255) 

  19: AS EXTERNAL NAME DemoUserDefinedFunctions.[Demo.ClrUdf.MyClrUdfClass].HelloWorld

  20: GO

  21:  

  22: -- DROP FUNCTION EncryptUsernamePassword

  23:  

8.  You’re done.  Just call your UDf or sproc from your T-SQL.  EzPz.

Happy Coding,

Tom Hundley

We are often faced with a requirement of searching a single column by multiple values.  For example, one of my customers wanted to pass a list of part numbers to an inventory database and get all the part information for that list.  There are several good ways of accomplishing this, but one way I discovered that I like a lot is to accept a comma delimited list as the parameter and then convert it to XML to be used in your query.  Better yet, you could simply accept XML as the parameter.  I’ll use an example of the former methodology though, because I can see a lot of practical uses for it such as building a stored procedure and letting a user pass such as list to SSRS, whereas using XML in that scenario would impractical (unless it was done programmatically in which case would be the ideal way of doing it).

DECLARE @EmployeeIds varchar(8000)
SET @EmployeeIds = '1,4,5,6'

DECLARE @Xdoc int
DECLARE @EmployeeIdXml varchar(8000)

SET @EmployeeIdXml = '<Employees><Employee EmployeeId="' + REPLACE( @EmployeeIds, ',', '"></Employee><Employee EmployeeId="') + '"></Employee></Employees>'

EXEC sp_xml_preparedocument @Xdoc OUTPUT, @EmployeeIdXml
SELECT EmployeeID, LastName, FirstName, Title FROM Employees
WHERE EmployeeID IN
      (
            SELECT EmployeeID
            FROM OPENXML(@Xdoc, '/Employees/Employee', 1)
            WITH(EmployeeId int '@EmployeeId')
      )

EmployeeID  LastName             FirstName  Title

----------- -------------------- ---------- ------------------------------

1           Davolio              Nancy      Sales Representative

4           Peacock              Margaret   Sales Representative

5           Buchanan             Steven     Sales Manager

6           Suyama               Michael    Sales Representative

Tom Hundley
Elegant Software Solutions, LLC

Here is a script to rebuild all of your indexes on your database. I find that indexes are often ignored once an application moves to production. It's a good idea to evaluate your usage patterns and setup automated maintenance jobs to proactively rebuild indexes before fragmentation has a chance to adversely effect performance. This is especially important if you are using Guids as primary keys and don't move the clustered index to a more logically sequential column.

 -- Show fragmentation for all tables
EXEC sp_MSforeachtable @command1="print '?' DBCC SHOWCONTIG('?')"

--Rebuild all indexes (note this method locks the tables while the indexes are being rebuilt)

USE [myDatabase]
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR

SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

Tom Hundley

Elegant Software Solutions, LLC