Hadi Fadlallah
Working with JIT Compiler

Getting started with SQL Server CLR functions

January 11, 2022 by

This article will explain how to create a SQL Server CLR function stored in a C# class library without creating a SQL Server database project. We will first briefly explain the concept of .NET common language runtime (CLR) and then build a C# class library using the .NET framework. Then, we will use the C# library to create user-defined CLR functions in SQL Server.

Introduction

The Common Language Runtime (CLR) is the heart of the Microsoft .NET Framework. It provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code.

Several services are provided by the common language to execute programs, such as the just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

The execution of the programs developed using .NET is managed by the Just-In-Time compiler (JIT) regardless of the .NET programming language it was created with. A language-specific compiler converts the source code to a common intermediate language (CIL). This language is then converted into machine code by the Just-In-Time (JIT) compiler. This machine code is specific to the computer environment that the JIT compiler runs on.

Working with JIT Compiler

Figure – Working with JIT Compiler (Image source: GeeksForGeeks)

Using SQL Server CLR, we can define several objects such as stored procedures, user-defined functions, triggers, user-defined types, and user-defined aggregates in managed code.

One of the benefits of SQL Server CLR is that it can achieve significant performance increases in some since managed code compiles to native code before execution. Besides, several complex operations are implemented in .NET easier than SQL Server.

The following section explains how to create a basic C# library using the .NET framework.

Side Note: .NET core and .NET standards cannot be used in CLR integration.

Creating a .NET Class library

Our first step is to create a .NET Class library that contains the C# functions we need to use in SQL Server. To do so, open Microsoft Visual Studio and create a new project:

Creating a new project

Figure – Creating a new project

To create a SQL Server CLR class library, we should use a .NET framework class library project.

Selecting .Net framework class library project

Figure – Selecting .Net framework class library project

In this article, we will use the .NET framework 4.6 to build our class library, and we will set “SQLExternalFunctions” as a project name.

Configuring the project name and framework version

Figure – Configuring the project name and framework version

Once the Visual Studio IDE main windows are shown, go to the solution explorer tab to the right and change the name of “Class1.cs” to “SQLExternalFunctions.cs”.

Renaming the main class

Figure – Renaming the main class

Now, we need to write the functions we need to use in SQL Server. First, let us remove the unnecessary parts of the class. Double-click on SQLExternalFunctions.cs class.

In the C# class editor, remove the SQLExternalFunctions namespace curly brackets and the unnecessary imported libraries (only keep the System and System.Linq) as shown in the image below:

Removing unnecessary code

Figure – Removing unnecessary code

We need to add the static keyword before the class definition since the SQL Server CLR functions must be defined within a static class.

After removing unnecessary parts, we are going to add three functions as follows:

First character occurrences count:

  • Input: a string value
  • Output: an integer value = the number of occurrences of the first character in the input string.
  • Code:

Character occurrences count:

  • Input:
    • A string value
    • A character
  • Output: an integer value = the number of occurrences of the input character within the input string
  • Code:

Get current date in ISO format:

  • Input: this function does need any input
  • Output: a string value of the current date in the ISO format (yyyy-MM-dd HH:mm:ss)
  • Code:

After writing the function, we should add some metadata to our class to let the SQL Server know it contains CLR functions. This metadata is what we call in C# “attributes”. If interested, you can learn more about C# attributes in the following official documentation: Attributes (C#) | Microsoft Docs.

Before each function definition, we need to add the following attribute, as shown in the image below:

Adding SqlFunction attribute before the C# function

Figure – Adding SqlFunction attribute before the C# function

As mentioned in the Visual Studio tooltip, the SQLFunction attribute “is used to mark a method definition of as user-defined aggregate as a function in SQL Server. The properties on the attribute reflect the physical characteristics used when the type is registered with SQL Server.”

SqlFunciton attribute description

Figure – SqlFunciton attribute description

Once done, we should build our class library. To do so, right-click on the project in the solution explorer and press “Build solution”.

Building the class library used to defined SQL Server CLR functions

Figure – Build solution

After building the solution, we should copy the created DLL file from the project directory (SQLExternalFunctions\bin\Debug\) to the location where we want to store it (in this article, we will keep it within (D:\CLR Functions\).

Opening the project directory

Figure – Opening the project directory

Opening the project directory

Figure – Class library location

Creating a SQL Server CLR user-defined function

After creating the .NET class library, the next step is to create a SQL Server CLR user-defined function that calls the functions from the .NET class library.

First, open the SQL Server Management Studio and open a new query editor.

Open a new query editor

Figure – Open a new query editor

SQL Server Configuration

Before defining the SQL Server CLR function, we should configure the Server to allow CLR integration. First, we need to enable the advanced options configuration using the following command:

Next, we should enable the CLR integration option using the following command:

Since this article does not focus on CLR security, we will disable the strict CLR integration security rules already set by SQL Server using the following command:

Warning: Turning the CLR strict security is not recommended at all, making the SQL Server vulnerable. You can read more about signing the CLR assemblies and the impact of the CLR strict security in the following article: Impact of CLR Strict Security configuration setting in SQL Server 2017.

Creating an assembly object

To map a CLR class library or application, we should create an assembly object within the SQL Server and map it to the class library file path as follows:

Creating the user-defined functions

For each .NET function, we defined in the C# class library, we should define a related SQL Server CLR function where we must specify the same input parameters and output data type. Besides, we should mention the original function name as follows:

In this article, we should define the following functions:

Using the functions

Now, let us try the following queries to test our created functions:

The image below shows the results of the query execution.

Testing the created SQL Server CLR functions

Figure – Query result

Summary

In this article, we briefly explained the .NET common language runtime (CLR) and how to create user-defined SQL Server CLR functions based on a C# class library developed with a .NET framework.

Another option to create and deploy SQL Server CLR functions is by defining them within the SQL Server database project, where they will be created during the database deployment operation.

Hadi Fadlallah
Functions, SQL commands, T-SQL

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional. On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques. Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website. View all posts by Hadi Fadlallah

6,475 Views