isnull( ) vs coalesce( ) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

isnull( ) vs coalesce( )

Author

Topic

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-07 : 07:03:15

What is the basic difference between isnull( ) function and coalesce ( ) as both are used to replace null values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 07:10:45

you can use more than one parameters for coalesce(). it always returns first non null value. isnull takes only two parameters and replaces the first columns null value with second value.
Also isnull() changes return datatype to datatype of first expression while coalesce is just like case when. see below
as an example

declare @v1 varchar(1),@v2 varchar(11)
set @v2='test'
select isnull(@v1,@v2),coalesce(@v1,@v2)

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-07 : 07:22:47

thanx visakh for such a quick response

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 07:25:33

No problem

You're welcome

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 07:26:29

see this also

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/10/04/isnull-or-coalesce.aspx

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-07 : 07:31:37

Through this blog post i also got that ISNULL( ) is faster then coalesce ()

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-07 : 11:37:50

COALESCE is ansi compliant and ISNULL is not.

Aditionally, I'm irritated at the naming/fuctionality of ISNULL as compared to ISDATE or ISNUMERIC. But, enough of my personal issues. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources