February 1, 2012 at 9:15 pm
Comments posted to this topic are about the item Twist in ISNULL function
February 1, 2012 at 10:02 pm
Possibly easier is to use COALESCE, which doesn't have this problem. COALESCE is also more flexible.
February 1, 2012 at 10:52 pm
Very interesting article!
Something important to remember though about COALESCE is that it will return out the data type of the expression with the highest data type precedence. This isn't functionally the same as an ISNULL, and if developers who don't have a good handle on data type precedence, you may end up with T-SQL with unexpected results.
February 1, 2012 at 11:00 pm
I can't recall the last time I used isnull. I always use coalesce specifically to avoid data type result issues.
February 1, 2012 at 11:34 pm
I wouldn't really call documented behaviour a twist, but maybe it is good to remind people once in a while that there are such caveat's, especially when working with NULL values.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 1, 2012 at 11:56 pm
a good tip to remember.
Very helpful while creating temp tables in store procedures.
February 2, 2012 at 12:04 am
This "twist" in the ISNULL tale should definitely be added in the list of best practices so that developers can avoid wasting valuable time scratching their heads on the "lost" strings.
Great article!!
February 2, 2012 at 12:07 am
An article about clearly documented behaviour, and no mention of the more serious problems with COALESCE? Poor.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2012 at 12:14 am
SQL Kiwi (2/2/2012)
An article about clearly documented behaviour, and no mention of the more serious problems with COALESCE? Poor.
Now you make me curious. Which issues are you referring to? A quick Google search tells me that datatype precedence is important for COALESCE.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 2, 2012 at 1:02 am
Koen Verbeeck (2/2/2012)
SQL Kiwi (2/2/2012)
An article about clearly documented behaviour, and no mention of the more serious problems with COALESCE? Poor.Now you make me curious. Which issues are you referring to? A quick Google search tells me that datatype precedence is important for COALESCE.
SELECT COALESCE((SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 999);
SELECT ISNULL((SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 999);
https://connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2012 at 1:35 am
Surely ISNULL(M.QuesSummary,F.FeedbackQues) is an error?
February 2, 2012 at 1:57 am
SQL Kiwi (2/2/2012)
https://connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null
Very interesting. Thanks for the links!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 2, 2012 at 3:48 am
shouldn't the line 'This confirmed that ISNULL function is type casting @a into @b-2 returning a string equal to the length of variable @a'
be
'This confirmed that ISNULL function is type casting @a into @b-2 returning a string equal to the length of variable @b'
As the length of @a is 100 as apposed to the length being returned for @b-2 of 10.
February 2, 2012 at 4:14 am
Impeccable timing sir. I just used this construct yesterday but did not experience this behavior. I will check today to make sure the lengths of my data types match.
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply