Introduction to SQL Server CLR table valued functions

By: Diana Moldovan | Read Comments (6) | Print Diana is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008. Related Tips: More

Problem

Table Value Functions (TVF) are great if you need to return multiple rows and/or multiple columns, because table valued user defined functions return their result as a table. Since result set acts like a table, you can just return the data or you can join the results to other tables.  TVFs can be created using TSQL or the Common Language Runtime (CLR) and in this tip we walk through a simple example of a CLR function that returns file level contents from a folder.

Solution

Let's create a basic CLR table valued function.

In this example I'll use Visual Studio 2010 with C#. The table value function I'll build will search a given directory for files with names matching a search pattern.  In a previous tip, how to create and deploy the CLR function project in Visual Studio, I outlined a few things that you have to do such as enabling the CLR on your instance and setting up your Visual Studio project.

Here is the C# code for this function.  The function will search the files in a given directory and will return the file name, its size in bytes and its creation time based on the file filter that is used.

using System;
using System.IO;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
    private class FileProperties
    {
        public SqlString FileName;
        public SqlInt64 FileSize;
        public SqlDateTime CreationTime;
        public FileProperties(SqlString fileName, SqlInt64 fileSize, 
        SqlDateTime creationTime)
        {
            FileName = fileName;
            FileSize = fileSize;
            CreationTime = creationTime;
        }
    }
    //The SqlFunction attribute tells Visual Studio to register this 
    //code as a user defined function
    [Microsoft.SqlServer.Server.SqlFunction(
        FillRowMethodName = "FindFiles",
        TableDefinition = "FileName nvarchar(500), FileSize bigint, 
        CreationTime datetime")]
    public static IEnumerable BuildFileNamesArray(string targetDirectory, 
    string searchPattern)
    {
        try
        {
            ArrayList FilePropertiesCollection = new ArrayList();
            DirectoryInfo dirInfo = new DirectoryInfo(targetDirectory);
            FileInfo[] files = dirInfo.GetFiles(searchPattern);
            foreach (FileInfo fileInfo in files)
            {
                //I'm adding to the colection the properties (FileProperties) 
                //of each file I've found  
                FilePropertiesCollection.Add(new FileProperties(fileInfo.Name, 
                fileInfo.Length, fileInfo.CreationTime));
            }
            return FilePropertiesCollection;
        }
        catch (Exception ex)
        {
            return null;
        }
    }
    //FillRow method. The method name has been specified above as 
    //a SqlFunction attribute property
    public static void FindFiles(object objFileProperties, out SqlString fileName, 
    out SqlInt64 fileSize, out SqlDateTime creationTime)
    {
        //I'm using here the FileProperties class defined above
        FileProperties fileProperties = (FileProperties)objFileProperties;
        fileName = fileProperties.FileName;
        fileSize = fileProperties.FileSize;
        creationTime = fileProperties.CreationTime;
    }
};

The "FileProperties" class will return the file name, its size in bytes and its creation time.

The "BuildFileNamesArray" method actually implements the function. This piece of code must implement the IEnumerable interface. According to BOL documentation,, TSQL table valued functions materialize the results of calling the function into an intermediate table, whereas CLR table values functions use a streaming model, and therefore the results can be consumed as soon as the first row is returned.

The result of the IEnumerable object (here called BuildFileNamesArray) is consumed incrementally. I've used the ArrayList class, which already implements IEnumerable. I've annotated it with the "SqlFunction" attribute, and therefore it will be registered as a function. In addition, I've changed the default values of a few properties of this attribute. I've set the "FillRowMethodName" property to "FindFiles". This is the name of the method which will be used to fill a row of data in the table returned by the function. The "TableDefinition" property sets the structure of the table returned by a table-valued function. In this case, the function will return the name of the file, its size in bytes and its creation time. Keep in mind that there are a few restrictions regarding the data types such as the columns of the returned table cannot be timestamp columns or non-Unicode string data columns such as char, varchar and text.

The "FindFiles" method will be used to fill the rows of the final table. It takes an object of the "FileProperties" type and breaks it into the 3 file characteristics we need - file name, size and creation time.

Build and Deploy

Before building and deploying the project, right click on the project name, choose properties, and from within the properties page set the permission level to External. This way the assembly will be created WITH PERMISSION_SET = EXTERNAL_ACCESS, which enables the access to external resources such as the file system.

Additionally, the TRUSTWORTHY database property should be set to ON. In fact, these security requirements are best described by a SSMS error message: "the database owner (DBO) should have EXTERNAL ACCESS ASSEMBLY permission and the database should have TRUSTWORTHY database property on; or the assembly should be signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission".

Debugging

If something goes wrong, you may need to debug your Visual Studio project. Go to the Test.sql file under the Test Scripts folder in your project and add a script which calls your function. Mark the line of code with a break point and start debugging. For example, you can use this code for testing purposes:

SELECT * FROM 
dbo.BuildFileNamesArray
    ('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup', '*.bak')

Sometimes finding what's wrong can be tricky. For example the project builds successfully, but deploying the project from within Visual Studio fails.

To create a build failure, change the "TableDefinition" property for file name from "FileName nvarchar(500)" to "FileName varchar(500)". Deploying the project will fail because of the varchar data type I've used in the table definition.

A "Deploy failed" message will appear in the bottom left of the screen. To see what's wrong, open the [ProjectName].sql file from the bin\Debug or bin\Release folder of your project. On my machine the project name is "SearchFiles", so the file name will be SearchName.sql. This file contains the TSQL executed behind the scenes. Run the script in SQL Management Studio and examine the errors in the Messages tab, but before running the script change to "SQLCMD Mode". When I run the code in SSMS, I got the below error message, so I can see that varchar is not supported for the FileName column.

Dropping [SearchFiles].[SqlAssemblyProjectRoot]...
Dropping [dbo].[BuildFileNamesArray]...
Dropping [SearchFiles]...
Creating [SearchFiles]...
Adding files to assembly [SearchFiles]
Creating [dbo].[BuildFileNamesArray]...
Msg 6514, Level 16, State 3, Procedure BuildFileNamesArray, Line 1
Cannot use 'varchar' column in the result table of a streaming user-defined 
function (column 'FileName').
** An error was encountered during execution of batch. Exiting.

Next Steps

• The Common Language Runtime CLR Tips section offers lots of useful CLR integration examples
• This book excerpt is a good documentation about the CLR table valued functions. It describes in detail the SqlFunction attribute and its syntax.

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code