One can write a function or procedure in SQL server to perform a task. However, writing those in SQL has very limited scope as the SQL deals with restricted number of in-built functions, targeting mainly towards the financial area. In Contrast, the dot.net framework has big scope and one can write nearly any kind of function and/or procedure. We claim a function as “SQL-CLR” function when we write it using dotnet framework and deploy it to run on SQL Server database engine. In this article, we are going to see how to write a SQLCLR function.
2. Loading CLR binary to Database
First, database developers write the functions in Dotnet framework and then expose that through a dll. The SQL server, then, loads the dll into database engine and starts accessing functions exposed by it. In native SQL language, one can write functions, stored procedures, and triggers. In addition to those, one can write Aggregate Function and can even delineate own “User defined Types” in dotnet framework that will later used by the SQL Engine.
Using the “Surface Area Configuration” tool, one can demand SQL server to allow the injection of Dotnet Framework code in the SQL Server Database engine. This tool ships with the SQL Server installation and the below given steps show enabling the SQL-CLR integration:
Watch Video 1 (or) Follow steps given below it:
Video 1: Surface Area Configuration – Turn-on SQLCLR
1) First, invoke the “Surface Area Configuration” tool from the windows start menu. This is shown below:
|Fig 1. Surface-Area Configuration from Start Menu|
2) From the displayed window, Select “Surface Area Configuration for Features” link as shown in the below screen shot:
|Fig 2. Surface Area Configuration|
3) In the Surface Area Configuration dialog box, select the node “CLR integration”, which comes under the Database Engine group. Now, from the right pane turn-on the checkbox, which says, “Enable CLR Integration”. After that, click the apply button and close the dialog by hitting OK.
|Fig 3. Enabling CLR Integration|
Now our SQL server is ready to load DLLs written in Dotnet Framework. Let, we explore writing a dotnet c# function and deploying that in SQL Server.
3. SQLCLR versus SQL datatypes
When we write functions in Dotnet framework and try to use that like SQL functions in the SQL server environment, the first trouble gets along in the form of datatypes. Because, the datatypes space allotment may be different in these two different domains. Therefore, to overwhelm this problem, Microsoft put in special CLR datatypes to communicate with the SQL Server database engine. The below table shows some of the important “SQL-CLR-DataTypes”:
CLR Object Types
char array or string
Table 1: Some Frequently used SQLCLR Data Types
Note that null value in SQL Server cannot match with any primitive datatype of core dotnet. The matching CLR object for that is “DBNull”. The above table shows some crucial data types rather than listing them all. Refer MSDN for knowing the variety of other datatypes.
4. Creating SQLCLR Function
In this section, we will create two functions. The first function does not accept any parameter and returns a string to the caller. The second function takes an integer value, computes square of it, and then returns that value to the caller. Here, the caller is SQL Server engine and the function, which we are going to write using “Dotnet Frame Work”. Let us start now.
4.1 Creating a Project for SQLCLR
To create project, open “Microsoft Visual Studio 2005” from start menu. Then open the “New Project” dialog using the “FileèNewèProject” menu. Select database “project type” from the tree view by navigating to “Visual C#èDatabase” and from the displayed right pane pick “SQL Server Project”. Provide the project name as “SQLCLR_Func” and then click ok. The screen shot below shows the steps involved:
|Fig 4. Creating SQLCLR Project in VC#|
4.2 Adding the function
After opening the project, Right click on the project name to invoke the context menu and select “AddèUser-Defined function” as shown in the screen shot below. This will open an “Add New Item” dialog box displaying the all the installed templates. From the template list, select “User-Defined Function”, provide the C #filename (Say testfunction.cs) in the name textbox at the bottom, and then click OK.
|Fig 5. Adding a Default User-Defined Function|
Now we are in a code window and we have some default code, which looks like below one. We can see that the “Add New Item” dialog already placed adequate “using statements” required for writing the function (Marked as 1). Similarly, the IDE takes the function name from given file name (Marked as 2). We are going to change this default function soon. Also, note the attribute “SqlFunction” added to the function name and this tells us that the SQL Server engine will invoke this Dotnet CLR function.
|Fig 6. The Default .cs File Content|
Now have a look at the couple of examples written by deleting the existing function SQLCLR_Func. The below screen-shot shows the example:
|Fig 7. C# SQL CLR Function Example - Refer Code Listing 1|
We already saw the details of item marked as one and two in the earlier sections of this article. You can notice that we changed the function name as TestFunction and changed the return string value (Marked as three). Next, we wrote a new function called “SquareOf”, which accepts an integer as parameter (Marked as 4), squares that parameter, and returns the computed value (Marked as 5).
You can also note the usage of the CLR datatypes such as SqlInt32 and SqlString. Refer MSDN for a complete list SQL Server CLR datatypes. With these two new functions, we can now create and deploy the dll into the SQL Server engine.
4.3 Deploying CLR Function in SQL Engine
Once the function is ready, we can deploy that function using the menu option “BuildèDeploy <name>”. The deploy menu once clicked, deploys the two functions (In our case) in SQL Server. The picture below shows the menu option required for the deployment of CLR functions.
|Fig 8. Deploy C# CLR Function to SQL Server|
We should make sure that visual studio is launched as administrator. Because, deployment of CLR function in SQL Server requires administration privilege.
4.4 Testing the Function
OK, we wrote couple of functions in C# and deployed those functions in SQL Server. Let, we see how we can check these functions working as expected. Have a look at the below picture. The item marked as two shows that the IDE created test.sql file. We can use this file to write our SQL Scripts and execute those written scripts in the Visual Studio Development Environment itself. Remember, the function we wrote is inside the TestFunctions.cs file, which is marked as one in the below picture. In the Test.sql we can write SQL Script to call the CLR functions and the call made in such a way is marked as three in the below picture. Writing SQL scripts in visual studio itself is helpful in case if we want to debug the scripts and you can see how the breakpoint is placed in the very first statement (Marked as 4). When you execute these functions in visual studio, you can see the results in the output window.
|Fig 9. Testing the SQLCLR Function in Visual Studio|
The screenshot below shows the output of the function in Visual Studio Debugging output window:
|Fig 10. Test Result in the Output Window|
Video 2: Deploying the Function
5. Usage of T-SQL Script for deployment
In the previous section, we deployed our SQL CLR function using visual studio IDE. Sometime, it may require deploying the application through SQL scripts. Because, the setup software have more control over these TSQL-Scripts. Have a look at the below scripts:
|Fig 11. Configure SQL CLR and Create Assembly using T-SQL - Refer Code Listing 2|
The script enables the CLR inclusion (Marked as 2) in the SQL Server engine and the same was done using the “surface area configuration tool” as shown in section 2 of this article. To set the flag ”clr_enabled” we set the “Show Advanced Flag” to one (Marked as two). When this flag is zero, SQL Server engine will not allow changes to the clr_enbaled. After setting the flag, we included the Dotnet DLL, which exposes our two new functions to SQL server engine. Moreover, this was done by making use of the “Create Assembly” TSQL statement as shown in the above script. It looks for the Assembly Name (Marked as 3) and the path to the dotnet dll (Marked as 4). Once we execute the above statements, we have successfully set the dotnet assembly.
Now, have a look at the script below:
|Fig 12. Registering DotNet function through T-SQL - Refer Code Listing 3|
Here, we registered the Dotnet Assembly functions as SQL Server functions. If you have a close look at it, we are actually creating a SQL Server function by linking it with the functions created in Dotnet C# (In our case) language. Here, we are creating two functions named CLRTestFunc (marked as 1) and ClrSquare (Not markedJ). In the above script marked as 2 and 3 shows the return type of the SQL Function. This return type should match with the return type of the return type of the C-Sharp version of the function. In the second function, we can see how the SQL sends the parameter to C# function. Here also the Datatype should match with c#. Note that we used smallint (SqlServer side) to match with Int16 of C# function.
Code snippet marked as 4, 5, 6 and 7 links the Dotnet CLR function with the SQL function that we are about the create. Here, the first one denotes the Assembly Name of the SQL (Note: Assembly name given in Create Assembly Statement), the second part denotes (4 and 5) the C# class name that exposes the function and final one denotes the name of the function (6 and 7) in c# side. Now, when we make a call to the function, say ClrSquare, SQL Server know that the function body and business logic defined in the external assembly and it know which function to call from that assembly as well.
When you want to remove the assembly from SQL Server, first remove all the functions registered through it. In our case, to remove the assembly MyCLRFunctions, we should first remove the SQL Functions CLRTestFunc and ClrSquare, which refers the assembly.
6. Code Listings
//Sample 01: Functions return string modified
public static SqlString TestFunction()
// Put your code here
return new SqlString("Test Function invoked");
//Sample 02: New function added
public static SqlInt32 SquareOf(SqlInt16 number)
return new SqlInt32(number.Value * number.Value);
sp_configure 'show advanced options', 1;
sp_configure 'clr_enabled', 1
Create Assembly MyCLRFunctions From
Create Function CLRTestFunc()
returns nvarchar(50) external name
Create Function ClrSquare(@param smallint )
returns int external name
Source Code: Download