THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

So You Like To Use ANSI SQL...

We all know that CURRENT_TIMESTAMP is ANSI and carries the Celko stamp of approval while GETDATE() is proprietary. So now run the following line of code

 

CREATE TABLE duh2(id DATETIME CONSTRAINT df_duh DEFAULT CURRENT_TIMESTAMP)

No problem right? Let's take a look at what is actually created, run the query below

 

 SELECT object_definition(id) FROM sysobjects WHERE name = 'df_duh'

Interesting, the object definition for the constraint shows getdate() not current_timestamp.

Let's verify that by using sp_help, run the query below and scroll all the way down, look at the constraint_keys column

SP_HELP 'duh2'
 
Same thing,it shows getdate()
 
What happens when you script out the table?
 
/****** Object:  Table [dbo].[duh2]    Script Date: 02/22/2009 20:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[duh2](
    [id] [DATETIME] NULL CONSTRAINT [df_duh]  DEFAULT (GETDATE())
) ON [PRIMARY]'
 
 
As you can see SQL Server changes CURRENT_TIMESTAMP to GETDATE().
 
Is this a bug/feature or something else? What if you use the same table on more than one RDBMS and want to quickly script the table from SQL Server so that you can create it on somewhere else?

 

 

Published Sunday, February 22, 2009 9:52 PM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

aspiringgeek said:

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.

February 22, 2009 9:42 PM
 

Jonathan Kehayias said:

Interesting find Denis.

February 22, 2009 11:27 PM
 

David L. Penton said:

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.

February 22, 2009 11:46 PM
 

Alexander Kuznetsov said:

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?

February 23, 2009 10:31 AM
 

Denis Gobo said:

Alex,

nope won't make it to the summit, have other obligations  :-(

I don't care about ANSI SQL that much, although I do try to confirm to some kind of standard, in other words I don't mix ISNULL, COALESCE and GETDATE() , CURRENT_TIMESTAMP in the same DB

Also some proprietary T-SQL performs better and it is easier to write

February 23, 2009 10:38 AM
 

AaronBertrand said:

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.

February 23, 2009 12:40 PM
 

James Luetkehoelter said:

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.

February 23, 2009 1:38 PM
 

AaronBertrand said:

>>

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).

February 23, 2009 1:47 PM
 

Hugo Kornelis said:

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.....

February 24, 2009 4:24 AM
 

Alexander Kuznetsov said:

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?

February 24, 2009 11:00 AM
 

Alexander Kuznetsov said:

February 24, 2009 6:10 PM
 

James Luetkehoelter said:

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.

February 25, 2009 6:01 PM
 

AaronBertrand said:

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.

February 25, 2009 6:21 PM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement