Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

An interesting thing about isnull Expand / Collapse
Author
Message
Posted Tuesday, March 30, 2010 8:12 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 18, 2010 8:24 AM
Points: 12, Visits: 85
Comments posted to this topic are about the item An interesting thing about isnull
Post #893447
Posted Tuesday, March 30, 2010 8:20 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:19 PM
Points: 605, Visits: 1,691
This QotD raises a point I'm always having to remind developers about, and I guess SQL guys can also do with a reminder from time to time.

However, my 2 cents is rather than...
declare @s char(4)
select ISNULL(convert(varchar(16),@s),'Unknown')


...I would use...
declare @s char(4)
select coalesce(@s,'Unknown')


...which also has the ability to take more than 2 arguments.

S.
Post #893448
Posted Tuesday, March 30, 2010 9:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 21,755, Visits: 15,457
Nice question



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #893459
Posted Tuesday, March 30, 2010 10:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 21,755, Visits: 15,457
Fal (3/30/2010)
This QotD raises a point I'm always having to remind developers about, and I guess SQL guys can also do with a reminder from time to time.

However, my 2 cents is rather than...
declare @s char(4)
select ISNULL(convert(varchar(16),@s),'Unknown')


...I would use...
declare @s char(4)
select coalesce(@s,'Unknown')


...which also has the ability to take more than 2 arguments.

S.


I, too, prefer the coalesce for this same reason. However, you will find that many prefer the isnull due to an increase in performance.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #893460
Posted Tuesday, March 30, 2010 11:06 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 18, 2010 8:24 AM
Points: 12, Visits: 85
It's also interesting that coalesce behaves a different way from isnull.

My point in giving the isnull/convert formulation was to show how to make isnull behave in the way you might expect. I wasn't particularly recommending it.

Can anyone give an account of why the semantics of isnull and coalesce (with two args) is different?
Post #893481
Posted Wednesday, March 31, 2010 12:02 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
richard.maw (3/30/2010)
Can anyone give an account of why the semantics of isnull and coalesce (with two args) is different?

Here is a nice article about this: http://blogs.msdn.com/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx
COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine.
...
COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL function
Post #893491
Posted Wednesday, March 31, 2010 2:35 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 2:46 AM
Points: 3,404, Visits: 1,745
CirquedeSQLeil (3/30/2010)
However, you will find that many prefer the isnull due to an increase in performance.


Do you have a supporting link or further information on the performance difference between ISNULL and COALESCE? I was unaware of any significant difference between the two?


-----
James Lean
@jamesleansql
Post #893541
Posted Wednesday, March 31, 2010 2:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:36 PM
Points: 6,002, Visits: 8,267
James Lean (3/31/2010)
CirquedeSQLeil (3/30/2010)
However, you will find that many prefer the isnull due to an increase in performance.


Do you have a supporting link or further information on the performance difference between ISNULL and COALESCE? I was unaware of any significant difference between the two?
COALESCE is implemented as a quite literal implementation of its definition in the ANSI standard (which is: COALESCE(a, b, ...) is equivalent to CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ... ELSE NULL END).

So if any of the arguments is a subquery, the implementation if COALESCE((subquery), ...) becomes CASE WHEN (subquery) IS NOT NULL THEN (subquery) ... ELSE NULL END. And the optimizer that sits a bit further down the line does apparently not realize that the two subqueries are identical, so it happily produces a plan that will evaluate the same subquery twice.

Without the subqueries, I have never witnessed a measurable performance difference.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #893549
Posted Wednesday, March 31, 2010 3:02 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 2:46 AM
Points: 3,404, Visits: 1,745
Thanks Hugo. That would seem to be a very specific case, I wouldn't have thought it was a good reason to make a general case for using COALESCE over ISNULL.

Personally I do tend to use COALESCE, even with only two arguments. That way, if you do need to add more arguments in future you don't have to worry about changing it. Unless I do need to take advantage of the implicit CASTing to the first argument's type, in which case I'll use ISNULL.

As always, I suppose, "it depends".


-----
James Lean
@jamesleansql
Post #893560
Posted Wednesday, March 31, 2010 6:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 11,194, Visits: 11,167
Hugo Kornelis (3/31/2010)
So if any of the arguments is a subquery, the implementation if COALESCE((subquery), ...) becomes CASE WHEN (subquery) IS NOT NULL THEN (subquery) ... ELSE NULL END.

That reminds me of a CASE construction like the following:
SELECT  CASE CHECKSUM(NEWID()) % 3 + 1
WHEN 1 THEN 'One'
WHEN 2 THEN 'Two'
WHEN 3 THEN 'Three'
ELSE 'WTF?'
END

...which quite frequently hits the ELSE, and that surprises some people.

And the optimizer that sits a bit further down the line does apparently not realize that the two subqueries are identical, so it happily produces a plan that will evaluate the same subquery twice.

Once expanded by the CASE, is it true to say that they are identical? It seems to me that the results are non-deterministic, so it would not be safe to collapse these evaluations. (One might argue that it would be safe at the SERIALIZABLE isolation level (or one of the row-versioning isolation levels), but the plan would not then be reusable).

I blame the expansion of the CASE - which is apparently a documented design decision. I can live with it, though.

Last thing: the optimizer does a great job at avoiding the extra subquery evaluations where possible. The plan produced is quite likely to feature a PassThrough expression on the joins, so that the subquery is not re-evaluated if a prior term in the COALESCE already produced a non-NULL value.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #893712
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse