Twist in ISNULL function

Question

Post reply

Twist in ISNULL function

ashutosh.kumarpandey

Old Hand

Points: 341
More actions
February 1, 2012 at 9:15 pm

#254725

Comments posted to this topic are about the item Twist in ISNULL function

Viewing 15 posts - 1 through 15 (of 46 total)

You must be logged in to reply to this topic. Login to reply

Dean Cochrane SSC Eights! Points: 992 More actions · Answer 1

Possibly easier is to use COALESCE, which doesn't have this problem. COALESCE is also more flexible.

Andrew la Grange SSC Journeyman Points: 84 More actions · Answer 2

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.

sherifffruitfly Ten Centuries Points: 1198 More actions · Answer 3

I can't recall the last time I used isnull. I always use coalesce specifically to avoid data type result issues.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

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

abhishekgupta109 SSC Enthusiast Points: 109 More actions · Answer 5

a good tip to remember.

Very helpful while creating temp tables in store procedures.

saurabh.kaushik SSC Rookie Points: 49 More actions · Answer 6

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!!

Paul White SSC Guru Points: 150467 More actions · Answer 7

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

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

Paul White SSC Guru Points: 150467 More actions · Answer 9

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

https://connect.microsoft.com/SQLServer/feedback/details/336002/unnecessarily-bad-performance-for-coalesce-subquery

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

andy-1061113 Grasshopper Points: 15 More actions · Answer 10

Surely ISNULL(M.QuesSummary,F.FeedbackQues) is an error?

bart.de.koning SSC Enthusiast Points: 141 More actions · Answer 11

Select Case WHEN @b-2 IS null THEN @a ELSE @b-2 END

Will do nicely

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 12

SQL Kiwi (2/2/2012)
https://connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null
https://connect.microsoft.com/SQLServer/feedback/details/336002/unnecessarily-bad-performance-for-coalesce-subquery

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

scottcrichton SSC Rookie Points: 27 More actions · Answer 13

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.

yohannn SSC Enthusiast Points: 119 More actions · Answer 14

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.