If LINQ to SQL Is Good, Is SQLCLR Still Evil?

I just got off the phone with a customer, a huge telco, that is in the process of rearchitecting some of their solutions.  They had some great questions about using technologies like SQLCLR as part of their architecture.

When SQL Server 2005 (it was code-named "Yukon" back then) was still in beta, customers couldn't seem to get enough information about SQLCLR and to see demos.  Since then, not so much.  Part of the reason that the discussions tapered off is that there was so much discussion early on that focused on scenarios when not to use this feature.  In fact, while preparing and researching prior to my phone call today, I did quite a bit of searching to find guidance that points out scenarios when to use SQLCLR integration. I could easily find arguments against SQLCLR, but I had a pretty hard time finding arguments in its favor.  Most of what has been written about it was written about 2 years ago.


Consider this piece from eWeek: DBAs Bar Door Against Big Bad .NET Wolf.  The author seems pretty blunt about how she thinks every DBA in the world views SQLCLR.  She asserts that all DBAs seem to think that CLR integration is a bad thing, or at least thought so at the time.  Most of the comments that support that argument seem to try to lump every DBA into a buncket and lump every developer into another bucket... those who get SQL and those who don't. The argument continues that if you don't know the CLR deeply, then you shouldn't use the feature.  Sure, it could be an incredibly useful feature, but it's a feature that you should only use it if you really understand what you are doing. 

When the CLR team visited Atlanta, they talked at length about the opportunities for CLR in SQL Server, but with the strongly worded advice that you shouldn't run out and move all of your T-SQL to managed code.  Continue to use set based operations in T-SQL and only leverage SQLCLR in certain instances. 

In his article, The First Rule of CLR Stored Procedures, DonXML gives the guidance:

If you would not have built it as an extended stored proc in SQL Server 2000, odds are that you don’t need to create a CLR Stored Proc in SQL Server 2005.

[via http://donxml.com/allthingstechie/archive/2005/08/30/2158.aspx

Another fried, Kent Tegels (who knows way more about SQL Server than I could possibly dream of) didn't really agree with Don's advice.  Kent offers in a rebuttal post:

If a Stored Procedure can be written in T-SQL, exhaustively try that first. If you find that its too slow or your organization lacks the ability to sustain it going forward, then consider using a SQLCLR Stored Procedure. Test and compare performance carefully, document well.

[via http://sqljunkies.com/WebLog/ktegels/archive/2005/08/31/16581.aspx]

Both Kent and Don invoke the name of yet another person who has way more SQL Server knowledge than can be humanly healthy:

Kimberly Tripp has stated (and I agree with her) that the 2 most abused features of SQL Server 2005 will be CLR Stored Procs, and XML data types 

Looking forward a few years, it turns out that CLR Stored Procs and XML data types aren't the most abused features because, well, they're more likely the most hardly used features.  At least, that's the case in my customers' data centers.  Their databases look just like they did 6 years ago.  Why?

Look at how everyone talks about this feature.  "Only use it if you know what you're doing."  "Stay away unless you have a good reason."  "Consider it a last resort."

If I were researching this feature for the first time, I would be scared to use it.  Nearly every article on SQLCLR starts out with "here's when not to use it" followed by a string of examples that show how easy it is to use.  Great, an easy to use feature that the author basically just told me to avoid.  Everyone who deeply understands SQL (Tripp, Demsak, Tegels cited here as good examples) provides nearly the same advice to keep the feature turned off until you have reason to turn it on.  Nearly all of those articles were written 2 years ago... and hardly a word on the technology since.

It's no wonder that the feature is hardly used.  You can't find a blog, article, webcast, or anything ranting about what a cool feature it is without a big, huge, looming warning in bold red letters. 

Lately, the buzz is all about LINQ.  It's incredibly cool, and I have started to get the hang of writing queries with LINQ.  I decided to do some research to see DBA's reactions to LINQ, and I am having quite a hard time seeing anything out there.  In fact, most of what I see is developers writing, "I used to be a DBA, and I think this rocks." 

The question at hand is really about control. 

Here's the early argument against SQLCLR:  If you are in charge of the database, you need control over everything.  You need to understand everything.  Since you don't understand each and every last nuance of SQLCLR, you shouldn't use it.  You can't maintain something you don't understand.

Yet I don't see the same arguments against LINQ.  In fact, I am seeing a love affair with LINQ.  Is the question not of control, but an invasion of territory?  You put your database in my code!  You put your code in my database!

I'm not politicking in favor of everyone running out and turning on SQLCLR.  But I am curious if it's a technology that got a bum rap from knee-jerk reactions from a few vocal opponents and a less-than warm welcome from its proponents.

I'm not ranting against LINQ, don't think that for a second.  LINQ is really cool, and I was able to quickly reduce the amount of code that I typically write pretty drastically.  I am just glad to see the knees have stopped jerking quite as much.