Jeff Moden (12/23/2009)
Next question, please... What application would have the need for such a thing where it could not be done using T-SQL? Not trying to be a smart guy here... I really want to know where folks have used or will use this type of thing and why they couldn't do it in T-SQL.
Hey Jeff. If by "such a thing" you are referring to CLR TVFs (and maybe Procs as well) in general, then there are some places that T-SQL does not cut it: Regular Expressions (especially doing a Replace), File System functions, and Internet related functions (e.g. FTP, Web GET / POST, Twitter, Ping, etc.). There are also some hashing functions (SHA256 and SHA512) that are not available in T-SQL.
Also there are places where T-SQL can work but I am not sure it is worth it. For example, in my SQL# project I have a BusinessDays function that works like DATEDIFF but you can configure it to use any number of "holidays". This way you do not need a "holiday" table that cannot detect Thanksgiving or Easter each year without having new entries added. And yes, that can most likely be done in a T-SQL TVF but given some of the calculations needed to come up with Easter (for both Eastern and Western churches as Easter is not the same day across the planet) it does not seem prudent to put non-set-based math in T-SQL. Not to mention how ugly and unreadable it would be. Another example is the URI class in .Net. I have a customer who was trying to parse the various parts of a URI using complex Regular Expressions but it wasn't very reliable. But the URI class in .Net is reliable and so I exposed that and it works great for him (and possibly many others now).
Matt Miller (#4) (12/23/2009)
I had attempted to use something like this to bring in data from an external file. One of those "ugly delimited" files, kind of delimited and kind of not, that I needed to bring in. This would have offered a way to parse it and clean it up in one single shot, and I would simply need to insert the results from the TVF into my destination table, skipping the whole staging table, clean up the data, etc...I ended up abandoning it because it was trying to build the entire table in mem before returning it, so the CLR function would crash before getting anything out. So - I ended up going in another direction and got it done that way.
Hey Matt. I would have suggested that for this problem you could have done a CLR Stored Procedure since, as you mentioned, they effectively do stream by returning each row as it is generated. The one thing that I have found that CLR Stored Procs can do that CLR TVFs cannot is return a dynamic result set where the columns aren't known ahead of time and can change between executions. In fact, I have a Stored Proc in my SQL# project that reads a file from disk and splits it into columns using a RegEx delimiter. It is called File_SplitIntoFields. It is not in the Free version but you can see a description of it in the User Manual which is on the Downloads page.
Also, another option if you are using SQL Server 2008 is to create a stand-alone .Net app (or include this in part of an existing application) to stream the data from disk into a T-SQL Stored Proc that accepts a Table-Valued Parameter (TVP). My previous article (http://www.sqlservercentral.com/articles/SQL+Server+2008/66554/) shows how to do this :-).
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR