CLR functions in SQL,Insane or Brilliant?

Question

CLR functions in SQL,Insane or Brilliant?

Jonathan Kehayias

One Orange Chip

Points: 26778
More actions
November 3, 2008 at 11:45 am

#214810

Since it seems that there is a desire for discussion of this topic, I decided to create a new post for the topic. There has already been a lot of debate regarding the proper use of CLR in SQL and whether or not it makes sense. If you have a question on the subject, which was selected based on a reference in another post, ask it here. Debate is fine, but keep in mind that you are going to get opinion back that you may not agree with and that is fine, since everyone is entitled to their own opinion.
As for my take on CLR inside SQL:
I think it has good utility when applied appropriately. I personally don't think a new developer should touch the stuff. A lot of the implementations I see going into SQL CLR and causing people lots of problems that lead to questions on the various forums have absolutely not business inside of SQL Server, in my opinion. If you can't first do it with TSQL, OLE Automation, xp_cmdshell, or other native functionalities, then you probably shouldn't be trying it in CLR.
One place that I like to use CLR is to eliminate a xp_cmdshell call like doing a filesystem operation or bcp import into SQL Server inline in a stored procedure. If you enable xp_cmdshell in SQL Server 2005, then there is no control over what can or can not be run from the command line using it. If you replace that need with a CLR function or stored procedure, there is much tighter control since the code behind dictates the functionality. Regular Expression Replacements/Matching is faster in CLR than using the OLE Automation routines that were previously used in SQL 2000. I am sure there are other examples of appropriate usage, but it really takes a good bit of experience in my opinion to decide whether or not to use CLR inside SQL Server.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

I believe you've hit upon the only two uses that I'd even think of letting someone write a CLR for. 🙂

The other thing is that, with the exclusion of the two problems you've identified, people give up on T-SQL way too early and resort to RBAR methods including Cursors and CLR's. And, your point is well taken... if you have to write a bunch of CLR's to get the job done, you shouldn't be trying to do it from SQL... write an application, instead! 😉

I forget who it is, but one good fellow has a "by line" in his signature on this fine forum that says something like "DTS and Cursors are for people who don't really know T-SQL". I'll add CLR's to that fine mix.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

I am sure that there are other various utility ways to do things in CLR beyond what I have provided above. I haven't had much need for it beyond DBA related tasks that are easier done in CLR than in TSQL, and as you say, at a certain point a external application just makes more sense to me. However, there are those out there who would opt for the simplicity of solving a problem in CLR over the required TSQL to solve the same problem, even if there is a slight hit to performance.

One area I didn't cover in my first post was the power of using CLR Aggregates for things like median/mode/FFT (Fast Fourier Transformation) calculations. However, these have a very specific uses and very specific applications. They aren't need in 95% of database applications. There is a trade off for having them in CLR and that is, you run more CPU utilization on the database server, but you save the cost of moving large volumes of data over the network to the app to do the same calculation.

I can definately see potential for where CLR can solve complex problems, but I rarely see CLR used in this manner. It is like XML, and cursors, when applied properly, it can be very powerful to solve complex problems, but it is easily misused. A guy at work today made the statement, "When all you have is a hammer, then every problem becomes a nail." He was referring to a process in our Oracle environment, where years ago someone wrote a process that was better solved another way. However, the developer then only had knowledge of one way to do things, and the process built really reflect that. I see .NET programmers making this same kind of mistake today in SQL Server, where they will write CLR procedures that use nested datareaders (cursor anyone) to do what could easily be done in a single set based operation with TSQL alone.

I personally have written a lot of CLR code inside SQL Server, just to figure it out, and learn the ins and outs for where it is a good fit or not. For reference, I have little to no CLR running inside a production SQL Server. I refuse to write it off as a possible solution outright, but I would need a good reason to implement it in production use.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

Jonathan Kehayias (11/3/2008)
there are those out there who would opt for the simplicity of solving a problem in CLR over the required TSQL to solve the same problem...

Heh... that's exactly what I'm talking about. Most TSQL problems are not difficult... people just don't want to think about how to do them. Not even Mode or Median...

And, I've seen the CLR code that some folks write... ya call that simple??? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)