Section 5.2. Table-Valued Functions

5.2. Table-Valued Functions

A table-valued function (TVF) is a UDF that returns a table. A TVF is implemented as a method of a class in a .NET Framework assembly that returns data as an IEnumerable or IEnumerator object. The columns of the return table cannot include timestamp columns or non-Unicode string data columns such as char, varchar, and text.

CLR TVFs are similar to their T-SQL counterpartsthe main difference is that a T-SQL TVF temporarily stores results in an intermediate table, whereas a CLR TVF streams results back to the consumer. As a result, a T-SQL TVF supports constraints and unique indexes on the result set, whereas a CLR TVF can be consumed incrementally once the first row is availablethe result set does not have to be fully materialized before returning values.

The following example creates, registers, and executes a table-valued function that returns a table containing the Name, Length, and ModifiedDate for each file in a specified directory. Follow these steps:


The IEnumerator interface supports simple iteration over a nongeneric collection. It is the base interface for all nongeneric enumerators . An enumerator can read the data in the underlying collection but cannot be used to modify the data. IEnumerator has one public property, Current, and two public methods, MoveNext( ) and Reset( ). Initially the enumerator is positioned before the first element in the collection.

  • The Current property returns an object containing the current element in the collection. You must advance the enumerator from its initial position to the first element in the collection by calling MoveNext( ) before reading the value of the Current property. Reading the Current property when the enumerator is not positioned on an element in the collection (before the first element or after the last element) returns an InvalidOperationException.

  • The MoveNext( ) method advances the enumerator to the next element in the collection. MoveNext( ) returns TRue if the enumerator was successfully advanced and false if the enumerator has passed the end of the collection.

  • The Reset( ) method sets the enumerator to the initial position before the first element in the collection.

The IEnumerable interface has a single method, GetEnumerator( ), which returns an IEnumerator object.

  1. Using the Visual Studio 2005 IDE, create a new SQL Server project named TableUdf.

  2. Create a user-defined function item in the project. Name the function ReadDirectoryFileInfo.

  3. Replace the code in the class with the following code:

         using System;     using System.Collections;     using System.Data;     using System.Data.Sql;     using System.Data.SqlTypes;     using Microsoft.SqlServer.Server;     using System.IO;     public partial class UserDefinedFunctions     {         [SqlFunction(FillRowMethodName = "FillRow", TableDefinition =             "FileName nvarchar(256), Size int, DateModified datetime")]         public static IEnumerator ReadDirectoryEntries(string path)         {             return new DirectoryLoader(path);         }         private static void FillRow(object obj, out SqlString fileName,             out SqlInt64 fileLength, out SqlDateTime dateModified)         {             if (obj != null)             {                 DirectoryEntry de = (DirectoryEntry)obj;                 fileName = de._fileName;                 fileLength = de._fileLength;                 dateModified = de._fileDateModified;             }             else             {                 fileName = SqlString.Null;                 fileLength = SqlInt64.Null;                 dateModified = SqlDateTime.Null;             }         }     }     public partial class DirectoryLoader : IEnumerator     {         // array that stores the directory entries         private FileInfo[] fia;         private int index = -1;         public DirectoryLoader(string path)         {             string[] files = Directory.GetFiles(path);             fia = new FileInfo[files.Length];             for (int i = 0; i < files.Length; i++)                 fia[i] = new FileInfo(files[i]);         }         public object Current         {             get             {                 if (index != -1)                     return new DirectoryEntry(fia[index].Name,                         fia[index].Length, fia[index].LastWriteTime);                 else                     return null;             }         }         public bool MoveNext(  )         {             if (index == fia.Length - 1)                 return false;             index++;             return true;         }         public void Reset(  )         {             index = -1;         }     }     public partial class DirectoryEntry      {         internal string _fileName;         internal long _fileLength;         internal DateTime _fileDateModified;         public DirectoryEntry(string fileName, long fileLength,             DateTime fileDateModified)         {             _fileName = fileName;             _fileLength = fileLength;             _fileDateModified = fileDateModified;         }     } 

    The code contains three classesUserDefinedFunctions, which implements the TVF, and two helper classes:


    The method ReadDirectoryEntries( ) implements the TVF. It is annotated with the SqlFunction attribute described in the preceding section, "Scalar-Valued Functions." The SqlFunction attribute identifies the public method FillRow( ) as the method that SQL Server uses to map the current enumerator element to a row in the table that is returned from the TVF. The SqlFunction attribute also specifies the TableDefinition property, which defines the record in the table returned from the TVF.


    The enumerator that creates a collection of directory entries for a path specified as an argument to its constructor. The contents of the directory are stored in a FileInfo array named fia. The Current property of the enumerator returns a DirectoryEntry instance containing the filename, file length, and date modified.


    Defines a class used to store the current element in the directory enumerator.

  4. Build the solution.

  5. In SQL Server Management Studio, register the assembly and create the table-valued function by executing this query:

         USE ProgrammingSqlServer2005     GO     ALTER DATABASE ProgrammingSqlServer2005     SET TRUSTWORTHY ON     GO     CREATE ASSEMBLY TableUdf     FROM 'C:\PSS2005\TableUdf\TableUdf\bin\Debug\TableUdf.dll'     WITH PERMISSION_SET = EXTERNAL_ACCESS     GO     CREATE FUNCTION ReadDirectoryEntries(@path nvarchar(256))     RETURNS TABLE       (FileName nvarchar(256), Length bigint, DateModified datetime)     AS     EXTERNAL NAME TableUdf.UserDefinedFunctions.ReadDirectoryEntries 

    Notice that the assembly is registered with EXTERNAL_ACCESS permission set to allow it to access the file system.

  6. Execute the table-valued function with the following T-SQL statement:

         SELECT * FROM ReadDirectoryEntries('c:\') 

    The results are shown in Figure 5-2.

Figure 5-2. Results for TVF example

Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton © 2008-2017.
If you may any questions please contact us: