SQL Server Performance

SQL Server might have bug when dealing with 'null' value

Discussion in 'SQL Server 2008 Questions [Archive Only - Closed]' started by kkabir, May 2, 2008.

  1. kkabir New Member

    SQL Server might have bug when dealing with 'null' valueDeclare @N as intset @N=NULLSelect len(isnull(@N,''))Select len(isnull(NULL,''))It should give output ‘0’ for both query. Check by yourself.Results coming as:
    1

    0
  2. MichaelB Member

    That is because you cant set an int to empty string. it would be 0 which has a length of 1. The one where you dont declare a type is defaulted to char I believe... Try below..Declare @N as int
    set
    @N=NULLSelect
    len(isnull(@N,''))Select
    len(isnull(NULL,''))Select
    isnull(@N,'')
  3. Adriaan New Member

    Let's not forget that len(null) returns null too. And perhaps OP does not fully understand the point of the ISNULL() function.
    Do not mistake predictable behaviour of SQL Server for a bug. What you need to do yourself, as a DBA or a programmer, is to anticipate correctly how SQL Server handles nulls.
  4. kkabir New Member

    If not a bug, then that is a problem
    I have a comeplecated query when I have to check then lenth of an integer and padding 0 to make same length,
    Select len(isnull(intFieldName,'')) -funtion giving 0-9 same value as when null.
  5. Adriaan New Member

    You're mixing character and integer data - don't!
    What about LEN(ISNULL(intFieldName, 0) - or try a CASE expression:

    SELECT CASE WHEN intFieldName IS NULL THEN ... ELSE ... END
  6. MichaelB Member

    But what you are trying to do is make a int be an empty string. It cant. it is not a string that can be empty. The problem is your understaning of this concept it seems. a isnull for a int should be isnull(intfield,0) not isnull(intfield,'').
  7. Dolson New Member

    About IsNull From Books Online...
    ISNULL
    Replaces NULL with the specified replacement value.
    Syntax
    ISNULL ( check_expression , replacement_value )
    Arguments
    check_expression
    Is the expression to be checked for NULL. check_expression can be of any type.
    replacement_value
    Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.
    Return Types
    Returns the same type as check_expression[:D]
  8. moh_hassan20 New Member

    can you imagine that buzzle:
    Declare @N as int
    set @N=NULL
    Select len(isnull(@N,''))
    Select len(isnull(NULL,''))

    it return
    1
    0
    --------------------------
    Declare @N as datetime
    set @N=NULL
    Select len(isnull(@N,''))
    Select len(isnull(NULL,''))
    it return
    19 [:D] [:D]
    0
    ------------------------
    Declare @N as char
    set @N=NULL
    Select len(isnull(@N,''))
    Select len(isnull(NULL,''))

    it return
    0
    0
    -----------------------
    Declare @N as sysname
    set @N=NULL
    Select len(isnull(@N,''))
    Select len(isnull(NULL,''))
    it return
    0 [;)]
    0

    .... and so on
    so it is not a bug , but it is due to the internal storage of NULL for the different datatype

Share This Page