It is strange that two important programming techniques in a SQL Server Database can’t be done in TSQL. The first is, of course, being able to read multiple results from a stored procedure. You can do it in ODBC/ADO and so on, but not in TSQL. The other obvious failing is that one cannot write user aggregate functions (which perform a calculation on a set of values and returns a single value) in the same way as scalar or table functions, except by using the CLR integration. TSQL can’t help you to add your own functions to SUM(), COUNT(), MAX() MIN() etc.

CLR integration was, along with Notification Services, the surprising turkey of the SQL Server 2005 launch. In reality, it was often doubling the development time over T-SQL procedures/functions. Whereas the task of building a function in T-SQL is trivial (load up management studio, edit the template, save and test), the construction of a CLR function was only made easy in the expensive VS Pro. Even so, there were two extra steps and a lot more for us who had only VS Standard.  Microsoft had failed to build the CLR development process into Management Studio. CLR integration was over-sold as an alternative to TSQL rather than a replacement for Extended Stored procedures.

Where you have to construct a custom aggregate function, then CLR is the only way there is, so should that be enough to send us scurrying to our cheque-books to buy VS Pro?  We had an editorial discussion about doing a workbench on CLR Aggregate functions. Phil loved the idea, but insisted on a really useful example, that readers would actually want.  Even a couple of Adnams Broadsides failed to bring from any of us an example besides concatenation, and that is already in Books-on-Line, and can be done without CLR. The idea is still there on the back-burner.

So, what are you using CLR for? Are you writing user aggregation functions, or using it to access powerful CLR libraries such as Regex? Even if you’ve looked at it and abandoned the idea of using it, we’d be interested to hear why. Usual rules apply – a prize to the best contribution.

Cheers,

Tony.