A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still read this article from here
Creating a DLL to be hosted in SQL Server let you run code directly in the database. You may think of it as a Stored Procedure written in VB or C#. it might be helpful to prevent doing large processing on the client after having transited a lot of data on the network wire.
Lately, I was reviewing training material on that topic to see if it was still accurate and found out that many things have changed since then. We are now using Visual Studio 2013 (or 2015 in CTP) and SQL Server 2014.
This month code is available in both VB and C#. The solution has been created using Visual Studio 2105 CTP 5 but would also work the same way if you are using VS 2013.
You will also need Microsoft SQL Server. I have used SQL Server 2014 but should also work with SQL Server 2012.
You will also need to install Microsoft SQL Server Data Tools (aka SSDT) which are tools added to Visual Studio to provide a better interaction between the 2 products. If you don’t already have it, you can get it from https://msdn.microsoft.com/en-us/data/hh297027
. SSDT is also installable right from within Visual Studio. The code can be downloaded from here
We will start by creating a very simple method that adds 2 digits together just to get our feet wet with the process and we will build up from there on a real re-usable CLR procedures.
So start your Visual Studio 2013 or 2015 (with SSDT installed) and create a new project. But not just any kind of project, a “SQL Server Database Project” that you will find under the SQL Server templates folder. As opposed to any other projects you created, there is no template letting you choose VB or C#. At this point, you are just creating a database project.
Figure 1: Creating a database project
Before going further with this project, we need set some of the project properties. So open the project’s properties and go to the project Settings tab. The most important setting on that tab is the target platform. Ensure you are targeting the version of SQL Server you will be running your code from. You have a wide range of version from 2005 to 2014 and even Azure.
Figure 2: Ensuring the targeted platform
Now that we have the database version set properly, you can open the SQLCLR tab. There are 2 important settings here. The first one is the Target framework which you need to set to the version installed on your server. Any version from the .Net framework 2.0 up to the most current one are available.
The second important property that you can set here is the language. By default, a new project will be created to use C# but you can change it to Visual Basic here. I strongly suggest that you change it right now if it is your intent to use VB.
Figure 3: Setting the Language
We have now customized the most important properties and are ready to add our little function.
Lets now create a new function. Right-click the project, select “Add” and then “New item…”.
Figure 4: Adding a new item
From the “Add New Item” dialog, be sure to select “SQL CLR [VB|C#]” (depending on the language you selected in the properties) from the left pane. Then select the type of object you want to create. For my demo, I have selected a User Defined Function. This is also the place where you should set the name of your new class.
Figure 5: Adding a new UDF to the project
After you clicked the Add button, a new class is added to your project with a sample function returning a string. I have replaced this sample method with this code:
Public Shared Function EM_Add(p1 As SqlInt32, p2 As SqlInt32) As SqlInt32
Return p1.Value + p2.Value
You will observe 2 things. The first thing is the attribute indicating that this method is special, it is a SqlFunction. The second thing is the data type used. We are using SQL types instead of .Net native types.
In my demo project, I have also created in the same class, a second method using the same template called EM_Sub in which I have changed operation from a + to a -.
Deploying it to the database
Now build your project to ensure your don’t have issues. When you will have your project building, right-click your project and select Publish.
Figure 6: Publishing the project
This dialog will show up but the target database connection will be empty. You can type the connection string that fit your environment or you can click the Edit button to have another dialog letting you enter the values that generates the connection string. You can click the Publish button to have your new database created (named DemoSqlClr as per the publish dialog) and your project gets deployed.
If everything went well, you will get the results shown in figure 7.
Figure 7: Project deployed
At this point, your database has been created and your new functions are ready to be used. If you now open SQL Server Management Studio (SSMS), you will find your new database.
Figure 8: Your new database from SSMS
If you start drilling into it and open the Programmability folder, you will find a name you should be familiar with (DemoSqlClr in my case) and your new functions under the Scalar-value Functions folder.
You might be tempted to right-click one of your function and select Modify. You can do it but don’t expect to see VB (or C#) code in there. Instead, you will find this code:
ALTER FUNCTION [dbo].[EM_Add](@p1 [int], @p2 [int])
RETURNS [int] WITH EXECUTE AS CALLER
EXTERNAL NAME [DemoSqlClr].[DemoSqlClr.UserDefinedFunctions].[EM_Add]
What does this code means? This code acts like a proxy (or a pointer) to another method using that signature found in the name following the EXTERNAL NAME. That means that whenever you will call this SQL Scalar-value function, the external .Net code will gets called.
To try it, from a query window in SSMS, you can enter a queries like these:
If it is the first time ever you are trying to run CLR code on your server, you might get an error.
Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.
This is because CLR is off by default on SQL Server. To enable it, just run these statements from SSMS:
EXEC sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
Debugging the method
You might now want to debug your .Net code. I doubt this simple method has an error but if your method is not as trivial as mine, chances are that a debugging session would be helpful. We cannot just run into code like this, we need to create a SQL query that will call our new function.
Go back to your Visual Studio project, right-click on your project, click Add and New Item … (as shown in figure 4). This time, select the User Scripts tab and select “Script (Not in build)” item.
Figure 9: Adding test script
That will generate a new empty .sql file. You can enter queries in that file just like we did in SSMS. For example:
You might get used to hit F5 at this point but don’t. Hitting F5 will just deploy your project to the server, it won’t run the query. Instead, you need to hit CTRL-SHIFT-E or hit the green arrow from the top left of you query editor.
Figure 10: Executing a query from Visual Studio
After it has been executed correctly, results will show up just underneath. This is not really what I call debugging! Just to the right of green arrow, there is a small arrow meaning that this command is in fact a drop down of commands:
Figure 11: More commands available
If you pick “Execute With Debugger”, you should be entered to the real debugging mode you are used to from which you can hit F11 until you get in your .Net code. Notice that most .Net debugging are fully working (Tooltips, Call stack, Locals, Watch Windows, …). Some features like setting the next line to be executed are not working. Edit-and-continue is also not working.
Adding new functions
After you have created your first version of your assemble, you will probably find out more features to put into it. This is quite simple. Just continue adding methods to your class (or create a new class in the same project) and re-publish it just like we did before (see figure 6).
Deploying to the production SQL Server
Chances are that you cannot connect to your SQL Server using Visual Studio just like we did here. Your DBA might not really to copy DLLs to the server.
What we’ve done here on a test server can all be scripted. You first need to create a script of the assembly itself as shown in figure 12.
Figure 12: Scripting the assembly
You also need to create scripts for each functions you created.
You finally need to append all the scripts (assembly and each function) to a big script that you add to your deployment scripts just like any database update scripts. Remember that you might have to enable the CLR on the production server as well.
Replacing existing SQL features
I have always been one of those saying that, if the feature exists natively in SQL, you should never rewrite it. Lately, I was proven wrong on a specific feature. It is probably due to the fact that XML is used and this usage of XML is very not optimized. I would never rewrite simple T-SQL features but I might do some performance tests when dealing with more complex features.
Microsoft documented some advantages of using SQL-CLR integration here
Concatenating strings by group
This is something we often have to do. We want to concatenate in a single string all the values for a group. Using the STUFF T-SQL function and a bit of XML, you can achieve that. I even blogged about it here
Somebody pointed me out to a CLR method somebody created and publicly available on CodePlex
. I first argue that the STUFF T-SQL feature was surely faster be I was told that it was surprisingly fast. I was not convinced! I created a test database with a table of 1,000,000 rows and created to queries, one with STUFF and another one with the CLR function. I was surprised to find out that the CLR was running quite faster (3 seconds compared to 9 seconds for the STUFF method).
So I have first apologize. Now I will setup a test database to compare both methods.
Over the years things are changing a bit but good stuff stick around. This article is an example. Using older versions of software, you were able to do it but this feature is now still working a bit differently.
I really think it was worth revisiting this article 5 years later because being able to run .Net code in a database is still a very valid solution especially that it can give you a performance boost in some scenario!