I have used CLR user defined functions for very specific instances, such as complex string handling, but generally didn't like them from a maintenance point of view (yet another place to hide code). What do people consider the pros and cons of this type of function, and are there any best practice guidelines as to when to use them, both from a performance and maintenance perspectives?

2 Answers 2


This is the classic argument, for or against stored procedures in general. The fact is, you can't stop people connecting to your database. Sure Joe in the warehouse will use the in-house stock control app that has all your business rules in, he's got a dumb terminal/web browser on the Intranet, he's got no choice. But if Bob the CFO wants to connect with Excel and do some pivot tables, are you going to say no, you can't do that, give me 6 months to write you a custom app in the application server tier? No. Or Jane SQL consultant who likes a command line client, and you're on vacation so you can't tell her no.

But you don't want to risk any integrity issues so put your validation and business rules somewhere there's no way round them, right up close with the data. Maybe T-SQL or C# isn't the prettiest language going - but SPs and triggers are the right tool for the job.

Other than that, managing code deployed in the database is actually no different to managing code deployed in an application server (e.g. J2EE, COM objects, whatever). It's a matter of just not treating it as anything "special", having proper discipline about versioning, building and releasing it, etc. How is it "hidden" at your site?


Generally, I prefer to add some CLR functions when possible to implement some of the ideas from the book Cryptography in the Database, such as hashing and salting. This way the clients don't need to worry about future maintenance developers incorrectly implementing password (or other data requiring protection) security.

I've worked at companies where encrypting stored procedures was done to preserve business trade secrets (frequently from the employees of the firm). Some of these databases would be hosted on the customers' servers, so security of the source code was paramount. If CLR in the database was available back then, they would have proceeded in that direction.

Finally, there are times when reporting functions are hard to implement in pure SQL, so a function can be implemented in CLR could allow for more complicated functions to be kept away from the report itself.

  • but but but ... I can't help but notice that CLR modules are written in .NET and get interpreted down to MSIL which makes them reversible whereas I've not heard of as many people reversing encrypted procedures. So which is really more secure?
    – jcolebrand
    Commented Feb 2, 2011 at 16:59
  • @jcolebrand, reversing MSIL takes an external tool, such as Reflector, which will no longer be free, and free versions will timebomb out at the end of May 2011. programmers.stackexchange.com/questions/42838/… As for encrypted stored procedures, please see the last paragraph at dba.stackexchange.com/questions/817/…
    – Tangurena
    Commented Feb 2, 2011 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.