<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 Marius Els
»
ISNULL
12 posts, Page 1 of 2
1
2
»»
ISNULL
Rate Topic
Display Mode
Topic Options
Author
Message
Marius Els
Marius Els
Posted Monday, November 02, 2009 9:06 PM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, October 31, 2012 3:59 AM
Points: 215,
Visits: 166
Comments posted to this topic are about the item
ISNULL
Post #812707
BudaCli
BudaCli
Posted Tuesday, November 03, 2009 12:32 AM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, March 27, 2013 9:02 AM
Points: 1,046,
Visits: 573
"The length of the value returned is always equal to or less than the field length of the checked expression"....
What you don't know won't hurt you but what you know will make you plan to know better
Post #812770
Carlo Romagnano
Carlo Romagnano
Posted Tuesday, November 03, 2009 1:34 AM
SSCommitted
Group: General Forum Members
Last Login: 2 days ago @ 2:04 AM
Points: 1,968,
Visits: 1,819
From BOL:
Return Types
Returns the same type as check_expression.
In this case, a cast from varchar(12) to varchar(10)
Post #812787
RedLlewy
RedLlewy
Posted Tuesday, November 03, 2009 5:06 AM
Old Hand
Group: General Forum Members
Last Login: 2 days ago @ 3:06 AM
Points: 367,
Visits: 845
I find these questions great training - thanks!
RedLlewy
"
The Plural of Anecdote is
not
Data
"
Post #812860
Noel McKinney
Noel McKinney
Posted Tuesday, November 03, 2009 6:00 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, April 05, 2012 2:35 PM
Points: 2,007,
Visits: 767
This is the main reason I make it a habit to use COALESCE rather than ISNULL unless there's a reason I need the behavior of ISNULL.
Post #812873
Carlo Romagnano
Carlo Romagnano
Posted Tuesday, November 03, 2009 6:07 AM
SSCommitted
Group: General Forum Members
Last Login: 2 days ago @ 2:04 AM
Points: 1,968,
Visits: 1,819
Noel McKinney (11/3/2009)
This is the main reason I make it a habit to use COALESCE rather than ISNULL unless there's a reason I need the behavior of ISNULL.
I prefer ISNULL because of performance.
Post #812877
Noel McKinney
Noel McKinney
Posted Tuesday, November 03, 2009 6:32 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, April 05, 2012 2:35 PM
Points: 2,007,
Visits: 767
Carlo Romagnano (11/3/2009)
Noel McKinney (11/3/2009)
This is the main reason I make it a habit to use COALESCE rather than ISNULL unless there's a reason I need the behavior of ISNULL.
I prefer ISNULL because of performance.
Interesting, I can't recall coming across references to performance comparisons between COALESCE and ISNULL, but yes, there does seem to be a slight advantage in CPU time with ISNULL that could be noticed over a very large number of rows. However, in my environment, preventing unintended truncation of string values is more important than slight performance gains.
Post #812889
Hugo Kornelis
Hugo Kornelis
Posted Tuesday, November 03, 2009 6:36 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 5,232,
Visits: 7,023
Carlo Romagnano (11/3/2009)
Noel McKinney (11/3/2009)
This is the main reason I make it a habit to use COALESCE rather than ISNULL unless there's a reason I need the behavior of ISNULL.
I prefer ISNULL because of performance.
So, do you have any repro code that actually demonstrates a performance difference between COALESCE and ISNULL? If so, please post!
The only situation I know of is when an expression in COALESCE is a subquery - in those cases, ISNULL can be found to perform better. But how often does one actuallly need an entire subquery in COALESCE or ISNULL?
Like Noel, I use COALESCE rather than ISNULL. Because of the weird datatyping rules of ISNULL, but also because it supports more than two arguments, and because it's ANSI standard. The only reasons I'd ever prefer ISNULL are
1) If I have to use it in a view or computed column where the column has to be not nullable - any COALESCE expression is always nullable; ISNULL is only nullable is the second argument is nullable.
2) If any of the arguments has to be a subquery, because of the potential performance problems COALESCE has with those - and since this is acutally caused by an optimizer weakness, I'd make a note to check if the optimizer has finally improved in this respect after every version or servicepack upgrade.
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog:
http://sqlblog.com/blogs/hugo_kornelis
Post #812893
Marius Els
Marius Els
Posted Tuesday, November 03, 2009 6:51 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, October 31, 2012 3:59 AM
Points: 215,
Visits: 166
I've almost 'grown up' (professionally) with microsoft's sql standard, thus I'm not too familiar with ANSI standard.
I've played with COALESCE before, but I find ISNULL is sufficient for my requirements; I haven't had to deal with multiple arguments possibly being null and I don't work with large number of rows...
But it is good to know how to 'skin the cat' a different way
Thanks for the input guys
Post #812899
mtassin
mtassin
Posted Tuesday, November 03, 2009 7:00 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 9:35 PM
Points: 3,226,
Visits: 64,096
I too go with using ISNULL because I discovered it before COALESCE.
For multiple NULL values until I discovered COALESCE, I'd just chain ISNULL's
ISNULL(<test value>,ISNULL(<test value 2>,<final value))
etc.
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #812901
« Prev Topic
|
Next Topic »
12 posts, Page 1 of 2
1
2
»»
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.
