See the full index.
For some, the answer is easy. Since Tibor reminded me several months ago that <> adheres to the standard and != does not, 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 RAND() and 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.
There's a minor typo in your second sentence, but your point once understood did make me smile. (What typo? The equals operator in ANSI SQL is simply "=". I had to look that up, so no "gotcha" is implied.)
Regarding VB, it was clear enough that you did mean, quote, "pre-.NET Visual Basic", end quote. And truly, antique VB does seem Mickey Mouse as compared to C#. What doesn't? However, in order not to have offended those who make a living from VB, perhaps you should have followed up with a well-timed jab at FileMaker….
The comments introduced a certain amout of disinformation about VB. The current version of VB.NET is every bit as powerful as C#, and does include (for example) operator overloading. A person who wanted more power in a .NET language would need to look a little bit further than C#, perhaps to C++/CLI.
But back to VB! Powerful, verbose, supports the <> operator, makes you do funny things with underscores, what an exquisite language! Truly, VB is excellent preparation for COBOL.NET.
As a VBA developer for 12 years, I trust that I'm allowed to poke a little fun at this family of languages. I only regret that in VB 2005 the Wend keyword was replaced with End While, which although more verbose is less opaque.
Hi Ken,
Well, x NOT IN (a,b,c) essentially expands to:
WHERE x <> a
AND x <> b
AND x <> c
Of course, if any of these comparisons yields false or unknown, the set as a whole cannot return true. I am sure you are aware that the common workaround is to use NOT EXISTS, or to make sure that before the NOT IN list gets materialized, you have already filtered out the NULL values.
Good article, and I had no problem with the snippy comment at the end. (Probably because I don't like any flavor of vb even when the code is so useful at times.)
The concept of NULL comparisons is difficult to grasp especially with IN vs. NOT IN. OK, not putting in rows where the left field is NULL in both cases made sense, but having a single NULL value on the right killing every row returned… I’ve come to terms with that is what happens, it still boggles my mind that is what should happen.
Please don't explain the concept of unknown. No matter how many times I hear it, I will still choose to believe it is wrong in "NOT IN" logic. 🙂
"I have no problem using IDENTITY (sorry Celko), UPDATE FROM (sorry Hugo), and little things like RAND() and NEWID() (I have no idea who to apologize to here). These offer me things that the standard can't… "
The very reason I like to use Indexes (apologies to Celko) 🙂
As iterative programming is almost always faster than object / .net code even in the hands of the novice iterative programmer as compared to the guru oop types, I also found the article great until the last sentence.
Working with Date types is always Goofy when crossing db apps from db apps let alone differing systems. But the handy time alternative reference, I will certianly remember should I be interfacing with different systems. Problems where DST varies are certianly issues across global apps, as where time server sources vary. GPS sources seem the most unifying, and should probably be sourced over internet based services.
I heart t-sql, and personally feel the glory of the product was and is MS 2k. The current trend of most vendors, ms and oracle in particular is the move away from the small team builds and heading toward larger scale productions. ADAM and such gpo / AD integration tools have a long road to till they show business improving features and thereby make the applications they could be introduced into more salable and thereby profit making ventures.
mySql and php only need to clean up the programming language and oddly 'sell' a product to take over the market completely. Since Sql 2k is pretty much non-existant anymore in 2k5 and 2k8, they could convert mySql into MS2k and I'd probably switch.
And finally humour it seems, for some is also a long road.
Aaron,
Unfortunately I do not know why != is not part of the standard.
Hugo,
I agree with you – let me use unknown instead of false.
A <= B is true means A<B is true OR A=B is true
A >= B is true means A>B is true OR A=B is true
A <> B is true means A<B is true OR A>B is true,
but only if you can compare A and B. Unfortunately, there are cases when you can say that two entities are not equal, but comparisons make little or no sense at all. Consider your avatar vs. Denis's one – clearly they are not the same, but which is one is greater than another?
In general, in some cases it is easier to determine if two entities are identical than to determine which one greater. Makes sense? If you ever did some object-oriented programming, you might have defined some custom comparisons for your objects and see for yourself.
Alex: I fail to see your point. A <> B does mean A < B or A > B, so it is similar to <= and >=. You are muddying the waters by bringing in a NULL value that wasn't there in the <= and >= examples.
Also, "A != NULL means that (A is equal to NULL) is false" is an incorrect statement. Any comparison involving NULL can only be 'unknown'. I could explain this in detail, but I have already done that in a series of blog posts a few months back so I'll just advise you to hop over to check out http://sqlblog.com/blogs/hugo_kornelis/archive/tags/NULL/default.aspx.
Aaron: "I have no problem using (…) UPDATE FROM (sorry Hugo)" – no need to apologize, mate! In all situations where it will give me a performance or maintainability avantage, where I am sure that the undefined behavior can't bite me, and where there is no view with in instead of trigger interfering, I have used it as well. And I will continue to do so, until I can switch to SQL Server 2008 and use MERGE instead. 🙂
Alex,
Then T-SQL maybe should also include ==
Anyway, like most people don't know the actual definition or origin of the word COALESCE, an operator does not have to be completely intuitive or obvious for users to employ it effectively and understand what it means without dissecting it. I agree that != makes more sense and is more common, but at the same time, why isn't it part of the standard?
Aaron,
I think <> notation is inconsistent and misleading for programmers. Come think of it,
A <= B means A<B OR A=B
A >= B means A>B OR A=B
but
A <> NULL does not mean that either A<NULL or A>NULL – both are false. Of course, there are many other inconsistencies in T-SQL. On the other hand, != is intuitive:
A != NULL means that (A is equal to NULL) is false. Simple, clear, and consistent with commonly used C++ and C#.
Yes, I was only poking fun, I did not think I would hit such a nerve…
Denis,
True, true!
Aaron,
LOL – it is difficult to determine tone in writing, but I thought you were poking fun from the beginning. 🙂
In my experience it's tough to beat a talented developer and their language of choice, regardless of the language. Developing scalable, performing solutions has a lot more to do with what my Mom calls "gumption" and less to do with any software language they prefer.
:{> Andy
That is why they have smiley faces
http://en.wikipedia.org/wiki/Smiley
:*)
I guess these are comments I should reserve for speaking engagements. That way my tone can more clearly indicate that I am joshing, and any retorts can be taken at face value too. 🙂
Aaron,
Heck no, I've been joshin' you since the first comment! In hindsight, I should've put a 😉 after that line.
I think it's funny, really!
:{> Andy
Andy, of course we each have our own preferences. I find it amusing to poke barbs at Visual Basic, especially after what Microsoft did in moving from classic VB to VB.Net. If you don't find it amusing, OK, I'm sorry. <shrug>
"I would rather write in the language I prefer and deploy that, than have to deal with any kind of conversion myself. "
Me too – I prefer VB over C#.
:{> Andy
Sure, they have tools, but no translation tool can be 100% perfect. I would rather write in the language I prefer and deploy that, than have to deal with any kind of conversion myself. This is why I choose not to write in assembly, etc. 🙂
Don't worry, I like C# better myself. Once I saw that they have C# in SSIS 2008 I was a happy camper. This back and forth translation is a pain in the neck (I know, I know they have tools for that)
Ah, I wasn't aware of that. OK, I will try to be more careful when I throw the term around in the future.
>>Denis, I agree when you are talking about VB.Net. But there was a reason I said "Visual Basic"… which is not the same as VB.Net.
This is where MS likes to confuse use because they dropped the .NET from VB.NET a couple of years ago. It is Visual Basic again 🙂
http://msdn2.microsoft.com/en-us/vbasic/default.aspx
Lazy me, still doing ISNULL instead of COALESCE
AND I have a sticky note on my LCD that writes "COALESCE for ISNULL" 🙁
Denis, I agree when you are talking about VB.Net. But there was a reason I said "Visual Basic"… which is not the same as VB.Net.
Now, my programmatic maturity cycle was based solely on VB and VBScript, so it's not that I hate it. I just prefer C#.
Hey, it's my blog, and I can distribute my own sense of humor as I see fit.
Not that I use VB (except in SSIS) but I believe that besides the ability to use operator overloading and unsafe code (this might have changed since .NET 2.0) you can do everything else in VB that you can do in C#. It pretty much compiles to the same Common Intermediate Language
This I always found funny
While
…… 🙂
Wend
Good article until the last sentence. Why insert a discordent note like that?
Yes, I always use COALESCE() and never use ISNULL(). The latter is misnamed (like TIMESTAMP); it should really be IFNULL(). ISNULL() implies a boolean return value.
COALESCE instead of ISNULL 🙂
Nothing is inherently *wrong* with Visual Basic. I just have it as my own impression as kind of a "script kiddie" language when compared to more powerful languages like C#. This is not an insult to Visual Basic developers out there, just a reflection of my own perception.
I do agree that Microsoft is pretty good at adhering to the standards, and like you, I certainly believe that they are not perfect. I do appreciate some of the ways that they deviate from the standards, because in several cases, they make me more productive and/or make my applications more efficient than would be the case otherwise.
Hi Aaron,
What's wrong with Visual Basic?
I'm not sure how Microsoft weighs the ANSI standard. It's obvious they don't ignore it, but they also do not completely adhere to the standard. How do they arrive at the current balance? I don't know. A few times I've asked "Why in the world did you do it that way?" and had the question answered. When that's happened, I learned something.
It turns out I hear from a handful of database professionals and Microsoft hears from a lot more. They base their decisions on their information, and they have access to a lot more information than me. I don't think they're perfect, but I do think they're pretty good.
One of my firstblog posts was Which "Flavor" DBA Are You? (http://sqlblog.com/blogs/andy_leonard/archive/2007/07/10/which-flavor-dba-are-you.aspx) The gist of the post is there are different specialties that go by the job title "DBA."
I think something similar applies here: If an application is optimized to run on Oracle, hire an Oracle database developer for the database work. If SQL Server is the backend, hire a SQL Server database developer. And (this is perhaps most important) if the requirement is a cross-platform database, either hire a database developer that can develop for all the database platforms supported, or hire enough different database developers to cover the desired platforms.
:{> Andy