CLR in SQL

The next version of SQL Server, code-named Yukon and due out later this year, is providing a new capability to expose external code as internally consumable assemblies. Now the basic idea here goes back quite a way. In fact, the ability to execute external code has been around for some time. Evidently most of the developers we have today are just to lazy do the work necessary. This is where Yukon will provide a new capability for the inept to design even more screwed-up solutions than ever before.

Before you decide I'm just one of those T-SQL diehard nutjobs who thinks every problem is solved with set theory, let me set you straight. You would be almost right. ;-) I think SQL Server is more than just a place to store data once the real work is done, but I know and appreciate the value of sequential processing too. Actually the primary reason for most of the disagreements I see today stem from having different developers trained different ways. Or not at all.

The armchair developer (not really engineers anymore) knows how to write code perhaps from his C or C++ days, if we are lucky. If not, they grew up in the VB era and have drag-n-dropped and intellisensed their way into passing themselves off as a engineer. The see a database as a place to store stuff and get it back again so they can provide the real functionality to their users in pretty forms and pages. If they have to, they've been forced to read and write SQL as a means to control the database interface. Secretly, they long to write all their code in C#/VB/Perl because sets are too complicated for them and survive soley by the use of nifty tools in their IDE. All of this is aimed at dumbing down the development process so they can point and click their way to a solution without all that pesky thinking, planning, and design work.

The database developer has been trained in using sets to solve complicated data retrieval and formulation problems. They know the concepts and the practicalities of normalization. They understand how and why business rules are implemented in a database. They see everything outside of a database as just another way to publish the information they so tightly guard, manage and provide. To them the database is the application, and the components, forms and pages that utilize their interfaces are just consumers not to be trusted.

The architect facilitates both these types getting along and pushes the business needs into the solution so that both viewpoints are equally respected. The architect ensures that decisions in both camps support the business requirements first and foremost, and that standard designs and implementations are utilized to realize the architecture.

Back to my CLR in SQL Server topic, being able to write managed code within SQL Server is a vey risky proposition. Not in a technical sense, although there are certainly aspects of this which are important. Mostly the risk here is from an architecture and design standpoint. Having been an MSFT insider responsible for making the development easier to become mired in, I certainly understand the rationale behind this move. Not much different than putting VBA inside the Office tools. It let any goofus become a developer, scripting their way to solutions in whatever crack-pot way they choose. End result, more Office sold. Make it so that you don't even have to be a developer to point and click your way through forms (ala VB) and suddenly you have millions of unwashed masses thinking they can develop solutions. The same strategy works to drive SQL Server adoption. Make it easier for the untrained to write their managed language of choice and install it right on the database server. End result, more SQL Server sold. It's a good strategy with the unfortunate side-effect of further weakening the level of engineering discipline we'll see in solutions going forward.

On the other hand, it's a nice way to seperate the trained and experienced from the untrained and naive. Whenever I come across someone so excited to be able to write stored procedures as managed C# code I know we are dealing with someone who is probably more talk than ability.

All this is not to say that I think the ability to access external assemblies isn't a good thing. It certainly can be. I myself see several reasons why you might choose to host code within an external assembly. They however have little overlap with the typical reasons people want to do this. There are two key reasons I typically come across A) speed and B) external interfaces. The serious computations requiring the speed aspect are not that common and in cases where they are complex, but not mathmatical it's probably better to lay them out in procedural code anyway. Accessing external interfaces like StringBuilder, WebDAV, or XML libraries makes terrific sense. It is simply rare that we see a design that would make use of these directly from within SQL Server.

Or maybe we'll see the usage of those patterns increase because of this new feature? Perhaps this feature is exactly what we need to bring SQL Server to the epicenter of architecture? I guess we'll see.