IsNull versus COALESCE

Every day I receive emails from specialist interest groups (Mostly SQL Server) with tips, tricks and articles. One of todays emails mentioned a gotcha with the ISNULL function. I read the rather long winded, slightly repetitive and difficult to follow article, as it contained a very valid point that has been asked of me by past clients. I thought I would give my somewhat shorter answer here.

The question:

“Why use the oddly named ‘COALESCE’ function when ‘ISNULL’ does the same thing and is so much more obvious in meaning when reading the code?”

Consider the following example…

declare @a varchar(100)
declare @b varchar(10)
set @a='1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'
select ISNULL(@b,@a)
select COALESCE(@b,@a)

ISNULL returns ‘1234567890’
COALESCE returns ‘123456789012345678901234567890123456789012
3456789012345678901234567890123456789012345678901234567890’

This is because ISNULL takes the return type from the first argument yet COALESCE returns the data type of expression with the highest data type precedence.

There are other differences between the funtions too such as ISNULL can only have two arguments whereas COALESCE can have several.

It is an interesting gotcha though as if the result is to be displayed or used in an insert to another table then IsNull can return a tructated result.

Its only fair that I include the original article so here it is…

http://www.sqlservercentral.com/articles/T-SQL/76861/

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s