Table-valued user-defined functions
T-SQL table-valued functions (or TVFs) existed in previous versions of SQL Server. In SQL Server 2005 we support creating TVFs using managed code. We call table-valued functions created using managed code "streaming table-valued functions," or streaming TVFs for short.

• They are "table-valued" because they return a relation (a result set) instead of a scalar. That means that they can, for example, be used in the FROM part of a SELECT statement.
• They are "streaming" because after an initialization step, the server will call into your object to obtain rows, so you can produce them based on server demand, instead of having to create all the result in memory first and then return the whole thing to the database.

Here is a very simple example of a function that takes a single string with a comma-separated list of words and returns a single-column result-set with a row for each word.

C#
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using Microsoft.SqlServer.Server;
public partial class Functions {
  [Microsoft.SqlServer.Server.SqlFunction
   (FillRowMethodName="FillRow")]
   // if you're using VS then add the following property
   // setter to the attribute above:
   // TableDefinition="s NVARCHAR(4000)"
  public static IEnumerable
   ParseString(string str) {
// Split() returns an array, which in turn implements 
// IEnumerable, so we're done :)
    return str.Split(','); 
   }
  public static void FillRow(object row,
    out string str) {
   // "crack" the row into its parts. this case is trivial  
   // because the row is only made of a single string
    str = (string)row;
  }
}
Visual Basic .NET
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions
Imports System.Runtime.InteropServices
Imports Microsoft.SqlServer.Server
Partial Public Class Functions
  <Microsoft.SqlServer.Server.SqlFunction
   (FillRowMethodName:="FillRow")> _
   'if you're using VS then add the following property 
   'setter to the attribute above:
   ' TableDefinition:="s NVARCHAR(4000)"
  Public Shared Function ParseString
   (ByVal str As String) As IEnumerable
' Split() returns an array, which in turn implements 
' IEnumerable, so we're done :)
      Return Split(str, ",")
  End Function
  Public Shared Sub FillRow(ByVal row As
    Object, <Out()> ByRef str As String)
  ' "crack" the row into its parts. this case is trivial
  ' because the row is only made of a single string
    str = CType(row, String)
  End Sub
End Class