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?
Sql-server – Using CLR User Defined Functions in SQL Server
sql-server-2005sql-server-2008
Related Question
- Sql-server – Do SQL Server Express Editions support CLR procedures
- Sql-server – How to call a remote User Defined Function with Parameters
- Sql-server – How to you save all user-defined functions to txt files on your local hard drive
- Sql-server – User defined function performance disadvantages
- Sql-server – User Defined Function Return value
- Sql-server – Why SQL server unload CLR assembly every midnight
- Sql-server – standard way to use common data and procedures across multiple databases
- SQL Server Optimization – Issues with User Defined Functions
Best Answer
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?