So You Like To Use ANSI SQL...

Outrageous!  I've been using CURRENT_TIMESTAMP for a few years & introduce it to my customers when appropriate.  It's frustrating to see behind-the-scenes shenanigans such as this.

Interesting find Denis.

I have a hard enough time getting people to use ANSI-92 joins rather than worry about what SQL Server will generate out for a script.  Now, if SQL Server had a "Generate ANSI-Compliant Script" that would be great.  But since it doesn't, I'll let this one pass.  ANSI Joins, COALESCE vs. ISNULL, <> versus != ... I think I would rate those higher than CURRENT_TIMESTAMP but it is still very relevant.

Hi Denis,

I think it is not practical to write ANSI SQL anyway. I wrote it up some time ago:

http://www.devx.com/dbzone/Article/32852

What do you think?

Are you coming over to Seattle?

Same with ROWVERSION and TIMESTAMP.  The former is preferred and the latter is deprecated, but even if you use the former, the latter is what sys.columns points to in sys.types and when you script out the table.  Not so much about ANSI compliance, but still annoying that the engine swaps out what you use with what it [incorrectly] thinks is a better alternative.

I think ANSI is totally irrelevant. This behaviour is an attempt by SQL to be ANSI-compliant. NO PLATFORM IS. And I think encouraging the use of CURRENT_TIMESTAMP in SQL is very dangerous (or any platform), because TIMESTAMP means different things on different engines.

If ANSI is going to have any relevance in the practical database world, vendors must embrace it, not say "ANSI-compliant plus additions". I really believe it ANSI has no real bearing on practical usage, because vendors have no faith in embracing it as a true standard. It's a suggestion, not a standard. I do think that's sad, though.

>>

And I think encouraging the use of CURRENT_TIMESTAMP in SQL is very dangerous (or any platform), because TIMESTAMP means different things on different engines.

>>

James, SQL Server has it wrong here. "CURRENT_TIMESTAMP" is not the danger at all.  Using TIMESTAMP instead of ROWVERSION is what causes the confusion.  Hopefully MS will correct this (someday).

Alex,

Thanks for the link to your article. I was unable to leave comments there, so I'll do it here (because the comments are relevant here as well).

Your first point, NULLS in indexes, is slightly incorrect. ANSI does not presscribe anything for indexes. But for UNIQUE constraints, your observation is correct. SQL Server is to be blamed here; ANSI clearly states that a UNIQUE constraint applies to non-NULL values only, but Microsoft chose to neglect that. See https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299229.

The scond point is the other way around. Oracle messed up when they decided to use the same internal representation for storing empty strings and NULLS. I believe they later introduced the "varchar2" datatype where this mistake has been fixed, but they have so far not changed the non-standard behaviour of "varchar".

The collation issues are not really related. You just show that SQL Server and Oracle have different defaults. Override the defaults to explicitly chooose a collation, and both RDBMS's should behave the same. I don't see this as a portability issue. (I hope that nobody believes that you can turn off your brain when porting...).

The same goes for the issue you describe as "intermttent bugs" - you even state yourself that SQL Server 2005 with SNAPSHOT isolation is compatible in this regard with Oracle's default isolation. I don't know Oracle, but I assume (and hope) that Oracle users can override their default isolation level too, to get something that is compatible with the READ COMMITTED isolation level.

Oh, and the fix for the bug (at least on SQL Server 2005 with SNAPSHOT isolation) is to use a table hint to override the default isolation level and use READ COMMITTED instead for the table in the subquery.

Bottom line: Yes, there are problems with portability. The main two reasons are:

1) Developers using vendor-specific extensions. They all have to be found (if not documented when implemented) and worked around before you can even begin to try porting.

2) RDBMS vendors deviating from or not implementing parts of the standard. Especially the situations where a vendor chooses to spit in the face of the standard are dangerous, because these are often not documented well enough, so you'll probably only find out during testing after porting the code. Or in production, if the test didn't cover that situation.....

Hi Hugo,

Basically in one case you are suggesting to use a different datatype (varchar2), in another case you are recommending hints or explicitly specifying a collation.

All of that IMO just reiterates my point: you cannot expect identical SQL to provide identical results on different RDBMS. You must write different SQL anyway, so what is the point of trying to stick to ANSI standard?

Hi Hugo,

I came up with a more detailed reply, as follows:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/02/24/writing-ansi-standard-sql-is-not-practical.aspx

Actually Aaron, I disagree with you. SQL Server doesn't have it wrong. Nor does MySQL. Or PostgreSQL. Or DB2, or Oracle. My point is that ANSI is a standard that IS NOT A STANDARD. Yes, the confusion in our world is timestamp vs rowversion in terms of functionality. That's not my point. I guess I'm just shaking my fist at both ANSI and the makers of RDBMSs. If you put out a standard and no one wants follows it, stop talking. If you make a product and want to try to abide by a standard, THEN DO IT.

At this point I don't see the relevence of ANSI - vendors do what they want regardless. Who's fault it is, I'm not sure. Perhaps both sides. Who suffers? Us in the middle trying to help people understand a platform and get people to use it consistently...

Perhaps that's just me being naive.

James you started by saying that SQL was trying to be ANSI compliant. If they were, they wouldn't have pulled the name Timestamp out of their hind quarters (which no other engine did) and instead would have used rowversion. Even if they weren't striving for compliance, they still might and very well could have picked a different non-ANSI keyword that made sense.  Warning against current_timestamp is the wrong answer to whatever your underlying point is.  And I guess we will just have to agree to disagree.