CLR Integration in SQL Server 2005

I have just finished reading a white paper regarding CLR integration in SQL Server 2005. To me, the integration of the CLR into SQL Server has been the most exciting aspect of the new version so I was glad to see a paper getting into some of the details of its place in the toolbox.

When I reached the section regarding the steps that must be taken to “install” a managed code procedure I couldn’t help but think that it sounded like a lot of manual steps to perform for every assembly. Luckily, Microsoft anticipated this and built functionality to automate much of this though Visual Studio 2005.

Highlights of the paper include:

  • A discussion of when managed code procedures are preferred to T-SQL.
  • Demonstrations of using managed stored procedures to validate column values using regular expressions.
  • Examples of table valued functions that retrieve their value from externally hosted XML documents (such as RSS feeds).

While the paper mentioned that other assemblies may be referenced from the assemblies that are uploaded into SQL Server 2005, a few questions on this subject were left unanswered though. [If these questions have been answered in other papers, I’d love to be directed to them.]

  • Do the referenced assemblies have to be uploaded into SQL Server too?
  • Can the managed code procedure consume Web Services?
  • Can the managed code procedures reference assemblies stored in the GAC?

I truly hope that developers take the time to thoroughly understand when managed code procedures are appropriate. Too often, features are overused simply “because they’re there.” I regularly see people fall into this mindset where they feel that they must use a feature or tool because of the “coolness” of it rather than actually examining if it is the proper thing to do.

A great example of this mindset is found with PowerPoint presentations. How often do people use multi-million color backgrounds or assign a sound to every animation simply for the sake of doing it and because they think that it is “cool” or “neat?”

More often than not, these presentations are unreadable because the text blends into the background. If the eyestrain from trying to read the slides doesn’t give the audience members a collective headache, the constant mechanical keyboard sound certainly will!

What’s worse is that these presenters typically seem to be completely oblivious to the frustration that they have given to their audience. It’s as though the tool has made them forget that their purpose is to present information. Additionally, these presenters do not seem to realize that the audience has learned little from the presentation because they have gotten lost in the distractions created by the presenter.