Using CLR User Defined Functions in SQL Server

Question

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?

Answer 1

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?

Answer 2

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.