Introduction:

SQL Server 2005, with its built-in T-SQL as well as SQL SERVER Management Studio, is just amazing. You can build your database, create your own procedures, functions, and triggers. You can also access data, process information, and update your records.

Till now, this is all fine. But what if you wanted to perform complex processing on the data, or apply a specific algorithm that requires a lot of loops? You have to deal with a complex T-SQL code and a lot of cursors, which is not recommended for performance issues.

The solution is to use the SQL CLR, which is built into the SQL SERVER 2005. It allows us to run and execute .NET code within SQL Server 2005 environment. SQL CLR enables developers to add user-defined functions, stored procedures and triggers as in T-SQL. It adds the ability to define your own types as well as your own aggregate functions.

Please read this introduction to the SQL CLR for more information.

Simple Example:

This following is an example of how to use .NET code inside SQL Server 2005:

First, run Visual Studio 2008 or 2005, then create a new project from File -> New -> Project.

Form the Project type chose Database project and select the SQL Server Project template and click OK.

Then New Database Reference window is shown you can select the server name and then chose the database you want to work with, or you can attach a new database file to the server, test the connection and if everything is okay, just click OK. A dialog box will ask you if you want to enable debugging the .NET code for the current connection.

Now to add a user-defined function, right-click on the project title then select Add -> User-Defined Function

A new C# class will be opened with the following code:

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 SqlString HelloWorld()
    {
        return new SqlString("Hello");
    }
}; 

Notice the namespaces that are already added to the class to make use of SQLCLR.

As we can see above, the function should be statice and should be attributed to [Microsoft.SqlServer.Server.SqlFunction] just to tell the SQLCLR to consider this function a user-defined function so we can add it and use it in our T-SQL code, we can also add any other not attributed functions, but will not be recognized as user-defined functions by the SQLCLR.

This user-defined function returns a string, but it’s not of the class String. It’s from the class SqlString. The integer is also SqlInt32 etc.

Now, we have implemented our function. We can build our .NET class, and it will be compiled as a .NET library class.

The last step now is to deploy this user-defined function. In Visual Studio, it’s so easy just to select Deploy Solution. VS will take care of creating an assembly in the SQL Server and will also create the user-defined function and attached it with the function in that assembly, of course, we can do it using T-SQL, but VS takes care of everything.

Before calling the function, we have to enable the SQLCLR inside the SQL Server because it’s disabled by default for security reasons.

sp_configure 'clr_enabled',1
reconfigure

What else we have to do? actually nothing, but calling our user-defined function that is written in C# but inside the SQL Server 2005 using a single select as follows:

select dbo.HelloWorld()

Conclusion

In the end, SQL CLR is an excellent feature of the SQL Server 2005 that enables the use of the great features of the .NET Framework and classes. It doesn’t require too much code but results in a lot of benefits.