SQL Server CLR Table Valued Function

By:   |   Updated: 2022-05-12   |   Comments   |   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.

Property Description Values
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.
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.
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.

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.

Create a new database project.

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

Configure your new project.

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.

Adding a new item to our project.

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

Adding a new SQL CLR C# User Defined Function.

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

New SQL CLR C# User Defined Function code template.

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.

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 to 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 calculus. 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.

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.

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_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.

Testing our first Table-Valued function.

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.

Testing the DriveInfoTVF function.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

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

View all my tips


Article Last Updated: 2022-05-12

Comments For This Article

















get free sql tips
agree to terms