Introduction to SQL Server CLR table valued functions


By:   |   Updated: 2012-01-16   |   Comments (14)   |   Related: More > Functions - User Defined UDF

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.

set the permission level to External

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.


Last Updated: 2012-01-16


get scripts

next tip button



About the author
MSSQLTips author Diana Moldovan Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Saturday, April 02, 2016 - 5:46:23 PM - Gravitas Back To Top

In order for this to work under Visual Studio 2015 + Update 2 + SQL Server 2014, you must also add the attribute "DataAccess = DataAccessKind.Read":

[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FindFiles", DataAccess = DataAccessKind.Read, TableDefinition = "FileName nvarchar(500), FileSize bigint, CreationTime datetime")]


Saturday, April 02, 2016 - 5:41:05 PM - Gravitas Back To Top

This will not work under Visual Studio 2015 Update 2 + SQL Server 2014, as the security has been locked down.

To fix, comment out the try/catch code, so the error will be displayed. It will complain about security permissions (by default, everything is locked down). For instructions on how to fix this, see:

http://stackoverflow.com/questions/15639213/how-to-grant-the-database-owner-dbo-the-external-access-assembly-permission/36379005#36379005

 


Friday, March 18, 2016 - 8:00:06 AM - Diana Back To Top

 Hi, Jennifer, thank you so much for appreciating my writing. Please feel free to this topic, as Greg already said.

Best Regards,

Diana Moldovan

 


Thursday, March 17, 2016 - 9:38:17 AM - Greg Robidoux Back To Top

Hi Jennifer,

this is totally fine linking to this article.

Thanks
Greg Robidoux
MSSQLTips co-founder


Wednesday, March 16, 2016 - 9:10:02 PM - Jennifer Hubbard Back To Top

Hi Diana,

I manage the Microsoft technical documentation team that covers, among other things, SQL Server. I enjoyed your post on CLR table valued functions. I noticed that you link to one of our topics - https://msdn.microsoft.com/en-us/library/ms131103(l=en-us,v=SQL.130).aspx. This topic does not do a good job at all of really explaining this to folks who don't already know. I am wondering if we could link to your topic (https://www.mssqltips.com/sqlservertip/2582/introduction-to-sql-server-clr-table-valued-functions/) on our page to help users find better introductory information?

Please let me know if that would be ok with you.

All the best!

Jennie

Jennifer Hubbard

Senior PM Manager, Technical Documentation, C+E, Microsoft

 


Tuesday, September 03, 2013 - 6:59:54 AM - Andrej Back To Top

I would like to point out something peculiar. I have created a two dim array in method which implements TVF. I have expected return value of that type will be passed to "object" type of FillRow method.

Instead of receiving string[,] type inside FillRow method, I have received simple string as type of "value" parameter, precisely System.String. Could you explain me what is the reason for such behaviour.

Diana, your idea was to wrap up data inside collection of objects (named FilePropertiesCollection). My idea was to pass data as a two-dim array. 

 

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Collections;

using System.Collections.Generic;

using System.Text;

 

namespace LicensePlateParser

{

    [SqlFunction(FillRowMethodName = "FillRow")]

    public static IEnumerable CLRParseString(String wsv)

    {

      string[,] tbl = new string[5, 4];

      //I have skipped code that fills table

      return tbl;

    }

    public static void FillRow(object value, out SqlString reg, out SqlString chkarray)

    {

      if (value == null)

      {

        reg = SqlString.Null;

        chkarray = SqlString.Null;

      }

      else

      {

        reg = (string)value;

        chkarray = value.GetType().IsArray.ToString();

      }

    }

  }


Monday, September 02, 2013 - 12:20:00 PM - Diana Back To Top

Henry, I'm not in office  right now; therefore I'm not able to check more thoroughly. Can you verify which are your permissions o the remote directory?

And sorry for being so late... 


Thursday, August 15, 2013 - 7:15:00 PM - Henry Back To Top

Thank you for writing this tip and sample code.  I finally was able to understand the TVP function and get a CLR TVP function working.  However, in my application I need to process and return data from a CAD file stored in a SQL File Table structure.  Therefore the targetDirectory is specified using UPC notation and the DirectoryInfo(targetDirectory) appears to not exist, causing the dirInfo.GetFiles(searchPattern) to fail.  I'm running on a 64 bit Windows 8 PC using VS2012, SS2012 and .NET 4.5. The code, which I think is unmodified from your sample, is as follows:

//------------------------------------------------------------------------------
//
//     Copyright (c) Microsoft Corporation.  All rights reserved.
//
//------------------------------------------------------------------------------

// SampleTableValuedFunction.cs - a sample CLR SQL Table-Valued Function to return rows to the calling stored procedure.
// Will use this to build Dgn7ParseTVP to return CAD Element rows
 
using System;
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;
    }
};

 

I have a SQL File Table defined which is mapped to the NTFS file system on my local machine as:

'\\DELLXPS16\mssqlserver\\NRDP_v2_FileTable_Directory\MicroStn FileSets\Test Folder' which contains a number of CAD files with and extension of '.dgn'.  I also have a copy of the files in my local C:\hweDevelopment folder which I use in the first test case.

-- test 01 from normal folder (works OK)
use NRDP_v2
go
SELECT * FROM
dbo.BuildFileNamesArray
    ('C:\hweDevelopment\WayToIntegration\NRDP_Dev\NRDP_v2\NRDP_v2\', '*.dgn')
go

-- test 02 from standard local share (UPC) - fails
use NRDP_v2
go
SELECT * FROM
dbo.BuildFileNamesArray
    ('\\DELLXPS16\DellXPS16Root\hweDevelopment\WayToIntegration\NRDP_Dev\NRDP_v2\NRDP_v2','*.dgn')
go

-- test 03 from SQL File Tables mssqlserver share (UPC) - fails
use NRDP_v2
go
SELECT * FROM
dbo.BuildFileNamesArray
    ('\\Dellxps16\mssqlserver\NRDP_v2_FileTable_Directory\MicroStn FileSets\Test Folder\', '*.dgn')
go

-- test 04 from mapped drive S: set to \\Dellxps16\mssqlserver share -fails
use NRDP_v2
go
SELECT * FROM
dbo.BuildFileNamesArray
    ('S:\NRDP_v2_FileTable_Directory\MicroStn FileSets\Test Folder\', '*.dgn')
go

So it would appear that the issue is with System.IO.DirectoryInfo and not the SQL File Table since test 02 is to a share that refers to the same files as test 01. 

On test 02, the following exception gets thrown on the FileInfo[] files = dirInfo.GetFiles(searchPattern); statement:

-        [System.UnauthorizedAccessException]    {"Access to the path '\\\\DELLXPS16\\DellXPS16Root\\hweDevelopment\\WayToIntegration\\NRDP_Dev\\NRDP_v2\\NRDP_v2' is denied."}    System.UnauthorizedAccessException

The StackTrace info is:

   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.FileSystemEnumerableIterator`1.CommonInit()
   at System.IO.FileSystemEnumerableIterator`1..ctor(String path, String originalUserPath, String searchPattern, SearchOption searchOption, SearchResultHandler`1 resultHandler, Boolean checkHost)
   at System.IO.DirectoryInfo.InternalGetFiles(String searchPattern, SearchOption searchOption)
   at UserDefinedFunctions.BuildFileNamesArray(String targetDirectory, String searchPattern)

I'm new at C# and the last time I was involved this deeply with the technology was back in the VB 3/4 and early SQL Server days.  I'm hoping you can shed some light on this problem for me.  Any assistance you can provide would be much appreciated.

 

Thank you,

Henry


Tuesday, January 17, 2012 - 3:02:58 PM - Diana Moldovan Back To Top

All - I'm looking forward for your ideas or requests for new tips.


Tuesday, January 17, 2012 - 12:05:24 PM - Jeremy Kadlec Back To Top

Diana,

Thank you for the URL.  Let's chat offline to see if we can come up with any test cases.  I think they could make for some good tips.

If anyone from the community has any ideas or issues they are working through, please post them as a future tip idea.

Thank you,
Jeremy Kadlec


Tuesday, January 17, 2012 - 10:00:28 AM - Diana Moldovan Back To Top

Jeremy, can you suggest a scenario for which you'd be interested in testing? For example, see this string split scenario: http://www.simple-talk.com/sql/t-sql-programming/clr-performance-testing/ - Table-Valued Functions (TVFs).

 

WEB, Harold - I prefer to do the .NET programming in C#, and it seems that other authors favor C# too. It's a personal choice.

 

 


Monday, January 16, 2012 - 5:44:39 PM - Harold Back To Top

 

In response to the previous comment, I don't think that the author is doing any such thing.  She merely seems to be illustrating her point by using C#.  A person wishing to implement a table value function is clearly (no pun intended) able to use any other language of their choosing.  Should have provided both VB and C# examples?  That would have consumed unnecessary space.


Monday, January 16, 2012 - 4:43:33 PM - WEB Back To Top

It appears the authors are making C# the lingua franca of SQL Server and SSIS. It would be nice to have some variety.
 


Monday, January 16, 2012 - 4:02:17 PM - Jeremy Kadlec Back To Top

Diana,

Have you done any performance testing with T-SQL table valued functions versus CLR table valued functions?

I would be interested in your results.

Thank you,
Jeremy Kadlec



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools