A Quick Intro into SQLCLR User Defined Functions
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.