Welcome to EMC Consulting Blogs Sign in | Join | Help

Claypole's World - The SQL Server Side

Concerning development of bespoke applications and database administration.

Getting rid of Getdate()...

Ok I admit - I use it. Pretty much everyone I know uses it and yet I feel dirty when I do. 

Why? Well it's not portable SQL and most certainly not ANSI standard.  I need to get the datetime I hear you say.  How do I do this without getdate()? 

There is a perfectly good substitute in the form of CURRENT_TIMESTAMP that is ANSI standard and therefore portable.  It's in SQL Server and works exactly the same as getdate().  So why don't we all use it?

Based on my personal experience, my guess is that it is longer to write and more cumbersome to say. However, I don't feel I can excuse myself when, as a result of my actions, the SQL I have written has been tightly bound to SQL Server and it needn't have been the case.

I find I am much more militant about the use of ISNULL() rather than COALESCE().  It's the same deal.  COALESCE() is portable and functionally the same as ISNULL().  So why do so many people use ISNULL()?  I have no idea.  I always use COALESCE() but then I also prefer the word :o).

I don't seriously expect the SQL Server product team to drop these functions; mainly because of the body of legacy code out there. I'd love it if they did - once I had fixed all of my code...

However, I do think we should all make more effort to keep our code portable. What do you think?

Cheers, James

Published Thursday, July 17, 2008 10:32 AM by James.Rowland-Jones

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

 

jamie.thomson said:

The analogy  to ISNULL/COALESCE is ever-so-slightly awry because those 2 can behave different in certain circumstances

http://haacked.com/archive/2005/01/21/difference-between-isnull-and-coalesce.aspx

-Jamie

July 17, 2008 11:30 AM
 

Claypole's World - The SQL Server Side said:

Crikey.  That title sounds like an essay I had to write when I was at school and that was a long

July 18, 2008 10:30 AM
 

James.Rowland-Jones said:

I have decided to put up a new post in response to this feedback Jamie as I wanted to cover something else I found as well.

You can see it here

http://blogs.conchango.com/jamesrowlandjones/archive/2008/07/18/isnull-lt-gt-coalesce-discuss.aspx

July 18, 2008 10:33 AM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About James.Rowland-Jones

James is an Advisory Practice Consultant with EMC Consulting. He works primarily with SQL Server technologies in architecture, development and administration capacities. He also co-ordinates our Internal SQL Server Community.

View James Rowland-Jones's profile on LinkedIn

Powered by Community Server (Personal Edition), by Telligent Systems