SQL Server CLR Scalar Functions

By: Daniel Farina   |   Updated: 2022-03-24   |   Comments   |   Related: > Common Runtime Language

Problem

You work as a developer with SQL Server and Visual Studio and you were asked to create a set of database functions to validate data and to perform some calculations that may be complex using Transact-SQL alone. A co-worker suggested using the CLR integration feature of SQL Server to create the functions, but you are not sure how this is done. In this tutorial we will go step by step on how to create CLR scalar functions.

Solution

The SQL Server CLR integration is a feature that sometimes is overlooked by developers and software architects at the time they are starting a new project. Sadly, by not knowing in depth the tools they are using, the final application will pay the consequences. Most of the times this cost is paid with an increase of complexity in the design of the application, and as a rule of thumb, when we increase the complexity of a system, we are also increasing its fault points.

As an example, suppose that given a set of parameters we have to perform an action, such as a complex calculation, and return the result of that action as a value (that is the definition of a function). We have two choices, either we perform that action in the database or in the application’s code. The right choice will depend on various factors that you should consider. But if you choose to perform that action in the database you should know that there are three types of functions in SQL Server as can be seen in the next table.

We can create functions of any of those types using .NET code.

What are the Limitations of CLR Functions in SQL Server?

CLR functions share the same limitations as their T-SQL counterpart to modify data within the database. Although you can read data using SELECT statements by using the Context Connection in the .NET code.

Why you should use CLR Functions in SQL Server?

The first and most obvious benefit of using CLR functions is the advantage that .NET code provides us over T-SQL code for complex logic and calculations. Another benefit that is sometimes overlooked, is the fact that CLR functions like all CLR objects are already compiled in the database and therefore don’t require the recompilation that T-SQL functions have in order to build an efficient execution plan.

Making our First CLR Scalar Functions

In order to proceed you will need to obtain a copy of Visual Studio which you can download from here. After downloading and installing your copy of Visual Studio, open the application and create a new "SQL Server Database Project" as shown on the image below.

After pressing the Next button, the New Project Wizard will prompt you for a project name and a location to save it. I named the project CLR_ScalarFunctions, but you can use a name of your preference.

Finally, with the project being created we must go to the Solution Explorer window and right click on any part of it. You will see a context menu pop up on which you must select the "Add" menu entry. Then another context menu will emerge and in this one you have to click on "New Item…". A picture is worth a thousand words, so on the next screen capture you will see what I just stated graphically.

After pressing the "Add New Item…" entry of the context menu, you will see a new window open. This window allows you to choose amongst different kind of items to select the appropriate template that suits your needs. In this case we will select "SQL CLR C#" on the tree view at the leftmost side of the window; and then we must select "SQL CLR C# User Defined Function" and name it ScalarFunctions.cs. Take a look at the next screen capture to see it graphically.

Finally, we will see the ScalarFunctions.cs file created with some prebuilt code as can be seen on the following screen capture.

Analyzing the code, we can see that it creates a partial class named UserDefinedFunctions. Something that you may not be aware of is that making a partial class allows you to define the class in multiple source files. Additionally, we can see that there is a function named ScalarFunctions with no input parameters that returns an empty string, which should not be confused with a NULL string.

Above the function definition and enclosed with brackets is a reference to the Microsoft.SqlServer.Server.SqlFunction custom attribute class that we can use to set properties that affects how SQL Server can use the function. Just in case you are new to C# attributes, I suggest that you read the following chapter of the C# 6.0 specification.

The Code

We are going to build three CLR functions:

• fn_rowcount: This function receives the name of a table and returns the number of rows.
• CompressText: Generates a GZip VARBINARY stream of the text received as parameter.
• UncompressText: Returns the uncompressed text received as a VARBINARY stream.

In order to make things easier for you I will put the entire code in the code section below, so you can copy and paste it to replace the whole content of the file created by the Visual Studio template. Further on, I will make the comments and explain by referencing parts of the code.

Something I want to emphasize is that since the purpose of the code in this tip is to be easy as can be, I am not getting into validations, exceptions and SQL injections.

using System.IO.Compression;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Text;
 
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(Name = "Fn_rowcount", DataAccess = DataAccessKind.Read , IsDeterministic=true)]
    public static SqlInt32 Fn_rowcount(SqlString TableName)
    {
        SqlDataReader reader;
        SqlCommand command;
        string str;
        SqlInt32 rowcount = 0;
        str = "SELECT COUNT(0) AS [COUNT] FROM " + (string)TableName;
        using (SqlConnection sqlCnn = new SqlConnection("context connection=true"))
        {
            sqlCnn.Open();
            command = new SqlCommand(str, sqlCnn);
            reader = command.ExecuteReader();
 
            while (reader.Read())
            {
                rowcount = reader.GetInt32(0);
            }
 
        }
 
        return rowcount;
    }
 
    [Microsoft.SqlServer.Server.SqlFunction(Name = "CompressText", DataAccess = DataAccessKind.None, IsDeterministic=true)]
    public static SqlBinary CompressText(SqlString sqlString)
    {
        MemoryStream byteStream = new MemoryStream();
        MemoryStream textStream = new MemoryStream(Encoding.ASCII.GetBytes((string)sqlString));
        GZipStream compressor = new GZipStream(byteStream, CompressionMode.Compress);
        textStream.CopyTo(compressor);
        compressor.Flush();
        compressor.Close();
        textStream.Close();
        compressor.Dispose();
        textStream.Dispose();
 
        return byteStream.ToArray();
    }
 
    [Microsoft.SqlServer.Server.SqlFunction(Name = "UncompressText",DataAccess = DataAccessKind.None, IsDeterministic=true)]
    public static SqlString UncompressText( SqlBinary sqlBinary)
    {
        MemoryStream byteStream = new MemoryStream( (byte[]) sqlBinary);
        MemoryStream textStream = new MemoryStream();
        GZipStream compressor = new GZipStream(byteStream, CompressionMode.Decompress);
        compressor.CopyTo(textStream);
        compressor.Flush();
        compressor.Close();
        byteStream.Close();
        compressor.Dispose();
        byteStream.Dispose();
 
        return Encoding.ASCII.GetString( textStream.ToArray());
    }
}

Right at the beginning of the code you will notice that I included the following using directives to import namespaces not related to SQL Server:

using System.IO.Compression;
using System.IO;
using System.Text;

The System.IO.Compression namespace is used to access the class GZipStream; System.IO allows us to use the MemoryStream class and the System.Text is used to convert the strings to byte arrays with the aid of the Encoding class.

Building and Deploying

Now let’s build this project by going to the Build menu and select Rebuild Solution as can be seen on the next screen capture.

After building the solution you will have the DLL file in the ".\bin\Debug" folder of your solution. With this DLL we can create our assembly named CLR_ScalarFunctions with the following Transact SQL code. Remember to change the path to point to your DLL file.

CREATE ASSEMBLY [CLR_ScalarFunctions]
AUTHORIZATION [dbo]
FROM
'C:\Users\Daniel\source\repos\CLR_ScalarFunctions\bin\Debug\CLR_ScalarFunctions.dll' WITH PERMISSION_SET=SAFE
GO

Then we create the function definitions in SQL Server as follows:

CREATE FUNCTION dbo.Fn_rowcount( @TableName AS NVARCHAR(255)) 
RETURNS INT 
AS EXTERNAL NAME [CLR_ScalarFunctions].UserDefinedFunctions.Fn_rowcount
GO
 
CREATE FUNCTION dbo.CompressText( @text AS NVARCHAR(MAX)) 
RETURNS VARBINARY(MAX) 
AS EXTERNAL NAME [CLR_ScalarFunctions].UserDefinedFunctions.CompressText
GO 
 
CREATE FUNCTION dbo.UncompressText( @gzip AS VARBINARY(MAX)) 
RETURNS NVARCHAR(MAX) 
AS EXTERNAL NAME [CLR_ScalarFunctions].UserDefinedFunctions.UncompressText
GO

On the next image you can see the output with the execution of the previous statements.

The Fn_rowcount Function

This function uses the context connection to execute a query against SQL Server. The query in question is a SELECT COUNT() statement from the table received in the TableName parameter of type SqlString. The function returns an integer (SqlInt32) with the number of rows.

If you look at the function attributes definition for this function, you will see that DataAccess is set to DataAccessKind.Read, because we are going to read data from the database, and IsDeterministic is set to true because this function will return the same value if its called with the same parameter (the table name) meanwhile the database is in the same state (in this case, if there are not inserted or deleted rows).

In order to test this code, I will create a dummy table by executing a SELECT INTO on the sysobjects table. If you remember, we can’t call this function on sysobjects table because we didn’t set the SystemDataAccess function attribute.

SELECT * INTO Test FROM sysobjects
GO
 
SELECT dbo.fn_rowcount('Test') AS 'This is the row count';
GO

On the next image you can see the execution of the code above.

The CompressText and UncompressText Functions

These functions share the same function attributes for DataAccess, which is set to DataAccessKind.None (because we are not going to read data from SQL Server other than the received as a parameter) and for IsDeterministic that is set to true.

For compressing and uncompressing we create an object of the GZipStream class with the following constructor.

public GZipStream(Stream stream, CompressionMode mode);

We can see in the above code that this particular GZipStream constructor function receives a first parameter of type stream that contains the data that will be compressed or uncompressed according to what is specified in the second parameter of type CompressionMode. This type is an enumerator with the values Decompress = 0 and Compress = 1.

Now it’s time to test these functions by executing the following code.

SELECT dbo.CompressText('MSSQLTips.com');
 
SELECT dbo.UncompressText(0x1F8B0800000000000400F30D0E0EF409C92C28D64BCECF0500C63D14900D000000)
 
SELECT dbo.UncompressText(dbo.CompressText('MSSQLTips.com'));

In the code above, we call the CompressText function with the text 'MSSQLTips.com'. Then in the second line we execute the UncompressText function with a VARBINARY parameter that is the compressed stream of the 'MSSQLTips.com' text. Finally, we chain both functions.

On the next screen capture you will be able to see the output of the previous code.

Next Steps

• If you want to build your own functions then you must check out this Microsoft Documentation page with the mapping of SQL Server to CLR data types: Mapping CLR Parameter Data - Microsoft.
• In order to test this code, you have to enable SQL Server CLR integration. In this tip you will find out how to do it: How to Enable SQL Server CLR Integration using T-SQL.
• Take a look at this SQL Server CLR Introduction tip if you are just starting with SQL Server CLR integration.
• Another interesting use of CLR in SQL Server is the creation of User Defined Types. If you want to know more about this you can read my previous tip SQL Server CLR User Defined Types Example.
• If you need an additional example of a CLR scalar function you can take a look at this tip Remove HTML tags from strings using the SQL Server CLR.

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips