The CLR in SQL Server 2005
-
I don't like, don't want to do it, but in this case I'll make an exception.
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?
Steve Jones
-
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.
Bill.
-
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?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
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),
Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
-
I have, for the last 7 years worked almost exclusively with SQL Server and Transact-SQL. I've coded so many stored procedures that I really stumble when I fool around with row-based processing in other languages. That being said, I believe that the integration of the CLR into SQL Server is not inherently a bad thing. I do agree that we will certainly see examples of perceived database performance decline as developers mistakenly use C# or VB.net for data access but that is not the fault of the database system. I am always on the side of potential benefit. Controlling the proper use of the database is a management issue, not a technical one.
My impression is that Microsoft, not having an application server product, is adding app server functionality to SQL Server. In the current issue of Queue Magazine from the ACM, Jim Grey makes this point although he does not mention SQL Server by name. I belive that we will discover benefits to having the CLR embedded in the DB Engine even though the benefits may not yet be apparent.
-
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.
Cheers
-
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.
Bob
SuccessWare Software -
WELL said
Cheers
-
It's interesting to note that CLR integration does NOT necessarily "[move] things from a set based model to a row by row based, procedural model." Indeed, I'm wondering how many people who make that claim have actually coded a SQL CLR stored procedure?
So here it is: You STILL NEED TO USE T-SQL. None of the set-based semantics change. What changes is that you can more easily integrate other types of data sources, e.g. web services or flat files (note, I don't think this is a good idea in 99.9999% of cases -- I don't like the idea of SQL Server waiting on a web service). But to access data in tables, you will use T-SQL. You simply can't code data access in pure C# or VB.NET. This is an urban legend from MS marketing that's gone far enough!
But CLR stored procedures are just one small, and in my opinion fairly useless, part. The interesting stuff is CLR UDFs, CLR UDTs, and CLR Aggregates. None of those (except perhaps aggregates) have ANY affect on the "set based model", and indeed aggregates will help us to create better set-based solutions to difficult problems that require cursors or undefined operations (read: aggregate concatenation) today.
UDTs are a really great extension -- we can finally define types that actually behave as they're supposed to. How do you currently store a phone number? And what happens when you do PhoneNumber + PhoneNumber? What does that really MEAN anyway? UDTs eliminate these issues quite well.
And UDFs, while only slightly more useful than user-defined stored procedures, are also a good extension for those rare times when the CLR just does something better than TSQL can -- e.g. heavy mathematical operations. These are really no different than TSQL 'native' UDFs from a set-based point of view -- do you object to those?
MS marketing has done a great job of telling everyone how T-SQL will go away (false) and has failed to highlight the really useful features that CLR integration provides... Now every developer wants to go write a bunch of CLR stored procedures and has no idea where the good stuff is. Great job, MS!
--
Adam Machanic
whoisactive -
They should look at CLR as an efficient alternative for logic that cannot be expressed declaratively in the query language
...and of course, each developer will rather use a language that requires a different mindset than to use the procedural tools he is used to when he is under time pressure?
Gimme a break!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
My issue is that CLR (MFC with a fancy badge on it) allows programmers immense powers within my databases but I am not convinced that there is the corresponding corporate responsibility to act as a check on that power.
If a developer comes to me and says review my SQL code, this is what I am trying to achieve then I can mentor them and tune their queries until they are humming along like Lance Armstrongs legs.
If people can code in whatever they want then there has to be the business discipline in place to say "thou shalt code to the corporate standard". It is bad enough trying to debug other peoples code but can you imagine trying to debug a whole plethora of languages?
I have been a big fan of .NET and the CLR since I first paid my own cash for a 13 week weekend training course. In the hands of a craftsman it allows a VB.NET programmer to do things that a VB6 programmer can only dream of, even with Dan Applemans "Programming the Win32 API".
Bad developers already produce extremely shaky code that only holds up for the short time until they become project managers. I expect .NET to accentuate the positive and exagerate the negative.
-
David,
DBAs can't continue to be just database geeks any longer. The DBA now has to be able to fill the role of .NET mentor and architect. See this not as a threat, but as an opportunity to broaden your skill set -- and be worth even MORE to the organizations that hire you.
--
Adam Machanic
whoisactive -
No one has yet touched on something very basic. If we noe have .NET, C# and whatever else in the database, where are the runtime environments (exe's, dll's, etc) stored at ? My guess is that alot of this 'stuff' needed to enable the various CLR languages/components needs to be installed on the SQL Server !
What ever happened to the SQL 101 adage: "A SQL Server is a dedicated machine. Dedicated to only SQL Server"
Another point in dealing with business rules and such not touched on is: "2-tiered architecture vs 3-tiered architecture"
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Good point.
I'm envisioning nightmare scenarios where unware programmers push everything into the CLR and drag the server down into a quagmire of processing irrelevant requests. I'm sure some 'wizard' will find a way of seamlessly shoving client side windows events and mouse clicks into a shared table so they can 'collaborate' with other clients. Then some hapless DBA will have to explain to the CIO why the database crashed right in the middle of their big sales drive and quarterly reporting.
Bwhahahahahaha
Bob
SuccessWare Software
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply