Linking to pay sites is something that I really try to avoid. Mainly because we're a free resource, we work hard to stay free, and it seems silly to me to point you to another site where they want your hard earned money. But I'll make an exception because of the content.
There was an article over at Intelligent Enterprise by Joe Celko on Keys to the Database dealing with the integration of the CLR into SQL Server 2005. Then there were a few editorials (one, two, and three) over the last few weeks at SSWUG about the same topic with lots of people expressing opinions.
Then today I saw Joe Celko's Rebuttal to the editorials.
Now I respect Joe, and I know he's a whole lot smarter than I. But I don't often agree with him and I'm not usually thrilled with his delivery. But in this case, I think he has some great points to be made.
If you're not sure about what the debate is, and I'm still not 100% clear on it, read the article, the comments from the editorial, and the rebuttal from Joe. The comments tend to go off in different directions, but it seems that people are fairly split on the idea. Joe wanders around a bit in his rebuttal, but he doesn't like it.
Nor do I. I think CLR integration is a horrible idea. One that fundamentally goes against the idea of SQL Server and other RDBMS systems in that it moves things from a set based model to a row by row based, procedural model. Now I have seen some interesting models, such as check constraints using a CLR function, but I'm not sure that things wouldn't be better served with more robust SQL enhancements instead of allowing programmers to write code in the database.
And I really think Joe is right on with two points. First that your data is valuable. In many cases, it has a direct relationship to the success of the business. Which means that companies should spend some money to ensure that it is architected and built correctly. Not necessarily according to the way Joe Celko wants, but using some fundamental database principles, which I see time and time again not being used.
The second thing he brings up is that in many trades, especially building trades, someone has to pay their dues, get some experience under more experienced professionals before they are allowed to practice. Yet in the computer business we often take someone with little to no experience and throw them into a situation expecting them to build something we can use. I admit that's how I got started and it seems that I've done well. But for every person that does succeed, there are many that fail horribly.
At least the electrician that doesn't do a good job has a building inspector to ensure the work meets some standard.
Who's checking the CLR code of the rookie developers?
I don't think the CLR is particularly necessary for SQL Server, but it's nothing to be afraid of, either. Joe seems to think that programmers do whatever they want to fulfil the customer need (unless they're too busy playing with cool stuff).
If that's the way we run our development teams, then fine. Perhaps if you do that then you let your accounts staff use any old transaction type they like to get the books to balance, and sales staff can bribe on expenses, etc. But any sensible company is aware of standards and uses them. This is to make sure the code that goes live is going to work and be maintainable.
Sure, with the CLR you can use any language you like, and there are many. But any company that allows more than one or two is probably going to make a mess. My team uses VB, and we allow C# too from time to time for simple stuff as the VB guys can work with this. But everything is reviewed by senior developers and nothing which will not be maintainable is allowed through. It was the same with DTS, and is the same with more standard features like views, UDFs, trigger and stored procs. It's possible to write a terrible system with ANSI SQL (and I've seen a few).
Joe needs to remember that there are many ways to do most IT tasks. Some are better than others, but most designs are compromises. Being offered another tool is not an attack on software quality. Some things are hard to do in SQL. Sometimes this is because the developer is ignorant. Sometimes this is because the database design doesn't sit easily with the new feature or report we have been asked to produce, because when we did the design, we didn't know the customer would ask for such a thing. Therefore sometimes there are things that it is hard to do in a set-based way.
We all have to compromise between many things. Cost, design elegance, reliability, performance, maintainability, cool, etc. In a real world system, different compromises will be needed for different parts, and at different times. If I had a GBP for every time a theoretician had assumed that we know everything about an app before we design or write any of it, I'd have, well, enough for a few beers anyway. Hence, an extra way of doing things which adds so much is very welcome, but I will have to decide how it should be used and developers should expect not to be allowed to do just what they like. One day it may give me a way to do a project quickly which makes that project viable.
This will be another neverending story, I think.
It's fairly easy at the moment to write bad code for SQL Server. It will get even easier with CLR integration. I imagine all those VBA part-time developers who will then get tasked with database development. And I'm really curious on the questions that will come up here. However, Microsoft has chosen this the way to go and who will seriously argue against this?
I continue to make the journey from developer to DBA, and found Joe Celko's insight into the differences between the mindsets frighteningly accurate.
Allow me to offer this perspective using my experiences as a developer: I watched VB evolve as a language from nothing to .Net. There have been two major paradigm shifts in the language since its inception: the jump to 32-bit programing and classes (VB4) and the jump to .Net.
In each instance, the move was met with resistance in the community.
Is resistance to change bad? This, I believe, is the operative question. I'm trained as an engineer, so forgive my results-oriented analysis...
Regarding resistance to these changes, allow me quote Dr. Phil, "How's that workin' out for you?" Has Microsoft ever, in response to complaints, changed everything "back"?
I feel I can pick on VB6'ers who are resisting the move to .Net. Not all of them, only the ones who haven't learned how and when to use classes - even though the functionality was introduced in VB4 (circa 95, 96?).
My point is thus: Technology changes, by nature and design. If you want to work in a field where things remain relatively stable, technology isn't for you.
Further, resistance to change can harm your career. If you don't believe that now, just wait - you will.
My points are simple: 1) Microsoft never responds to complaints about change by going "back." They simply institute another change. 2) My money is on professionals who will use the CLR integration into SQL Server 2005 to accomplish things we cannot imagine today.
Just my $0.0162 (after taxes),
There's one thing everyone here has missed so far... security.
When we allow developers to write the code they know straight into the db, we pretty much immediately remove sql injection attacks... I've seen enough inline sql in old asp and even .NET that I had to run a demo against the companies development environment and drop the database from the website before anyone believed me. Removing this threat alone is worth having to double check the junior programmers code before it goes into production.
Who allows unchecked and untested code into production anyway?? Junior developer or senior, everyone makes mistakes. Bottom line, test your code, test your teams code, it doesn't matter where that code is, make sure everything works before the public uses it.
In case you can't tell, I'm excited to see what can be done, and how easily it can be done.
In many small shops, the database guy and the applications guy are the same person. And he has no training for the database role he has to play. He has no idea what a data dictionary is, what Normal Forms are or anything else that he absolutely needs to know to do the job.
This simply has to stop. If management will not pay for training then it should pay for an outside consultant to set up the schema and look in on it from time to time.
I find this comment strange from the standpoint of the following:
a. Treats all developers as DBA nincompoops.
b. Exalts that every shop should have a dedicated DBA. I've seen people handle both DBA and development roles quite well thank you.
c. This is just not reality for a lot of small firms. Granted, they can't lose their data and every manager should do dilligence in verifying he can recover from a disaster; however, the truth is small firms run lean and leaner all the time. That's not going to change in an environment where IT coding is outsourced and people are plied with more work than they can reasonably handle in a 60 hour week.
Here is a direct quote from MS white paper "Using CLR Integration in SQL Server 2005"
"Developers attempting to use CLR features should ensure that they are taking full advantage of the query language, including the extensions in SQL Server 2005. They should look at CLR as an efficient alternative for logic that cannot be expressed declaratively in the query language." (emphasis mine)
The danger is in not understanding what the CLR is for. The biggest problem, IMHO, is that developers make things do things that they weren't designed for. The CLR integration with SQL 2005 is not designed to replace set based interactions with the database, but you can bet that because you CAN replace it, there will be developers that DO replace it. Then they'll wonder why their database performs like crap.
It really falls back into the age old problem of disciplined programming versus expedient programming. If there isn't somebody actively watching out for crappy code, then that's what you'll get.