SQL CLR overview
Introduction:
SQL Server 2005 with it's built in T-SQL as well as SQL SERVER Management Studio are 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 a complex processing a on the data, or apply a certain algorithm that require a lot of loops then 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 that allows us to run and execute .NET code with in SQL Server 2005 environment.SQL CLR enables developers to add user-defied functions,stored procedures and triggers as in T-SQL, but what it adds is 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 on 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 every thing is good, just click OK and 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 name spaces that are already added to the class in order to make use of SQLCLR.
As we can see above the function should be statice and should be attributed with [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 of 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 go to Build menu and then select Deploy Solution, then 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 every thing.
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
At the end, SQL CLR is a great 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 a lot of benifits.