I know it is a week late. Let’s just agree that I have been busy with other things. For those that are unaware, Meme Monday is where I assign a writing assignment for those that need to be given a topic to write about in order to help you blog more often. With Meme Monday posts you don’t need to tag others, you don’t need to link back to this post, and you don’t have to wait to be tagged. Just start writing!
Here is your assignment for next week:
SQLCLR: Good? Or Evil?
Give an example of how you have used CLR, or seen CLR used well, or not so well. Tell us why you may have decided to not use CLR at all.
The issue I had with CLR is that when a developer would build a CLR object (say, a stored procedure) and then complain about lousy performance on the database side, we couldn’t see the code that was in the assembly. And we didn’t have access to the source code (because we weren’t developers), so we would have to walk over to the developers cube to look at the .NET code in order to try to decipher what was being done. This made performance tuning and troubleshooting rather difficult, if not impossible.
I still remember being at the MTC in Waltham and the Microsoft folks many years ago telling me about how wonderful CLR was going to be and my asking the simple question “yeah, so how do I help tune code and queries I can’t see?” The look on their faces was priceless. “You could get a refactoring tool…I guess….we don’t know…why would you need to see it? It will execute just fine.”
Yeah. Just fine. Until it doesn’t run just fine. And then you need to fix it like a mechanic performing a tune-up in the dark with both hands behind their back.
Despite all of that I find CLR to be “mostly good”. I try to tell developers to use CLR any time they would be thinking of creating a .DLL for something. If whatever you are doing doesn’t need a .DLL, or an extended stored procedure, then you don’t need to be thinking about creating a CLR.
Of course regular expressions, but I don’t use those in production in my case.
Mathematical calculations. Basically a set formula (this is “model scoring” (predictive analytics)).
In the case of the scoring formulas, its just a deterministic scalar function into which several values are passed and a result is calculated.
But, as a former application programmer, I think that there is a big problem (which is being addressed by MS little by little) with the separation of application code and SQL code (and this is only worse in the non-MS development world).
As an application developer the SQL code is also not at all transparent. It’s annoying as an application developer to have logic “hidden” in SQL triggers and stored procs where seeing what’s happening becomes difficult and not obvious.
My dream, and probably that of many, would be to be able to trace into code and have it jump seamlessly between application source code and database layer code. To be able to step into a process that inserts a record, and then if there is an INSTEAD OF trigger or AFTER trigger on the table, to trace right into that code within the same environment as if it were just another source file.
I think MS is working in that direction, the new VS 11 and their stand alone Data Tools environment point in that direction.
But yeah, I think that writing and deploying CLR should be as simple as writing and deploying any normal SQL object, and debugging the code should be as simple as well.
Agreed!