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.