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 Thursday, April 1, 2010 4:26 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 9,928, Visits: 11,199
Hugo Kornelis (4/1/2010)
Wow! That is really intriguing....

Exactly my reaction when I first looked at your Connect item! I have added it to my watch list.
Thanks, Hugo.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #894574
Posted Wednesday, April 7, 2010 1:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 7,874, Visits: 9,613
Paul White NZ (3/31/2010)
CirquedeSQLeil (3/31/2010)
Adam Machanic summarizes his testing here.

In the comments, Adam notes that there seems to be no difference in SQL Server 2008...

I think that's him expressing himself badly - the figues he gives for SQLS 2008 indicate that coalesce is now measurably faster than isnull.


Tom
Post #898223
Posted Wednesday, April 7, 2010 1:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 7,874, Visits: 9,613
Paul White NZ (4/1/2010)
Hugo Kornelis (3/31/2010)
...I submitted a bug report... a case I constructed, based on this discussion, where COALESCE(..., 1) may erroneously return NULL (which should of course never happen).

Voted, repro'd, and a sort-of 'workaround' added.

The intersting reult here is that the MS reply recognises that the optimiser should recognise that the duplicated expression is just that, and evaluate it only once. So maybe it will get fixed sometime.

It would be good if that were to happen for case statements too, but given that the ANSI definition syas the simple version is a shorthand for the version duplicating the expression I guess it won't. The workaround in the reply works for case too, of course.


Tom
Post #898236
Posted Wednesday, April 7, 2010 2:09 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 9,928, Visits: 11,199
Tom.Thomson (4/7/2010)
Paul White NZ (3/31/2010)
CirquedeSQLeil (3/31/2010)
Adam Machanic summarizes his testing here.

In the comments, Adam notes that there seems to be no difference in SQL Server 2008...

I think that's him expressing himself badly - the figues he gives for SQLS 2008 indicate that coalesce is now measurably faster than isnull.

Did it? My memory was of mixed results. Can't actually be bothered to check again though




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #898259
Posted Wednesday, April 7, 2010 2:13 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 9,928, Visits: 11,199
Tom.Thomson (4/7/2010)
The intersting reult here is that the MS reply recognises that the optimiser should recognise that the duplicated expression is just that, and evaluate it only once. So maybe it will get fixed sometime.

I think it is important to say that this only applies to the specific expansion of COALESCE.
I hope it does get fixed, but I don't expect for one moment that they will change the way COALESCE is implemented (with CASE). Will be an interesting one to watch.

It would be good if that were to happen for case statements too, but given that the ANSI definition syas the simple version is a shorthand for the version duplicating the expression I guess it won't. The workaround in the reply works for case too, of course.

I agree - they won't change the ANSI-esque expansion.




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

Add to briefcase «««12345

Permissions Expand / Collapse