SQL CLR Integration

It’s not uncommon to run into scenarios where you would need to call .NET code from SQL Server. This is where SQL CLR integration comes into play.

Common language runtime (CLR) provides the execution environment of .NET code. Since SQL Server 2005, SQL Server features the integration of the common language runtime (CLR). This means that stored procedures and user-defined functions can now be written using any .NET language code. This code gets converted into native code by CLR, and it helps achieve a significant performance increase. Also, the code can now leverage all the functions and services provided by CLR.

SQL Server CLR integration simply means calling .NET code from SQL Server.

Benefits of Integration:

• A better programming model.

• Improved safety and security.

• Streamlined development through a standardized environment.

• Improved performance and scalability.

Creating a CLR function in SQL Server involves the following steps:

The first thing to remember here is that CLR integration is disabled by default. You need to enable it manually. Below is the code you can use to enable CLR integration.

I will demonstrate this concept using code to add two numbers. You definitely do not need SQL CLR code to do this but for simplicity’s sake I’ve chosen this example. The three steps here are:

1. CLR method: Define a static method of a class in a language supported by the .NET Framework. Then compile the class to build an assembly in the .NET Framework by using the appropriate language compiler.

–  This is the place where the actual business logic is housed. You would create a method just like any other regular .NET method.

–  This method needs to be annotated with an attribute that tells it to mark the method definition in an assembly. Here I have used [SqlFunction] as the attribute that marks the method as a function.

–  Build the project and have the assembly ready for the next step.

2. Register the assembly in SQL Server by using the following CREATE ASSEMBLY statement.

–  This statement creates the assembly in the server.

–  When deploying SQL CLR stored procedure, you can enable it for “Safe”, “External_Access”, “UnSafe”. For this example, we will leave it at “SAFE”.

Once you execute the above statement, you should be able to go into SSMS and verify that the assembly has been created.

–

Transform your business operations with our Microsoft solutions

Explore Our Microsoft Consulting Services  →

–

3. CLR function: Create a function that references the registered assembly by using the CREATE function statement.

–  The way you tell the engine you want a CLR method to be used as a SQL procedure or function is by using the CREATE FUNCTION or CREATE PROCEDURE SQL commands.

–  Note the method signature must match the CLR method.  The AS EXTERNAL tells the SQL engine to go look for this fully named method signature.  Since the assembly has already been loaded, it should be able to find it.

Once the function is created, test it by writing a simple query. I have written a simple query calling the function and passed in two random numbers to test it, and, to our surprise, it actually works.

So that’s how you can integrate SQL and CLR together to achieve what you want. I can totally see us using this for several different scenarios, but if you think you could use it too, please feel free to leave a comment telling us what the scenario would look like.

If you have questions or comments about this blog post, or other aspects of Microsoft SQL CLR integration, please leave a comment below, tweet us at @CrederaMSFT, or contact us online.

• SQL CLR Integration