Introduction

SQLCLR is the marriage of SQL Server and the .NET execution environment. In simple terms; it allows us to write code modules such as stored procedures, user-defined functions in the C# language. It however, does not mean that C# is going to replace T-SQL. T-SQL is still the best way to access data. Hosting the .NET Framework (CLR) within SQL Server opens up a new world for developers; giving them freedom to program SQL Server.

What about Extended Stored Procedures? Extended Stored Procedures had the same idea behind it, but it still ran as a separate process (out-of-process). But with SQLCLR, C# code will run within the SQL Server process (in-process). You might say in-process is a bit of a dangerous implementation. But, I would say, "NO". This is because of the built-in features of the .NET Framework. It gives better security, memory management, thread management etc.

Hosting CLR within SQL server (SQLCLR) provides the following benefits:

• Type Safety - through Common Type System (CTS)
• Rich data structures provided with the .NET Framework (classes, structures)
• Access to .NET Framework Base Class Libraries
• Simple to write - writing extended stored procedures require sound knowledge of C++
• Intermediate Language Verification - code compiled to MSIL assemblies
• Second level security - through Code Access Security
• Validated properties
• Easy administration
• Better performance for process-intensive code

Moreover, developers can write more robust code modules using any .NET language and do wonders which T-SQL could not do. Code modules are stored and run from within a SQL Server database instead of the file system.

Will DBAs allow us to write code modules and store them in the database and run them within SQL Server? DBAs need not worry about these code modules. The SQL Server Hosting Layer manages most of the things like assembly loading, memory management, threading, security, etc., so that it prevents dangerous behaviors of the code modules.

The following table summarizes the different code modules that we can write with T-SQL & SQLCLR:

Configuring SQLCLR in SQL Server

Enabling the SQL Server instance to use SQLCLR is normally a one time activity. By default this is disabled as a security feature of SQL Server. The fact is SQL has lots of services that are installed but not enabled by default, to protect the server from attacks.

If you are planning to run powerful/robust code modules with SQLCLR, first you need to enable the SQLCLR feature. This can be done either through GUI Configuration tools or using T-SQL.

1. If you are using GUI Configuration tools:
   
   • Select Start → Programs → Microsoft SQL Server 2005 → Configuration Tools → SQL Server Surface Area Configuration
   • Choose Surface Area Configuration for Features
   • From the list of features installed, select CLR Integration
   • Check Enable CLR Integration
     
2. If you are using T-SQL:
   
   • Login to your SQL Server instance using SQL Server Management Studio
   • Click on the New Query button to open a new query window
   • Execute sp_configure ‘clr_enabled' , 1 in the query window (0 - disable, 1 - enable)
   • Then, Execute RECONFIGURE for changes to take effect

Writing SQLCLR

You can use either Visual Studio 2005 IDE or a text editor to write SQLCLR code modules. First we shall look at writing raw SQLCLR code modules using Notepad. Writing raw SQLCLR code modules involves the following 4 steps:

• Write the .NET code and compile - generates MSIL assembly
• Create an assembly object in the SQL Server database - this accesses the assembly (.dll) from the file system and installs it onto the database. Not only the assembly, but we can also include other components like source code, debug symbols, etc in order to improve management.
• Register the code - this makes code modules visible/available for client applications.
• Use assembly to access data

Creating a simple User-defined function:

Step 1:

1. using System;
2. using System.Data;
3. using System.Data.SqlTypes;
4. public class MyCLRFunc
5. {
6.     [Microsoft.SqlServer.Server.SqlFunction]
7.     public static SqlString MyCLRHelloFunction()
8.     {
9.         return new SqlString("Hello everybody, I'm from SQLCLR!");
10.     }
11. };

Type the above code in Notepad and save as a C# (.cs) file.

In the above code, the attribute [Microsoft.SqlServer.Server.SqlFunction] in line number 6 denotes that MyCLRHelloFunction is a user-defined function and that it needs to be run within the CLR. You can write as many user-defined functions as you wish within the class and mark them as [Microsoft.SqlServer.Server.SqlFunction] in order to make them run within SQLCLR. If you want to write stored procedures, aggregates, triggers you need to change the above attribute accordingly.

Next, you need to go to Visual Studio command prompt and compile above C# (.cs) code to an assembly using following statement:

