Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase 123»»»

The CLR in SQL Server 2005 Expand / Collapse
Posted Monday, March 28, 2005 4:04 PM



Group: Administrators
Last Login: Yesterday @ 9:28 PM
Points: 34,372, Visits: 18,590
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

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #170448
Posted Tuesday, March 29, 2005 12:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 29, 2008 5:10 AM
Points: 78, Visits: 43

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.


Post #170504
Posted Tuesday, March 29, 2005 1:04 AM



Group: General Forum Members
Last Login: Friday, January 8, 2016 1:54 AM
Points: 5,957, Visits: 289

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
My blog:
Post #170507
Posted Tuesday, March 29, 2005 4:10 AM

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 25, 2016 11:39 AM
Points: 393, Visits: 1,092

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 Leonard
Data Philosopher, Enterprise Data & Analytics
Post #170534
Posted Tuesday, March 29, 2005 5:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 6, 2016 8:42 AM
Points: 8, Visits: 243
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 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.

Post #170546
Posted Tuesday, March 29, 2005 7:08 AM


Group: General Forum Members
Last Login: Thursday, February 16, 2012 10:55 AM
Points: 18, Visits: 4

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.

Post #170568
Posted Tuesday, March 29, 2005 7:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 3, 2016 8:21 AM
Points: 59, Visits: 154

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.

Post #170572
Posted Tuesday, March 29, 2005 9:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 2:05 PM
Points: 308, Visits: 55

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.


SuccessWare Software
Post #170604
Posted Tuesday, March 29, 2005 10:38 AM


Group: General Forum Members
Last Login: Thursday, February 16, 2012 10:55 AM
Points: 18, Visits: 4

WELL said



Post #170622
Posted Tuesday, March 29, 2005 12:36 PM

Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, May 3, 2015 2:03 PM
Points: 1,113, Visits: 713
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
SQL Server MVP THE SQL Server Blog Spot on the Web
Post #170649
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse