Which is better (Case, IsNull, COALESCE)

Question

Post reply

Which is better (Case, IsNull, COALESCE)

drbob

Old Hand

Points: 378
More actions
November 19, 2013 at 4:55 pm

#286443

1. Case When mycolumn IS Null Then 0 Else mycolumn END As mycolumn
OR
2. IsNull(mycolumn,0) As mycolumn
OR
3. COALESCE(mycolumn,0) As mycolumn
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

In this case, I would plump for 2.

Reason 1 : #1 and #3 are the same behind the scenes, but #3 is shorter and more readable, so eliminate #1

Reason 2 : ISNULL will convert your default value of zero to whatever datatype myColumn has, which in this case would be preferable.

Reason 3 : COALESCE can evaluate the expressions being coalesced more than once, which can lead to unexpected results.

Reason 4 : you are only handling one expression and a default value for null, so you don't need coalesce.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

drbob Old Hand Points: 378 More actions · Answer 2

Thanks..

So Do I understand correctly that coalesce would be best in a situation like this>

COALESCE(myalpacolumn,mynumcolumn,mybinarycolumn,'') As mycolumn

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

I didn't say that 😉

...but yes, generally I would advocate COALESCE for that use.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Remember to have in mind the data type that ISNULL and COALESCE return.

ISNULL will try to convert the second expression to the data type of the first expression.

COALESCE will convert all expressions to the expression with the highest data type precedence.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2