Streaming Table Valued Functions
People who have worked with SQL Server are familiar with table valued fuctions (TVF). A TVF is one that returns as its output a table which can then be used as part of a SELECT statement or consumed in other manner. One of the things to remember with T-SQL TVFs is the fact that when you request for output from the function, SQL Server materializes the results into a temporary table and then returns the results to you. This means that if the function returns a few thousand rows, it might take some time for your query to start processing the results from the function.
With the integration of the CLR into SQL Server 2005, you now have a new variant of the TVF. Its called the streaming TVF and its written using .NET code. The mechansim here is very similar to how you would implement objects that implement IEnumerable and IEnumerator. The SQL Server query engine calls into a method in your class that implements the IEnumerable interface and then iterates over the various elements using the GetEnumerator method that returns an instance of another object that implements the IEnumerator interface. Where the behavior of the streaming TVF is different from the regular T-SQL TVF is that, in the streaming TVFs, the results are made available as soon as they are present and your client application can continue to receive them without having to wait for all the results to be materialized. This can result in faster applications that call TVFs which return large rows. The implementation mechansim is very simple, however, because of the streaming behavior, there are a few limitations, but they are quite minor.
I've written an article about this new feature at my web site: SQLCLR Streaming Table Valued Functions (TVFs). This articles explores the basics of this feature and shows the equivalence in the .NET world and how the new SQLCLR version works. Do send me your feedback about this article at srisamp@gmail.com.