Keys to the Database

Microsoft is making a big marketing push about the forthcoming CLR features in the next release of SQL Server 2005. To quote: "Using common language runtime (CLR) integration, you can code your stored procedures, functions, and triggers in the .Net Framework language of your choice." (msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsql90/html/sql_ovyukondev.asp)

Okay, I pick COBOL, because 78 percent of the world's running programs are written in it. No CLR for that one? Well, then, let's go over to the old PL/I code that's still lying around some shops. Not fair? Okay, we'll use ADA and get some military work. My point is that saying "the .Net Framework language of your choice" assumes that you have and forever will only write code in a Microsoft language. What happens if you like Java, but not C# (which I still insist on pronouncing "See Octothorpe")?

Let's keep quoting. "Many tasks that were awkward or difficult to perform in Transact-SQL can be better accomplished by using managed code..."

I agree with that. But these aren't database tasks. We used to joke that SQL stood for "Scarcely Qualifies as a Language" because it has no I/O and can't format output. Its math library is limited because it isn't a computational language. It doesn't do text searching, list processing, or graphics. The only purposes of SQL are data management and retrieval. Period.

Adding more and more things leaves you with a kludgy mess that does none of these things well. As Antoine-Marie-Roger de Saint-Exupery said, "Perfection is reached, not when there is no longer anything to add, but when there is no longer anything to take away."

Change Bad!

The next quote from Microsoft is really interesting. "You can now better use the knowledge and skills that you have already acquired to write in-process code."

So much for professional growth in a Microsoft world. "Ogg not like bow and arrow. Ogg not learn bow and arrow. Ogg bash food with rock, like always!" Never mind that the old skills often aren't appropriate for new situations. Going back to my Neanderthal: "Ogg bash lizard with rock, so Ogg bash saber-toothed tiger with rock and eat cat for dinner." And see the possible problems. To a Neanderthal with a rock, everything looks like a lizard.

You're being invited to turn an RDBMS into a file system, to keep writing third-generation code, and not learn declarative programming. If you have trouble with declarative programming, such as writing constraints in the DDL, maybe you ought to get some help until you learn that skill. Putting that kind of access to the insides of a database into the hands of improperly skilled programmers is not a good idea.

There's yet another way to use CLR to screw up your entire enterprise — mixing multiple languages together inside the RDBMS.

Have you ever looked at the differences in the MOD() functions among programming languages? The order of operator precedence? Does the EOF flag go up when you read the last record in a file or when you read past the last record in a file? (Pop quiz: how does a Standard SQL cursor do it?) Do you have pretest or posttest loops? How does your language handle three-valued logic in an IF-THEN-ELSE or switch control construct?

Speak any Microsoft?

When the host languages were outside the RDBMS, you had ANSI/ISO standards that defined those interfaces. But the host language wasn't inside the RDBMS, firing triggers or executing stored procedures in multiple proprietary languages. Now all bets are off. The code will be such a mess that you'll never leave Microsoft or interface with any other data source.

This trade repeats old errors much like the fashion industry (well, okay, my Nehru jacket hasn't yet returned to cool). Destroying tiered architecture is fundamentally wrong. It endangers data integrity and returns us to the worst of file processing.

Sure, Little Johnny with his MS certificate now thinks he's a DBA and has the keys to get inside the RDBMS, but can anyone begin to imagine any advantage to the enterprise?

Joe Celko is an independent consultant in Austin, Texas and the author of Joe Celko's Trees and Hierarchies in SQL for Smarties (Morgan Kaufmann, 2004) and Joe Celko's SQL for Smarties: Advanced SQL Programming (Morgan Kaufmann, 1999).