Click here to monitor SSC

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.

CLR, beer and turkey

Published 6 June 2008 4:07 am

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.

21 Responses to “CLR, beer and turkey”

  1. BEACHDBA says:

    >> CLR integration was over-sold as an alternative to TSQL rather than a replacement for Extended Stored procedures

    Well said. Absolutely true. I never had a need to write my own Extended Stored proc, nor have I done anything with the CLR integration.

    That’s probably not going to win me any prizes.

  2. imassi says:

    I had considered it to replicate the functionality of a couple of functions in Excel. This was over a year ago, but I believe they were the FORECAST and LINEST functions. Lacking the time to delve into how to go about creating CLR functions (and lacking VS Pro), I decided to build a solution using stored procedures. My client wanted the values stored on the records of his table anyway, so they were calculated in a batch and the records updated accordingly.

    I also considered CLR as a possibility to perform an interesting calculation to determine how many agents in our call center were required to keep wait times low. Unfortunately, our migration to SQL Server 2005 has been delayed. I ended up building that calcuation in Excel using the Poisson function. This was about a year ago too.

    I still haven’t had time to learn how to implement CLR functions and look forward to one of your great workbenches should you put one together on the subject.

  3. mksql says:

    To date, the only use I have personally come up with is access to the mentioned RegEx methods. I have seen demos of UDTs implemented as classes (a currency type for foreign exchange data), but have not implemented personally.

  4. MVV says:

    So far , i’ve never needed any “extra kick” to deal with the bussiness rules in SQL server. I recall that i did some dll for Interbase , a long time ago , just to emulate SQL Server ISNULL , to port some SQL code verbatim to a linux host , and one string conversion function , that’s all.

    I’ve still to try doing some tinkering with the CLR , just for the hell of it, but , seriusly , we are talking about SQL Server. If we have to go all the way to do some programming in .NET to provide an answer , then we don’t undestand the question.

    Dabases are about storing and retrieving sets of data. To go up close and personal with data and do something ugly with the results in .NET is something better left to the programmers. I think all the hardcore DBAs have shudders thinking the big bad ugly programmers are going to do eldrich things to the way SQLServer works.

    I can conceive that being capable of doing something like

    Select Sigma2(price) from CurrentStock

    may appeal some people , but i have serious reservations about doing such things so near the core of the database engine.

  5. j.dbo.smith says:

    CLR integration looks like a viable replacement to SQL Server 2000 jobs that utilize the xp_commandshell. CLR integrations that execute an NT bat file can be integrated into a stored procedure and scheduled via the SQL Server scheduler. Many of our database maintenance jobs are in NT bat files. What we are still trying to figure out is the best way to secure the assemblies so that we can control what stored procedures utilize the CLR integrations.

  6. chriscr says:

    I have one useful example: We use a CLR-based function to parse datetime fields into TimeZone localized datetimes for report presentation. It is a simple CLR assembly that accesses the Windows API to do the time zone adjustment. It’s unsafe, so we have to sign it, but it is handy to let Windows manage the locale data for us instead of manually keeping up a table or a complex stored procedure.

  7. MVV says:

    To me it looks more presentation stuff that real DB proper work , but if it works for you , then all is well.

    Incidentally , i came across some stuff that would be well suited for CLR work. An estimate of the current network load during query execution. I started to wonder about perf counters and how in hell would i integrate that in the query result , and this topic came to mind. I’ll taste the idea while drinking a beer tonigth :)

  8. jarhed says:

    It is interesting that you mention regex as a good reason for implementing CLR solutions. I agree that if you want regex, that is indeed a good one. I am a software developer, and I mostly use a language that does not natively have a regex. Regex statements are generally against our coding standards: they stack multiple data manipulations on a single code line, and they hide simple transformations behind an abstruse command. I will go so far as to say most of the regex uses do not even work as intended (for example, enter the email address a@a into your favorite email editor). In any case, I suspect that SQL experts naturally tend toward regex-type solutions, but there are many developers who do not, and I suspect that never the twain shall meet.

  9. gordonp says:

    In over 10 years of working with previous version of SQL Server, I had cause to write only 1 extended stored procedure. I am expecting the same time frame for the need to write a CLR solution. I have installed a RegEx solution just as a test of the process. However, the need to use even these is a rare event. I say leave the CLR programming to the .NET programmers. I tend to agree with MVV that if we find ourselves in need of using the CLR, than we either don’t understand the problem or we are approaching the solution from the wrong direction.

  10. TadRichard says:

    The most frustrating limitation of TSQL, for me personally, is the inability to pass table names into a function, and use them in dynamic SQL.

    How about addressing this post in the Forums:

    http://www.simple-talk.com/community/forums/thread/53180.aspx

    with a CLR solution?

    It would be a great primer for me to utilizing CLR code in SQL server as I tend to stick with pure TSQL in SQL Server SPs and procedures, but am primarily a C# developer.

  11. gbrayut says:

    We use CLR stored procedures to allow customers to integrate directly with our application’s Web Service Interface (WSI). We provide a signed assembly with full source and instructions for setting it up in their database so that they have minimal work to get up an running. Web services are by far the best method of integration as they are compatible with may different programing languages (everything from javascript to CLR stored procedures) and still filter through our business logic layer. The assembly can be used to call the web service methods using a secure HTTPS connection and convert the XML SOAP messages into TSQL record sets. Below is an example from our WSI user guide (more info at http://demo.oqanalyst.com/OQAHS/Files/WSI/OQ_Analyst_Raw_WSI_Overview_And_Examples_2008MAY08.pdf ):

    Microsoft SQL 2005 or SQL Express CLR Stored Procedure
    If you are using a Microsoft SQL 2005 or SQL Express database you can use a special Common Language Runtime (CLR) stored procedure to access the WSI directly from the database. We have created a wrapper class for all of the WSI methods that can be imported as a Stored Procedure into an existing database. When you call the stored procedure, the CLR code will convert the request into a SOAP message, call the method on the OQ Analyst server, parse the results into an SQL record set, and return the data to your query. This allows you to quickly integrate with the WSI using standard MS SQL techniques such as triggers, cursors and constraints. Below is an example of how to run a query once the assembly has been loaded into your database.

    – Create temporary values and tables
    Declare @Message varchar(200)
    CREATE TABLE #Clients (PersonID Int, MedRecordNum VarChar(20), FirstName VarChar(20), MiddleName VarChar(20), LastName VarChar(20), BirthDate SmallDateTime, Diagnosis VarChar(25), Gender VarChar(10), ClinicianPersonID Int, ClinicianFirstName VarChar(20), ClinicianLastName VarChar(20), Clinic VarChar(25), Instrument VarChar(20))

    –Load clienINSERT #Clients
    t records into temp table
    EXEC WSI_GetAllClients ‘admin’, ‘password’, @Message OUTPUT

    –Print message
    Print @Message

    –Display Results
    Select * from #Clients

    –Clear temp table
    Drop Table #Clients

  12. BobMN says:

    I come from an Oracle background and one thing I really missed when moving to SQL Server was how PL/SQL has Autonomous Transactions (http://www.oracle-base.com/articles/misc/AutonomousTransactions.php) and TSQL doesn’t. So I think one really nice use of CLR stored procedures is to have the ability to have Autonomous Transactions within your TSQL stored procedure processes when the volume of data is just too large to process in one transaction.

    One simple example use of this is in error logging. You can create a CLR proc that creates a new database connection and commits an error message to an error log table even when the stored procedure rolls back everything else. I know for that you can also cheat and use a table variable as it doesn’t take part in the transaction, but the CLR lets you do this in a nice clean consistent manner.

  13. dullroar says:

    I use a single CLR function, which oddly enough is in a utility assembly I wrote before we went to SQL Server 2005 but which works well when I used the method as a function in SQL Server 2005 as well (wow, actual code reuse!) It was written to do “smart casing” of personal and city names and street addresses. By smart casing I mean:

    1) Capitalize the first character of every “word” and lowercase everything else, except for the following.

    2) Handle name suffixes like II and III as well as street directions like NW and SE and rural routes like Route AA correctly.

    3) Handle “Mc” prefixes correctly.

    4) A few other cleanup type operations around rationalizing punctuation.

    It was originally written to help with importing data from our IBM mainframe system (typical mainframe data in all caps) into Microsoft CRM. Since then I’ve used it in SQL Server 2005 for various mainframe-to-local-data mappings and cleanup.

    I typically try and do as much in T-SQL as possible, but as a C#/VB.NET programmer I would still consider using CLR functions when applicable. But other than the above (where I already had the code laying around) I haven’t found anything applicable (yet).

  14. G Mackenzie says:

    I would like to see CLR implementations of the new(ish) ansi sql aggregate/set functions that go with the new(ish) ansi sql boolean data type. The functions are EVERY, ANY and ALL. I am sure you could produce equivalent implementations based around sql server’s bit data type. The logic should be relatively straightforward, so we can see clearly what’s involved in implementing the CLR side of things. Thanks.

  15. aleonard763 says:

    Hi Tony,

    I used CLR integration once in production (so far). It was for a search and replace function. I could not get T-SQL to recognize a non-printable character so I created a CLR procedure to do the job. It worked really well and solved this particular issue.

    :{> Andy

  16. zenon says:

    1. CLR integration was a stupid idea.
    2. If you cannot add together the results of MAX or MIN or whatever (aka aggregate), then that is not a reflection of T-SQL, it is a limitation of your original thinking; aka, creativity, and I suggest not bragging about your limitations so openly.

    The only limitation I have found in T-SQL is the ability to directly minipulate the file system; but I have other built-in SQL Server capabilities for that. CLR was a fix for a non existing problem.

  17. Thomas Williams says:

    G’day Tony – I’ve used one CLR function to calculate Levenshtein distance (see http://www.merriampark.com/ld.htm). The implementation in T-SQL was far from efficient (at http://www.merriampark.com/ldtsql.htm), it was a no-brainer to implement it with the CLR.

    Cheers, Thomas

  18. sagreene says:

    Used it to make reporting services call a proc and have that proc generate a report and create a file for export. The user couldn’t be arsed to use the save as function – so we empowered user lazyness with the latest technology and didnt’ use the command shell function.

  19. Philip Kelley says:

    We’re currently using it to implement some pretty hairy calculations that need to be applied against a potentially large set of data across a potentially large spread of time (think data warehouse). The data could be read and sent to the web server, processed in .NET, and the results (about as much data as was originally read) sent back to the database server, but the decision was to do it all on one box. (Relatively low volume work, or so they say–if wildly popular, we’ll have scaling problems.) FYI, I’m a DBA/database developer, and this work was done by the application group.

    There’s talk of doing funky aggregations some day, but we’d be looking at Analysis Services first to solve that problem.


  20. Thomas Williams says:

    G’day Tony – you mentioned that I’d won the $50 Amazon voucher. You can contact me at my new blog at TheRuntime.com.

    Cheers!

  21. Dutch72 says:

    Hi Tony,

    I was contemplating of using a CLR stored proc for use in sql reporting.
    Initially all the hairy calculations were put in the reports. After creating multiple reports I am sick of fixing calculations in multiple places.

    I tried regular sql stored proc + UDF. Got to complex.
    Tried assembly for use in sql reporting. also got to complex.

    Would a CLR stored proc suit my needs?

Leave a Reply