06 June 2008

CLR, beer and turkey

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.




For more articles like this, sign up to the fortnightly Simple-Talk newsletter.


  • Rate
    [Total: 0    Average: 0/5]

Tony Davis is an Editor with Red Gate Software, based in Cambridge (UK), specializing in databases, and especially SQL Server. He edits articles and writes editorials for both the Simple-talk.com and SQLServerCentral.com websites and newsletters, with a combined audience of over 1.5 million subscribers. You can sample his short-form writing at either his Simple-Talk.com blog or his SQLServerCentral.com author page.

As the editor behind most of the SQL Server books published by Red Gate, he spends much of his time helping others express what they know about SQL Server. He is also the lead author of the book, SQL Server Transaction Log Management.

In his spare time, he enjoys running, football, contemporary fiction and real ale.

View all articles by Tony Davis