See the full index.
For some, the answer is easy. Since Tibor reminded me several months ago that
<> == standard and
!= <> standard (and yes, I used that notation on purpose), I have been making a conscious effort to use only
<> going forward. Similarly, I have tried – with varying degrees of success – to avoid
GETDATE() in favor of
CURRENT_TIMESTAMP. (Of course this doesn't work in most of my systems where we use UTC, and occasionally get bitten by some sys admin accidentally switching a server to EST/EDT or to observe daylight savings time under GMT — so there I always use
GETUTCDATE()). While I have no intention of going back into old code just to change it, I have been making little updates here and there when I have been in there for other reasons…
In general, I am not immediately concerned about portability. Companies don't just change their database platforms overnight, and when they do decide to move, it's going to require a lot of changes. Certainly code is easier to change than data types and table structure, but little syntax things like this are still going to remain a small part of the puzzle for the majority of database applications I have observed.
I have no problem using identity columns (sorry Celko),
UPDATE FROM (sorry Hugo), and little things like
NEWID() (I have no idea who to apologize to here). These offer me things that the standard can't… for example, in order to convert most of my
UPDATE FROM statements to standards-compliant
UPDATE statements, I would need to use a cursor instead of a set-based solution, and in several situations the performance would not be acceptable.
I also love some of the new syntax that is not supported elsewhere (e.g.
DECLARE @foo INT = 5;), and will not be afraid to use these when we move to SQL Server 2008. On the other end, there's
MERGE, which follows the standards but has many issues.
Others have expressed opposition to anything that deviates from the standard, but I believe that if the standard is limiting, and a vendor introduces something that makes our lives easier, why should I avoid it based on principle? Those concerned with portability can just make it a priority to avoid non-standard syntax… however, in any vendor implementation, all but the simplest database applications will undoubtedly have to deviate from the standard to some degree. In fact, I would wager that it is impossible to develop a real-world application of any complexity whatsoever, on a popular RDBMS (meaning Oracle, MySQL, SQL Server, DB2, etc), and adhere solely to ANSI-92. Part of the problem is that no vendor is 100% compliant, but even more so, all vendors have attractive and tempting alternatives that improve development, maintenance, performance, or all three.
Are there Microsoft deviations that don't make sense? Of course. The prime example that comes to mind is that Microsoft's implementation of TOP is horrible. And because they have stale tools that would take too much effort to change / replace, they are *still* inadvertently force-feeding this mythical idea that in order to save a view definition with an
ORDER BY clause, all you need to do is add
TOP 100 PERCENT to the
SELECT list. And people are still surprised / angry that selecting from such a view without an order by clause on the outer query does not always return the rows in the order they expect. The
OVER() clause is a better alternative to
TOP in certain scenarios, but it is not complete (and won't be complete in SQL Server 2008, either).
We even start people off with the general idea that following the standard is not important. Look at MS Access, the starter database for most people in the Microsoft realm. It has very little ANSI compliance, and supports all kinds of Microsoft-specific extensions like
FIRST(), VBA, macros, etc. And when moving to SQL Server, a database platform made by the same vendor, the level of effort required will often exceed that of a SQL Server -> Oracle or MySQL -> DB2 conversion. So it is not surprising to me that people generally have little concern for adhering to the standards, not just because in reality they rarely *need* to do so, but also because they've been "brought up" that way, so to speak.
Yes, best practice should dictate to follow the standard. But often it just doesn't seem worth it.
Anyway, back to the title. I am using
<> now, to help ease the pain down the road, in the odd event that I write something that lasts longer than the company's commitment to the Microsoft platform. Though, deep down, I prefer
!= … mostly because it seems less Mickey Mouse-ish. And by Mickey Mouse, I mean, of course, pre-.NET Visual Basic. 🙂
See the full index.