<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Richard Maw
»
An interesting thing about isnull
45 posts, Page 5 of 5
««
«
1
2
3
4
5
An interesting thing about isnull
Rate Topic
Display Mode
Topic Options
Author
Message
Paul White
Paul White
Posted Thursday, April 01, 2010 4:26 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 10,990,
Visits: 10,540
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
L' Eomot Inversé
L' Eomot Inversé
Posted Wednesday, April 07, 2010 1:11 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 7,104,
Visits: 7,170
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #898223
L' Eomot Inversé
L' Eomot Inversé
Posted Wednesday, April 07, 2010 1:29 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 7,104,
Visits: 7,170
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #898236
Paul White
Paul White
Posted Wednesday, April 07, 2010 2:09 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 10,990,
Visits: 10,540
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
Paul White
Paul White
Posted Wednesday, April 07, 2010 2:13 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 10,990,
Visits: 10,540
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 »
45 posts, Page 5 of 5
««
«
1
2
3
4
5
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
