SQLCLR

 Initial Setup

For a quick start guide into SQLCLR, the easiest first step will mostly like be creating a user defined function.

To get started, I’m using the following installations:

• SQL Server Data Tools for Visual Studio 2012 (http://msdn.microsoft.com/en-us/jj650015)
• Microsoft SQL Server Data Tools Business Intelligence (http://www.microsoft.com/en-us/download/details.aspx?id=36843)
• SQL Server 2012
• A database to deploy to, I’ll be using Adventure Works 2012 (http://msftdbprodsamples.codeplex.com/releases/view/93587)

Creating the Project

To get started, open up SQL Server Data Tools for Visual Studio 2012. Select new project and go to the Other Languages in the templates section. Name the project (SQLCLRDemo):

Once in the project, in the Solution Explorer, right click on the project and select Add then New Item:

There are 5 basic types of SQL CLR objects that can be developed in C# for SQL Server:

• Aggregate
• Stored Procedure
• Trigger
• User Defined Function
• User Defined Type

 Creating a User Defined Function

Let’s start with the easiest and most used SQLCLR type, User Defined Function. For this example I’ll create a simple function called IsLeapYear which will accept a year as an integer value and return Boolean:

I’m not going to do any special coding for this example, since C#’s DateTime library has a nice slew of usable functions. Here is the user defined function template with the added code:

//------------------------------------------------------------------
// <copyright file="CSSQLFunction.cs" company="Microsoft">
//    Copyright (c) Microsoft Corporation.  All rights reserved.
// </copyright>
//------------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
     [Microsoft.SqlServer.Server.SqlFunction]
     public static SqlBoolean IsLeapYear(SqlInt32 year)
     {
        // Put your code here
        return DateTime.IsLeapYear((int)year);
      }
}

As you may have noticed the data types used in SQL Server and C# are different, where SQL Server’s data types are given the prefix of SQL. When using built-in C# libraries, you will need to cast to a C# type.  The return data type, for the function, will need to be a SQL data type (i.e. SqlBoolean).

So build the project, to verify that code is correct. Before deploying, you may want to check and verify the project’s properties. (PROJECT > SQLCLRDemo Properties¦) Here you will be able to set the target SQLServer platform, default schema type, database settings, permission level, etc¦

Deploying to SQL Server

To publish right-click the project and select Publish¦.

Select the target database and then click publish. Verify in the Data tools Operations pane that everything goes over smoothly:

You should then see the new scalar-valued function in the database (you may have to refresh the list):

Testing the SQLCLR function

So far so good, let’s test the function:

USE [AdventureWorks2012]
GO

;WITH cteYears AS
(
	SELECT 2013 as iYear
	UNION ALL
	SELECT 2012 as iYear
	UNION ALL
	SELECT 2011 as iYear
	UNION ALL
	SELECT 2010 as iYear
)

SELECT 
iYear,
dbo.IsLeapYear(iYear)
FROM cteYears
GO

Uh, oh. We get an error:

Simple enough, just need to enable CLR Integration (http://technet.microsoft.com/en-us/library/ms131048.aspx) just run this script on the targeted database:

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

Re-run the test script and finally get our results:

Conclusion

Creating some SQLCLR functions could make SQL development easier and provide a faster technique versus regular SQL-Server functions.

The other SQL CLR types can get more complicated with higher security needs, potential un-safe code, serializations, and importing dlls to SQL-Server, as some examples. I will try to do some more in-depth SQLCLR tutorials in the future that will run into some of these situations.