CLR Function in SQL Server 2005

In SQL Server 2005 and later version of it, database objects like function, store Procedure, etc can be created which are already created in CLR.

Introduction

In SQL Server 2005 and later versions, database objects like function, stored procedure, etc. can be created which are already created in the CLR. In some of the 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 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 acceptiing 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.