CLR Function in SQL Server

Introduction

In SQL Server 2005 and later versions, database objects like functions, stored procedures, etc. can be created which are already created in the CLR. In some cases, the CLR is faster than T-SQL. CLR is mainly used to do those things which are not possible with T-SQL.

How to create CRL Function?

The steps to create a CLR function are as below.

Step 1 

Create Code in C# or VB.NET

In the following example, the Create function returns the total customer count present in the database.

using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

[SqlFunction(DataAccess = DataAccessKind.Read)]
public static int GetTotalCustomer()

{
    using (SqlConnection conn = new SqlConnection("context connection=true"))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(
            "SELECT COUNT(*) AS 'Customer Count' FROM CUSTOMER", conn);
        return (int)cmd.ExecuteScalar();
    }
}

Step 2

Generate a DLL for the preceding code and put it in a shared folder or put it on the SQL Server machine.

Step 3

We need to enable CLR in our SQL. With the help of the following command, we can enable it. Also, we can enable it from the GUI.

EXEC dbo.sp_configure 'clr enabled',1 RECONFIGURE

Step 4

Now we must register our CLR code DLL to SQL Server.

CREATE ASSEMBLY CLRFunctions FROM '\\DESJT\JT\CLRFunctions.dll';

Step 5

Create a User-Defined Function, as in.

CREATE FUNCTION CountTotalCustomer() RETURNS INT
AS EXTERNAL NAME CLRFunctions.CLRFunctions.GetTotalCustomer;

GO
SELECT dbo.CountTotalCustomer();

The following is another example of accepting a customer id and returning his/her name.

[SqlFunction(DataAccess = DataAccessKind.Read)]

public static string GetCustomerName(int id)
{ 
    using (SqlConnection conn = new SqlConnection("context connection=true")) 
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(

            "SELECT CustomerName FROM CUSTOMER where CustomerId = " + id, conn);
        return (string)cmd.ExecuteScalar();
    } 
}

CREATE FUNCTION dbo.GetCustomerName  
(   
 @id AS int  
)   
RETURNS NVARCHAR(255)  
AS EXTERNAL NAME CLRFunctions.CLRFunctions.GetCustomerName 

Note. Before dropping an assembly you must drop all references i.e. you must drop all CLR functions, SP, etc. which are created with a reference of the assembly being dropped.

Conclusion

A CLR function is useful where writing highly procedural code or using system facilities not accessible from T-SQL would be of benefit. The CLR integration layer does offer access to .Net libraries, which may be useful to get access to capabilities that T-SQL cannot support.