SQL Server CLR Table Valued Function

By: Daniel Farina   |   Updated: 2022-05-12   |   Comments (1)   |   Related: > Common Runtime Language

Problem

You are working as a software architect / developer in a project that involves Microsoft technologies. As a consequence, you have to use SQL Server as the database engine, ASP.NET for the web layer, C# as the programming language and of course Visual Studio as the coding platform.

To solve a specific situation, you need to show some results in a table fashion. But instead of having a static table, the need varies according to some input parameters. After some research you find out that a Table Valued function fits your needs. The only problem is that the algorithm needed to create the output table is way too complex to be coded in Transact SQL. Fortunately, SQL Server with its integration with .NET CLR brings you the possibility to use C# and any other CLR supported language to create the algorithm for the table valued function you need. In this tip I will show you the very basics on how to create your first CLR table valued function.

Solution

There are a few things you have to take into consideration when you decide to code a Table Valued Function. Assuming you have some knowledge of C# and at some degree maybe a little bit of experience coding CLR Scalar functions, thinking about a function that returns one value is not something that is hard to imagine. I mean, the standard definition of what functions are is that it is a piece of code that does something and returns a value is based on the assumptions of a scalar function.

Of course, we can extend that definition to "a piece of code that returns an object", and in such case we can suppose that making a table valued function is like making a scalar function but returning an object of some kind of table type, like a DataTable object. That would be very easy to code.

Well, that is not exactly how it works. From a performance point of view, it's better that each row be sent to the client when its ready than waiting to get all the rows. In other words, the information is returned to the client in an asynchronous fashion.

That's why making a CLR table valued function is in fact, making two CLR methods. Yes, we need a main method that is the table valued function per se, and another method that will "fill the rows" to be sent to the client.

The main method of the table valued function must return a collection object that implements the IEnumerable or IEnumerator interfaces. If you are new to the concepts of Object-Oriented Programming, this may sound weird to you, but don't worry we will see an example soon.

The other method that we will refer as the "Fill Row method" must receive an object and one parameter for each output column of the table valued function. These parameters are passed by reference to this function, meaning that we have to set the value for each of the parameters in order to send the data to the client. In layman's terms, this function gets the object returned by the first function and sets the value for each column to be sent to the client.

Making Your First CLR Table-Valued Function

To follow along you will need Visual Studio which you can download from here. After installing, open the application and create a new "SQL Server Database Project" as shown below.

Click Next to open the New Project Wizard. I named my project CLR_TVF and click Create.

In the VS Solution Explorer, right click anywhere and in the pop up menu select "Add". Another menu will open and select "New Item…" as shown below.

In the new window that opens, select "SQL CLR C#" and then "SQL CLR C# User Defined Function" and name it HelloWorldTVF.cs.

Finally, we will see the HelloWorldTVF.cs file created with some prebuilt code as shown below.

If you take a look at the code template you will clearly identify that it contains one method named HelloWorldTVF that returns an empty string. We will need to change many things in the function definition.

This method is inside the partial class 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.

Above the function definition and enclosed within brackets is a reference to 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 you read the following: https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/language-specification/attributes.

Hello World Function Code

Here is the code for our first CLR table valued function.

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
 
public partial class UserDefinedFunctions
{
    private static Int32 identity = 1;
    [SqlFunction(FillRowMethodName = "FillRowHelloWorldTVF", TableDefinition = "ID INT, TextMessage NVARCHAR(255)")]
    public static IEnumerable HelloWorldTVF()
    {
        SqlString[] stringArray = new SqlString[2]  { "Hello World!" ,
                                                       "This is my first TVF!" 
                                                     };
        return stringArray;
    }
 
    public static void FillRowHelloWorldTVF(Object obj, out SqlInt32 ID, out SqlString TextMessage)
    {
        SqlString textMesage = (SqlString) obj;
        ID = identity++;
        TextMessage = textMesage;
    }
}

The first thing to notice is the addition of the "using System.Collections;" using directive. This will allow us to use the IEnumerable interface that we need for passing the row to the fill row method.

At the beginning of the UserDefinedFunctions class you will see that I created a static variable named identity that I will use to simulate an identity column. Something interesting to this approach is that the value of the variable will keep incrementing on each successive call to the function until the CLR module is unloaded.

The HelloWorldTVF method is easy as can be. It creates an array of type SqlString that will be returned by this metod as an IEnumerable interface. Notice that on the function attributes I set the value "FillRowHelloWorldTVF" to the FillRowMethodName attribute and the value "ID INT, TextMessage NVARCHAR(255)" to the TableDefinition attribute.

Finally, on the FillRowHelloWorldTVF method we have to cast the object received as parameter as a SqlString object. Remember that in the HelloWorldTVF we returned through the IEnumerable interface an array of SqlString type elements.

Build and Deploy

Now it's time to compile the solution to obtain the assembly DLL library. To do so, you have to go to the Build menu and click on the "Build Solution" menu item.

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_TVF with the following Transact SQL code. Remember to change the path to point to your DLL file.

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

In case you receive an error telling you that the assembly is not trusted you will need to set the TRUSTWORTHY database option to ON in your test database by using the following.

ALTER DATABASE Your_DB_Name SET TRUSTWORTHY ON;

After creating the assembly, we can proceed and create the table valued function in SQL Server with the code that you can see below.

