Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Arto Ahlstedt » How long is a NULL?
Add to briefcase
30 posts, Page 1 of 3
123»»»

How long is a NULL? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Arto Ahlstedt
Posted Thursday, December 10, 2009 9:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:13 AM
Points: 298, Visits: 574
Comments posted to this topic are about the item How long is a NULL?
Post #832704
Bhavesh_Patel
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
BrainDonor
Posted Friday, December 11, 2009 1:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:47 AM
Points: 1,400, Visits: 6,886
Does anyone know why it behaves like this? It appears to be at odds with any documentation I can find.

BrainDonor.

BrainDonor
Linkedin
Post #832756
Arto Ahlstedt
Posted Friday, December 11, 2009 4:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:13 AM
Points: 298, Visits: 574
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
ronmoses
Posted Friday, December 11, 2009 5:28 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 10:09 AM
Points: 648, Visits: 684
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
paul.knibbs
Posted Friday, December 11, 2009 5:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:56 AM
Points: 1,256, Visits: 4,253
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
Carlo Romagnano
Posted Friday, December 11, 2009 6:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:04 AM
Points: 1,968, Visits: 1,819
Also a void string take 1 CHAR:

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

Result: 123456
Post #832865
sabyasm
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: Tuesday, March 26, 2013 3:14 AM
Points: 345, Visits: 503


as well ..

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

Results : 123456

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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:04 AM
Points: 1,968, Visits: 1,819
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
rudy - Doctor "X"
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: Yesterday @ 2:11 PM
Points: 3,108, Visits: 2,114
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
30 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.