SQL Server CLR Scalar Functions

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


   Free MSSQLTips webinar - "Efficient Monitoring and Management of SQL Server" (click to register)

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.

Function Type Description
Scalar Returns a single value, like a string, date or number.
Table Valued Returns a table (rows and columns).
Aggregate Perform a calculation over a set of values and return a single value as its result.

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.

Create a new database project.

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.

Configure your new project.

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.

Adding a new item to our project.

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.

Adding a new SQL CLR C# User Defined Function.

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

New SQL CLR C# User Defined Function code template.

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.

Property Description Values
DataAccess This property indicates if the function can use the Context connection to read data from SQL Server. DataAccessKind.None: The function won’t access the data stored in the database. DataAccessKind.Read: The function will read data using the context connection.
IsDeterministic Indicates if the function is deterministic. A function is said to be deterministic if it produces the same result value, given the same input values and the same database state.
Only set this value to true if you are 100% sure that the function is deterministic. As a rule of thumb, if you are not sure, leave it as False.
True: The function is Deterministic. False: The function is not deterministic (Default value).
IsPrecise This is used to indicate that the function incurs on unprecise calculations like those made using floating point arithmetic. True: The function doesn’t make unprecise calcs. False: The function uses unprecise calculations (Default Value).
SystemDataAccess Indicates if the function will access information on the system catalog. DataAccessKind.Read: The function accesses the system catalog. DataAccessKind.None: The function doesn’t access to the system catalog (Default Value).
TableDefinition If the function is a Table Valued Function (i.e. it returns a table) this property contains the definition of columns in the output table. A string containing the output table definition.
FillRowMethodName The name of a method in the same class that fills a row in the table returned by the function. A string containing the name of the method.
Name Used by Visual Studio to reference the name that the function will use when registered in SQL Server. This is not required. A string containing the name of the function.

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.

Building the project.

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.

Creating the assembly and the functions in SQL Server.

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.

Testing the Fn_rowcount function.

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.

Testing the CompressText and UncompressText functions.
Next Steps



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



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

View all my tips


Article Last Updated: 2022-03-24

Comments For This Article





download














get free sql tips
agree to terms