CREATE FUNCTION [dbo].[HelloWorldTVF]()
RETURNS TABLE
(
    [ID] INT NULL,
    [TextMessage] NVARCHAR(255) NULL
)
AS
EXTERNAL NAME [CLR_TVF].[UserDefinedFunctions].[HelloWorldTVF];
 

Finally, we can test our first table valued function with a select statement.

SELECT * FROM dbo.HelloWorldTVF();
GO

On the next screen capture you will see what we get when executing the select statement above.

Get Drive Info with a Table-Valued Function

I want to show you another example, much like a real-world usage. We are going to create a Table-Valued function that returns the properties of the System.IO.DriveInfo .NET class, which as you may guess, returns information regarding system drives.

Here is the code.

using System;
using System.IO;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
 
public partial class UserDefinedFunctions
{
    [SqlFunction(FillRowMethodName = "FillRowDriveInfoTVF", TableDefinition = "AvailableFreeSpace BIGINT, DriveFormat NVARCHAR(20), " +
        "DriveType NVARCHAR(20), IsReady BIT, Name NVARCHAR(512), RootDirectory NVARCHAR(512), TotalFreeSpace BIGINT, " +
        "TotalSize BIGINT, VolumeLabel NVARCHAR(255)")]
    public static IEnumerable DriveInfoTVF()
    {
        DriveInfo[] allDrives = DriveInfo.GetDrives();
        return allDrives;
    }
 
    public static void FillRowDriveInfoTVF(Object obj, out long AvailableFreeSpace, out SqlChars DriveFormat, out SqlChars DriveType,
        out SqlBoolean IsReady, out SqlChars Name, out SqlChars RootDirectory, out long TotalFreeSpace, out long TotalSize,
        out SqlChars VolumeLabel)
    {
        DriveInfo driveInfo = (DriveInfo)obj;
        if (driveInfo.IsReady)
        {
            AvailableFreeSpace = driveInfo.AvailableFreeSpace;
            DriveFormat = new SqlChars(driveInfo.DriveFormat);
            DriveType = new SqlChars(Enum.GetName(typeof(DriveType), driveInfo.DriveType));
            IsReady = new SqlBoolean(driveInfo.IsReady);
            Name = new SqlChars(driveInfo.Name);
            RootDirectory = new SqlChars(driveInfo.RootDirectory.Name);
 
            TotalFreeSpace = driveInfo.TotalFreeSpace;
            TotalSize = driveInfo.TotalSize;
            VolumeLabel = new SqlChars(driveInfo.VolumeLabel);
        }
        else
        {
            AvailableFreeSpace = 0;
            DriveFormat = new SqlChars("N/A");
            DriveType = new SqlChars(Enum.GetName(typeof(DriveType), driveInfo.DriveType));
            IsReady = new SqlBoolean(driveInfo.IsReady);
            Name = new SqlChars("N/A");
            RootDirectory = new SqlChars("N/A");
 
            TotalFreeSpace = 0;
            TotalSize = 0;
            VolumeLabel = new SqlChars("N/A");
        }
    }
 
}

By looking at the code above, you will see that there are two member functions: DriveInfoTVF the main function to be called from SQL Server and FillRowDriveInfoTVF which as its name says, is the fill row method.

The code shares the same dynamics as the previous HelloWorldTVF example. The main function (DriveInfoTVF) creates an array of objects of type DriveInfo that the function returns as an IEnumerable interface.

Then the fill row function (FillRowDriveInfoTVF) receives as input parameter a variable of type object and the rest of the variables are passed by reference using the out directive. In the function body only assign each property of the object to its matching variable.

In order to test this code, we have to rebuild the project and then create the assembly and function.

To keep things simple, we will remove the first function we created above and the assembly.

-- remove existing function and assembly if already created
DROP FUNCTION dbo.[HelloWorldTVF]
DROP ASSEMBLY [CLR_TVF]
GO

Then we will create the new assembly.

-- create new assembly
CREATE ASSEMBLY [CLR_TVF]
AUTHORIZATION [dbo]
FROM
'C:\Users\Daniel\source\repos\CLR_TVF\bin\Debug\CLR_TVF.dll' WITH PERMISSION_SET=UNSAFE
GO

Then we need to create the DriveInfoTVF function.

CREATE FUNCTION [dbo].[DriveInfoTVF]()
RETURNS TABLE
(
    AvailableFreeSpace BIGINT,
    DriveFormat NVARCHAR(20),
    DriveType NVARCHAR(20),
    IsReady BIT,
    Name NVARCHAR(512),
    RootDirectory NVARCHAR(512),
    TotalFreeSpace BIGINT,
    TotalSize BIGINT,
    VolumeLabel NVARCHAR(255)
)
AS
EXTERNAL NAME [CLR_TVF].[UserDefinedFunctions].[DriveInfoTVF];
GO

Finally, we can test our new table-valued function by querying it with a SELECT statement.

SELECT * FROM [dbo].[DriveInfoTVF]()

On the next screen capture you can see the result of its execution.

Next Steps

• You can take a look at another tip that covers the basics of Table-Valued functions: Introduction to SQL Server CLR table valued functions.
• 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
• Are you looking for an introduction to CLR Scalar functions? Take a look at my previous article: SQL Server CLR Scalar Functions.
• 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 SQL Server CLR Introduction tip if you are giving your first steps into 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