SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Twist in ISNULL function


Twist in ISNULL function

Author
Message
ashutosh.kumarpandey
ashutosh.kumarpandey
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 77
Comments posted to this topic are about the item Twist in ISNULL function
Dean Cochrane
Dean Cochrane
Mr or Mrs. 500
Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)

Group: General Forum Members
Points: 570 Visits: 641
Possibly easier is to use COALESCE, which doesn't have this problem. COALESCE is also more flexible.
Andrew la Grange
Andrew la Grange
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 15
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
sherifffruitfly
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 427
I can't recall the last time I used isnull. I always use coalesce specifically to avoid data type result issues.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)

Group: General Forum Members
Points: 133776 Visits: 13348
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
abhishekgupta109
abhishekgupta109
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 26
a good tip to remember.

Very helpful while creating temp tables in store procedures.
saurabh.kaushik
saurabh.kaushik
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 98
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
Paul White
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73536 Visits: 11399
An article about clearly documented behaviour, and no mention of the more serious problems with COALESCE? Poor.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)

Group: General Forum Members
Points: 133776 Visits: 13348
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Paul White
Paul White
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73536 Visits: 11399
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search