csc /t:library [C# file name]

This creates the MSIL assembly (.dll) and you are ready to load it onto SQL Server.

Step 2:

In order to load the assembly that we generated, we need to create an assembly object with a meaningful name in the database and point it to the assembly (.dll) in the file system.

Switch to the database that you want to load the code module to, in SQL Server Management Studio; and execute the following in a query window:

USE AdventureWorks

GO

Create the assembly object and load the .dll using CREATE ASSEMBLY statement:

CREATE ASSEMBLY MyUDFCLR FROM ‘C:\SQLCLR\MyCLRFunc.dll'

GO

What if we delete the assembly (.dll) from the file system after loading it to the database? No problem, because once we have loaded the assembly onto the database using the above statement, the assembly is loaded and run from within the database. There will be no relationship with the actual file system assembly (.dll) once it is loaded onto the database.

Step 3:

Having loaded the assembly onto the database, you now need to make it visible/available to be called from client applications by registering it with the CREATE FUNCTION statement.

1. CREATE FUNCTION dbo. MyCLRHelloFunction()
2.     RETURNS nvarchar(50)
3.     EXTERNAL NAME MyUDFCLR.MyCLRFunc.MyCLRHelloFunction;
4. GO

In the above code we use CREATE FUNCTION since we have created a user-defined function in our code module. In line 1 we assign this user-defined function to the dbo schema and name it as MyCLRHelloFunction. This can be any valid SQL alias and not necessarily the same function name in the assembly. In line 2 we specify the SQL equivalent of the return value of assembly function. And in line 3 we have specified the actual link to the assembly in the file system in the sequence of assembly name, namespace/class name, method name.

Step 4:

Now we are ready to execute the code module we created.

SELECT dbo.MyCLRHelloFunction()

Above SELECT statement calls the MyCLRHelloFunction user-defined function created in the C# assembly and gives following result in the result pane:

Hello everybody, I'm from SQLCLR!

Verify SQLCLR

You can use the Object Explorer in SQL Server Management Studio to verify that the assembly is loaded in your SQL Server instance. Expand the Programmability node and then the Assemblies node to see whether your assembly is loaded in SQL Server. Depending on the type of the function we created, we can see that it is loaded under Functions node under the Programmability node. Since we had created a user-defined function which returns a scalar value, we can see that the function name is displayed under the Scalar-valued Functions node. Sometimes, we need to change the implementation of the user-defined function after deploying to SQL Server. In that case we need to re-compile the assembly and re-deploy to the SQL Server. Re-deploying involves, deleting the existing assembly object and recreating it.

Using Visual Studio 2005 to create SQLCLR

We can also use the Visual Studio 2005 IDE to create SQLCLR code modules and deploy them to SQL server with little effort. Importantly, we can step-through and debug the code modules after deploying to SQL server using the debugging features of Visual Studio's rich debugging options. We shall create the same user-defined function we had created earlier; this time using Visual Studio:

Step 1:

We need to create a Project using the following steps:

• Select Start → Programs → Microsoft Visual Studio 2005 → Microsoft Visual Studio 2005
• Now, choose File → New → New Project
• Expand the language option, Visual C# → Database → SQL Server Project
  

Depending on the history of your Visual Studio, it would ask whether you want to use an existing Database Reference or Add New Reference. If you are creating your first SQL Server Project, then you get a familiar window - New Database Reference, to create a new database reference.

You need to specify server, database, and authentication for the SQL server database that you are going to work with. If you have already created SQL Server Projects at least once before and have created database references, instead of the above dialog, you will be given a dialog where you can choose an existing database reference or create a new one.

This database reference is used when we deploy and test our code modules within Visual Studio. This will add all the required references to the project. Now you can start adding all 5 different code modules (user-defined functions, user-defined types, aggregates, stored procedures, triggers) to the project.

We shall add a user-defined function by following these steps:

• Open the Solution Explorer
• Right-Click the Project name
• Choose Add → User-Defined Function...
• Give a name to the user-defined function code module, and choose Add
  

The above will create a partial class called UserDefinedFunctions and add a user-defined function template with the [Microsoft.SqlServer.Server.SqlFunction] attribute. You can now implement this function and add any other supporting member functions as well.

Now we are ready to build the assembly by doing the following:

• Choose Build → Rebuild Solution

The above will compile the user-defined function code module, into an MISL assembly so that we can load it onto SQL Server.

Step 2:

Now we are in the phase of loading the assembly onto SQL Server, by following these steps:

• Choose Build → Deploy Solution

This will automatically create an assembly object on the SQL Server and deploy it to SQL Server. It also does the registration at the same time. The deployment location, i.e. server name and database name will be obtained from the database connection we had established at the beginning.

Step 3:

Now we can use the Visual Studio IDE's debugging feature to test the code module without switching to SQL Server Management Studio. When we create a SQL Server Project, Visual Studio gives us a test script called Test.sql. We can use this Test.sql file to test any aspect of the code module.

Follow the following steps to test the code module that we had deployed to SQL Server:

• From the Solution Explorer, expand the Test Scripts folder and open Test.sql
• Go to User-defined function section and uncomment the SELECT statement and replace the sample function name with the actual user-defined function you created.
• Move to the bottom of the Test.sql file and comment the last SELECT statement. This SELECT statement is for informational purpose only.
• Save the Test.sql file
• Choose Debug → Start Without Debugging
• Verify that it displays the output in the Output window in Visual Studio.

Conclusion

We have created a user-defined function in C# with the power of SQLCLR. Similarly we can create stored procedures, user-defined types, triggers and aggregates.We looked at the comparatively harder way of creating SQLCLR code modules using Notepad and the much easier and smarter way of doing it using the Visual Studio 2005 IDE.

Though SQLCLR gives us the ability to write any type of code module and host them within SQL Server, it is important to note that it introduces some sort of overhead to the execution engine. It is always better to find out whether you can do the same task using T-SQL before writing SQLCLR. If it is possible to do the task with T-SQL, it is always better that way rather than introducing the overhead by writing SQLCLR.

[RATE this ARTICLE, and COMMENT on it ]