THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

James Luetkehoelter

Nearly any SQL topic presented at times in a slightly eclectic manner.

Survey: Do you write custom SQL CLR procedures/functions/etc

I'm quite curious because despite the great capabilities of writing CLR-based stored procedures to off-load those nasty operations TSQL isn't that great at (like iteration, or complex math), I'm continuing to see a wealth of SQL 2008 databases with complex stored procedures and functions which would make great candidates. The in-house skill to create the CLR code exists as well, but there is flat out resistance to use it. In one scenario I was told "Oh, iteration isn't a problem because we've trained everyone to use WHILE loops instead of CURSORs" (um, still iteration folks...find a set-based solution or get that 11-hour processing stored procedure out of TSQL!).

So, who uses them? What do you use and what kinds of problems have you solved?

Published Wednesday, March 31, 2010 8:19 AM by James Luetkehoelter
Filed under: ,



Scott Whigham said:

I have a few that I use - mostly for either string processing or XML shredding. Nothing fancy though - I wouldn't say that I have too many opportunities to use them; generally the needs are on one side of the other rather than a decision involving both (if that makes sense). My string CLRs are of the generic regex or validation variety - nothing impressive :)

March 31, 2010 9:52 AM

daveballantyne said:

You may find this thread on SSC informative

March 31, 2010 10:04 AM

James Luetkehoelter said:

Thanks Scott - but that's a great example of something TSQL doesn't do well (string manipulation) that is quite easy to implement as a CLR function or stored procedure using the regex stuff...

Actually that one sentence didn't make sense :) Are you getting at pragmatic issues (like time to create) outweighing where the best place for a specific type of processing is?

March 31, 2010 10:06 AM

James Luetkehoelter said:

Thanks Dave, a nice thread, although there are a few items in there that scare me (but I'll leave that for forthcoming rant) :)

March 31, 2010 10:18 AM

John Q. Newman said:

Survey: Can you post anything that's not a survey?

March 31, 2010 10:20 AM

James Luetkehoelter said:

Why yes I can John, and plan to - these surveys are leading to other posts...

March 31, 2010 10:23 AM

Jack Corbett said:

I have not used CLR as yet, because I have not found a problem in my environment that I needed to solve where I needed the CLR.  I'm not saying that there aren't good applications for it, just my environment has not had those requirements or was not large enough where I needed the performance gain the CLR might provide.

March 31, 2010 10:26 AM

Adam Machanic said:

Have been using it heavily, and hope to be releasing a new SQLCLR-based project soon. Watch my blog...

March 31, 2010 10:29 AM

BlackWasp said:

I haven't deployed anything but I looked at it for an integration. We wanted a trigger that would write information to an MSMQ queue but the messaging DLL isn't supported with SQL so the idea was dropped.

March 31, 2010 10:47 AM

Steve Abraham said:

I do use CLR, but sparingly.  There is a risk of moving business logic into the database using CLR sprocs, etc.  I generally only use the CLR for general purpose lightweight functionality or general functionality that cannot be achieved with SQL Server out of the box - like regular expressions.

March 31, 2010 10:48 AM

Dave Ott said:

I use CLR Stored Procedures to pull SharePoint List items into SSRS reports. We have several Score cards and the subjective info is in SharePoint. For example for Customer service audited called etc are entered into sharepoint by managers, but the number of calls answered, number of rings etc we have in the database.

March 31, 2010 11:29 AM
New Comments to this post are disabled

About James Luetkehoelter

I am passionate about what I do - which is DBA, development, IT and IT business consulting. If you don't know me, haven't met me or have never heard me speak, I'm a little on the eccentric side. One attendee recently described me as being "over the top". Yup, that about says it - because I only speak on topics that I'm passionate about.
Privacy Statement