ISNULL() or COALESCE()?


Both the functions are used to show/assign different value if the parameter is null.

select isnull(null,'test'),coalesce(null,'test')

But there are diiferences between the two

isnull() is sql server specific
The datatype/length of the result depends on the parameter

ex

declare @s varchar(1)
Select isnull(@s,'test')

Note that the result is t and not test
The name isnull sounds that it should return 0 or 1 like other functions isdate and isnumeric

But coalesce has advantages over isnull

It is defined by ANSI standard
It will accept more than one parameters
The result doesnt directly depend on the parameter

ex

declare @s varchar(1)
Select coalesce(@s,'test')

and note that the result is test

Although it is proved that isnull is faster than coalesce here (http://sqljunkies.com/WebLog/amachanic/archive/2004/11/30/5311.aspx), for the above reasons I prefer using coalesce

Published Thursday, October 4, 2007 2:41 PM by Madhivanan
Filed under: , ,

Comments

No Comments