How important is T-SQL portability?
This came up on a newsgroup recently. I weighed in with my .02 worth and decided to blog about it as well. Question: how important is it to write T-SQL such that it strictly follows the ANSI/ISO standard and will run on other DBMSs unmodified? My answer on the newsgroup [paraphrased]:
In my experience, most of the noise about writing portable T-SQL is usually just that: noise. Practically speaking, it's very unusual for an app of any complexity not to use some proprietary extensions -- whether it's running on SQL Server or some other RDBMS. It's also very rare for whole apps to run unchanged on multiple DBMSs. Usually, there are functionality or performance trade-offs that come with this least common denominator approach that people are simply not willing to live with.
And a special shout out to you standards zealots out there: I know you believe in standards. So do I. Some of you think that using proprietary extensions makes one a newb. That does not match what I've observed. In my experience, using proprietary extensions is usually a personal preference or a business decision that has little to do with actual SQL skill. Portability is one of many factors that should influence what type of code we write; it's neither the only one, nor the most important one. How important it really is in a specific situation will vary based on the needs of the business.
Bottom line: how important this is depends on how much you need portability. The old adage that you should write portable code because you never know where it might need to run at some point is both impractical and an oversimplification. As with many languages, writing portable T-SQL is not without its downsides. You can very easily end up trading functionality and performance for portability you may never need. The ability to port to other DBMSs with less effort is something you may not care anything about. And you have to be careful lest the whole portability question becomes a self-fulfilling prophecy -- you give up so much performance and functionality in SQL Server in the interest of portability that you end up wanting to switch DBMSs, at which time portability suddenly becomes much more important.
Comments
Anonymous
September 06, 2005
I always think that programming standards are as match about making programmers portable, as they are about making software portable. The fact that all the big RDBMS keep somewhat to the SQL standard means that you can move a programmer from one RDBMS to another without to match retraining.Anonymous
June 01, 2009
PingBack from http://paidsurveyshub.info/story.php?id=76179Anonymous
June 16, 2009
PingBack from http://fixmycrediteasily.info/story.php?id=16102