How long is a NULL?

  • Arto Ahlstedt

    Mr or Mrs. 500

    Points: 572

    Comments posted to this topic are about the item How long is a NULL?

  • Bhavesh_Patel

    SSCrazy

    Points: 2259

    I did not really knew that.



    [font="System"]Bhavesh Patel[/font]

    http://bhaveshgpatel.wordpress.com/
  • BrainDonor

    SSCoach

    Points: 19234

    Does anyone know why it behaves like this? It appears to be at odds with any documentation I can find.

    BrainDonor.

    Steve Hall
    Linkedin
    Blog Site

  • Arto Ahlstedt

    Mr or Mrs. 500

    Points: 572

    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.

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    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

  • paul.knibbs

    SSCoach

    Points: 15270

    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!

  • Carlo Romagnano

    SSC-Insane

    Points: 22010

    Also a void string take 1 CHAR:

    SET CONCAT_NULL_YIELDS_NULL ON

    SELECT ISNULL('abcd'+''+null,'1234567890')

    Result: 123456

  • sabyasm

    SSCarpal Tunnel

    Points: 4324

    [font="Comic Sans MS"]

    as well ..

    SET CONCAT_NULL_YIELDS_NULL ON

    SELECT ISNULL('abcd'+NULL+NULL,'1234567890')

    Results : 123456

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • Carlo Romagnano

    SSC-Insane

    Points: 22010

    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!

  • Rudyx - the Doctor

    SSC-Forever

    Points: 43696

    ahhh ... just another case of getting 'caught' by an 'implicit' converstion !

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • FargoUT

    SSC Eights!

    Points: 853

    Interesting behavior ... I had to look up the YIELDS_NULL ON before I answered the question, then still got it wrong. Had I thought about it longer, I would have realized that there is only one answer that actually fits. But I jumped the gun and chose the wrong one. Oh well, yet another one I got wrong. *beginning to question my choice in career now* 😀

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Arto ahlstedt (12/10/2009)


    the NULL that is appended somehow contributes to the total length of the expression with an effective length of 1.

    Got to love the "somehow"...

    Since NULL can be of any datatype, SQL Server always has a hard time determining what datatype was meant when it encounteres the constant NULL. In this case, since it's appended to a string, it's obviously a string as well. So that makes it a varchar (char could have been used as well, but SQL Server chooses varchar in these cases). Both of these have a default length of 1.

    The result of concatenating varchar(5) ('abcde') with varchar(1) is of course varchar(6), so that is the datatype of the first argument. And since ISNULL returns the same datatype as the first argument, the result is also varchar(6).

    Here is some code to repro this:

    SELECT 'abcde' AS a, 'abcde' + NULL AS b INTO TestTable;

    go

    EXEC sp_help TestTable;

    go

    DROP TABLE TestTable;


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Jon Cooney

    Old Hand

    Points: 329

    SET CONCAT_NULL_YIELDS_NULL ON

    DECLARE @STR AS VARCHAR(8)

    SET @STR = 'abcdefghij'

    SELECT @STR

    SELECT LEN(@str)

    SELECT LEN(@str+NULL)

    SELECT ISNULL(@str+NULL,'1234567890')

    --------

    abcdefgh

    -----------

    8

    -----------

    NULL

    ---------

    123456789

    Okay.....

  • vk-kirov

    SSCertifiable

    Points: 7686

    Hugo Kornelis

    Thank you for explanation. Could you also explain why "under SQL Server 2000, the result is '1234' regardless of the number of NULLs appended"?

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    vk-kirov (12/12/2009)


    Hugo Kornelis

    Thank you for explanation. Could you also explain why "under SQL Server 2000, the result is '1234' regardless of the number of NULLs appended"?

    Thanks!

    First, please be aware that my explanation for the SQL Server 2005 behaviour is based on guesswork and then verified by experiments. I don't have a SQL Server 2000 instance running, so I can only guess there.

    Also be aware that, as far as I know, there is no documentation about how SQL Server guesses the datatype of a NULL. As with any undocumented feature, it can change between sessions, or even between service packs or hotfixes.

    My guess is that on SQL Server 2000, the engine assumes a zero length string for NULL. That, or some completely different conversion takes place - if I recall correctly, a lot of the implicit type conversion stuff was changed between 2000 and 2005.

    Bottom line - if you need NULL to be the datatype you expect it to be, always use CAST(NULL AS datatype)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

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

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