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

How long is a NULL? Expand / Collapse
Author
Message
Posted Thursday, December 10, 2009 9:13 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:15 AM
Points: 319, Visits: 669
Comments posted to this topic are about the item How long is a NULL?
Post #832704
Posted Thursday, December 10, 2009 10:13 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 11, 2013 12:41 PM
Points: 621, Visits: 297
I did not really knew that.



Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
Post #832718
Posted Friday, December 11, 2009 1:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:16 AM
Points: 1,533, Visits: 7,924
Does anyone know why it behaves like this? It appears to be at odds with any documentation I can find.

BrainDonor.


BrainDonor
Linkedin
Blog Site
Post #832756
Posted Friday, December 11, 2009 4:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:15 AM
Points: 319, Visits: 669
The documentation is slightly different; 2005 BOL says that when "replacement_value" is returned, it will be implicitly converted to the type of "check_expression" while 2000 BOL just demands both expressions to be of same type.

This was the last straw for me, I haven't typed "ISNULL" into a code window since but switched to always using COALESCE.

Which I was expecting someone would mention. Perhaps there are circumstances where usage of ISNULL is warranted, but such use would require good commenting as most think of ISNULL and COALESCE as being equal.

COALESCE returns the full 10-character "replacement_value" in both versions.
Post #832830
Posted Friday, December 11, 2009 5:28 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:33 AM
Points: 821, Visits: 833
Wow, that is just bizarre. I wouldn't have called that in a million years, and I don't feel the least bit bad about getting it wrong. Great question!

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #832841
Posted Friday, December 11, 2009 5:45 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 1,529, Visits: 5,184
Yes, I found this bizarre behaviour the first time I encountered it. Just did a quick experiment on a SQL 2008 installation and I see it still does the same there, so guess we're probably stuck with this now. Pity, because it's utterly non-intuitive that NULL should have a size at all!
Post #832847
Posted Friday, December 11, 2009 6:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:39 AM
Points: 2,397, Visits: 2,287
Also a void string take 1 CHAR:

SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('abcd'+''+null,'1234567890')

Result: 123456
Post #832865
Posted Friday, December 11, 2009 7:20 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:30 AM
Points: 346, Visits: 518


as well ..

SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('abcd'+NULL+NULL,'1234567890')

Results : 123456


--
Sabya
Post #832922
Posted Friday, December 11, 2009 8:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:39 AM
Points: 2,397, Visits: 2,287
The question is:
it is a BUG?
... or it is by design?
... or both: it is a BUG by design!


Try this:

DECLARE @v char(10)
SET CONCAT_NULL_YIELDS_NULL ON
SELECT ISNULL('abcd'+@v,'1234567890')

RESULT: 1234567890

This behavior is all right!
Post #832983
Posted Friday, December 11, 2009 9: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: Friday, November 22, 2013 11:05 AM
Points: 3,108, Visits: 2,116
ahhh ... just another case of getting 'caught' by an 'implicit' converstion !



Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #832999
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse