In discussions with various people recently, there's been some discussion about what people are doing with the CLR, as in SQL CLR, and how it best should be applied to database problems. There was a survey at the MVP Summit recently in Redmond and the vast majority of the 70-80 SQL Server people in the room said they were not using the CLR in their applications.
Obviously the followup question is then "why not?" There were various reasons given, but most of them didn't really resonate with me. As I listened to the various answers being given, there was one main thing that struck me about the CLR.
My feeling is that people do not think about using the CLR because it's not on their mind.
There's an old saying that when the only tool you have is a hammer, then everything begins to look like a nail. This implies that you use the tools you have, but I think it applies here as well, but in a different way. We have multiple tools now to solve many problems: T-SQL, SQLCLR, Service Broker, SSIS, etc. All of these are on the SQL Server platform and they're available, but it seems that adoption of these new features isn't happening rapidly.
Or at least the new ones that are their own subsystem. The T-SQL enhancements that have come about in 2005 (TRY..CATCH, PIVOT, CROSS APPLY, CTEs, etc.) are adopted pretty widely and I see lots of writing and work being done with those features. But I see very little with the SQLCLR subsystem.
Most of us are comfortable with T-SQL, and we're happy to pick up the extensions and we can rapidly see where they are handy to use. However the SQLCLR is a completely different environment. Most DBAs don't have a good feeling or even a good set of references to determine when they should use the CLR.
I asked a few people at Microsoft about use-cases or problems that are solved with the CLR, but they didn't have a comprehensive set of examples that soeone could look at. The few examples that I've seen published are fairly trivial, or are solving very complex problems. In both cases, however, I don't see enough examples to allow me to be think laterally about where to use the CLR capabilities of SQL Server.
In some sense this seems like a feature that was built to match something in Oracle, but I can't help but think that there might be better ways in which the CLR could be used. I just think that Microsoft and authors should be giving us many more examples that we can reference and use to give us ideas about how to use the CLR.
Steve Jones
The Voice of the DBA Podcasts
The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.
or now on iTunes!
- Windows Media Podcast - 27.3MB WMV
- iPod Video Podcast - 22.8MB MP4
- MP3 Audio Podcast - 4.6MB
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.