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 12»»

ISNULL Expand / Collapse
Author
Message
Posted Monday, November 02, 2009 9:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 2:20 AM
Points: 216, Visits: 169
Comments posted to this topic are about the item ISNULL
Post #812707
Posted Tuesday, November 03, 2009 12:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 6:08 AM
Points: 1,076, Visits: 591
"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
Posted Tuesday, November 03, 2009 1:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 2,393, Visits: 2,286
From BOL:
Return Types
Returns the same type as check_expression.

In this case, a cast from varchar(12) to varchar(10)

Post #812787
Posted Tuesday, November 03, 2009 5:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:58 AM
Points: 382, Visits: 908
I find these questions great training - thanks!


RedLlewy
"The Plural of Anecdote is not Data"

Post #812860
Posted Tuesday, November 03, 2009 6:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:12 PM
Points: 2,007, Visits: 768
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
Posted Tuesday, November 03, 2009 6:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 2,393, Visits: 2,286
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
Posted Tuesday, November 03, 2009 6:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:12 PM
Points: 2,007, Visits: 768
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
Posted Tuesday, November 03, 2009 6:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 5,794, Visits: 8,008
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
Posted Tuesday, November 03, 2009 6:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 2:20 AM
Points: 216, Visits: 169
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
Posted Tuesday, November 03, 2009 7:00 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: Tuesday, April 15, 2014 12:05 PM
Points: 3,569, Visits: 72,411
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 »

Add to briefcase 12»»

Permissions Expand / Collapse