icrosoft’s impending release of the new capability to write stored procedures in SQL Server is a big advance and technologically admirable, but it raises serious questions about whether such a capability is appropriate. In short, do managed stored procedures increase or solve some of the management problems associated with writing database-driven applications?
Many to One
From a .NET developer’s point of view, the new capability is convenient, because it lets you avoid learning and using T-SQL. With today’s supersized frameworks, having one less language to learn and keep up with is a definite advantage. In addition, because you have full access to the framework’s power, writing .NET code for SQL Server is the equivalent of giving T-SQL modern object-oriented powers, but without having to learn any new syntax.
One to Many
On the other hand, from a database administrator’s point of view, exactly the opposite applies. Many DBAs are SQL and T-SQL experts who can quickly write, review, and spot problems with T-SQL code. But they often aren’t .NET-language experts. Worse, developers will write managed stored procedures in their .NET language of choice. But one DBA may manage a database that many different developers are using to service their applications; therefore, DBAs will need to be able to decipher all the .NET languages used to run code in those applications. Practically speaking, probably 99 percent will use VB.NET or C#, but there’s the possibility of many other languages as well.
Many to Many
From a management point of view, the new capability is bound to cost money, whether that means blocking managed stored procedures and training new developers in T-SQL, or allowing managed stored procedures and training DBAs in one or more .NET languages. Moreover, because the source for managed stored procedures lives in an external assembly, managers who allow developers to use the new capabilities open up yet one more management issue: where to store and how to manage the source code for managed stored procedures. Obviously, Microsoft has put some thought into the problems of maintaining the link between SQL Server and the compiled assemblies, for example, backing up or copying a SQL Server database copies all the linked assemblies by default. But there’s no such connection to the source code. If you’re planning on having developers write complex stored procedures in a .NET language, you should give some thought to how you can manage the source for those procedures, and not just the binaries.
|
A Lock-In Transaction
Even without considering training and management costs, managers may be very reluctant to let developers use CLR-based stored procedures, because allowing even one developer to write them puts you on a one-way path toward SQL Server lock-in. It may be rare, but occasionally companies do change from one back-end database to another. In the past, making such switches has been painful, because the flavors of SQL used in the RDBMS implementations are not identical. But they are similar. It’s far less painful and expensive to move SQL-based stored procedures from SQL Server (T-SQL) to Oracle (PL/SQL) than it will be to rewrite CLR-based stored procedures in PL/SQL?and in fact, it may prove impossible, because developers can (and will) use features in .NET that simply aren’t available in any SQL flavor.
For most developers, such lock-in may not have immediate financial repercussions, but it is lock-in, nonetheless. In my experience, you either use a language or you slowly lose it. This language loss comes not only because you begin nearly immediately to forget the syntax that once tripped so lightly from your fingers, but also because languages aren’t static?they change steadily, and when you’re not paying attention those changes add up. In other words, the advantages of writing all your stored procedures in your favorite .NET language will come at the expense of your fluency with SQL. Should you ever change jobs and need that SQL facility, you’ll have to do some work to get it back and catch up with new features.
All in all, adopting CLR-based stored procedures is much like a transaction; you either adopt it wholeheartedly, committing to SQL Server lock-in for now and pin any future migration hopes on other RDBMSs adding .NET capabilities, or eschew it entirely, deciding that in the long run, it’s not a good business plan.
No Magic Translator
You might think that Microsoft could solve this problem by simply providing a translator that would take in .NET code and spit out equivalent T-SQL, but that isn’t going to happen. There’s no way that T-SQL will evolve to the point where it can express everything you can express in a full .NET language, and even if it did, such SQL wouldn’t be any more portable to other RDBMSs than is CLR code. However, if you decide to go ahead with CLR-based stored procedures in spite of the concerns listed here, there’s no reason you couldn’t go whole hog and write one to do the reverse?translate T-SQL into your favorite .NET language!
It May Not Matter
With the new feature yet to be released, it’s anyone’s guess how popular it will prove to be. People are still hotly debating whether there are any advantages in using stored procedures at all, so there’s likely to be just as much confusion and resistance to putting code inside the database that’s even less likely to be portable.
| Author’s Note: The link also points out the fallacy of the most common reason developers cite for using stored procedures: that they’re pre-compiled in SQL Server while dynamic SQL is not. That’s under debate as well. Microsoft’s SQL Server documentation isn’t consistent. Apparently, in SQL Server versions prior to 2000, stored procedures enjoyed a considerable advantage over dynamic SQL, but here’s one reasonably clear explanation of the current situation. |
Oracle, DB2, and Sybase developers have had the ability to create and run Java stored procedures for several years already, but that capability hasn’t proven nearly as popular as the manufacturers had hoped even though Java code is still far more portable than .NET code. The latter situation may change over time though. For example, DB2 already has a project under way to let .NET stored procedures run in that database much as they will in SQL Server, and it’s at least within the realm of possibility that Oracle will follow suit if .NET continues to gain in popularity. Unfortunately, like Java stored procedures, it’s highly unlikely that all implementations will use the same runtimes or support the same range of features, both of which may impact portability.
Finally, you can’t rule out the possibility of third-party language translators arising to ease platform portability concerns. Because of the high degree of similarity between .NET languages and between C# and Java, several companies provide translators from one language to another. Microsoft provides a Java-to-J# translator. Remotesoft sells the Octopus translator, which translates among several different languages (C# to Java is still being evaluated as of this writing). If there’s a need, a product will evolve to fill it. Of course, that may not happen in time to save you from bearing the burden of translation.
Waffle Anyone?
As you can see, the advantages and disadvantages are stacked up high on both sides of this equation. If all the developers in your company are already using .NET, you’re planning to upgrade to Visual Studio 2005 (Whidbey) and SQL Server 2005 (Yukon), don’t think your company is likely to change databases, and you’re already heavily invested in stored procedures, then writing CLR-based stored procedures is a viable and attractive option. However, if you think you might change databases, or you mix developer languages, or you eschew stored procedures for parameterized SQL, or you don’t want the hassle of managing stored procedure source separately from your database, then you probably want to steer clear of this new technology.
Still, it’s tempting to think of the convenience, for example, of using regular expressions in your queries, or mapping records to objects right inside a stored procedures or…. You see? Have a waffle.
