Understanding the difference between IS NULL and = NULL

  • Carl Federl

    One Orange Chip

    Points: 25384

    It is a SQL Server BUG that is fixed in Service Pack 4

    FIX: A parallel query may return unexpected results

    http://support.microsoft.com/default.aspx?scid=kb;en-us;814509

    SYMPTOMS

    When the following conditions are met, a parallel query may return unexpected results:

    The query uses a parallel nonclustered index scan in the execution plan.

    The query contains an IS NULL condition in the WHERE clause of the query.

    SQL = Scarcely Qualifies as a Language

  • Rajni Kant Ranjan

    SSC-Addicted

    Points: 444

    Thanks Travis to provide interesting topics like that before that i was very confuse about NULL.

    Thanks ................................

    Excellent jobs

  • S-322532

    SSCommitted

    Points: 1613

    When a variable is created in SQL with the declare statement it is created with no data and stored in the variable table (vtable) inside SQLs memory space. The vtable contains the name and memory address of the variable. However, when the variable is created no memory address is allocated to the variable and thus the variable is not defined in terms of memory.

    Hi,

    Out of confusion aroused with the above two statements (highlighted )contradicting. Can any one explain them.

  • ben012453

    Grasshopper

    Points: 14

    Stephen Baez, I believe this is what you are looking for:

    SELECT COUNT(*) AS TotalRows, COUNT(id) AS NonNULLRows, COUNT(*) - COUNT(id) AS NULLRows FROM

    Modify to fit your database.

    COUNT(*) does exactly that, it returns ALL ROWS of a table.

    -SQL NEWB

  • ben012453

    Grasshopper

    Points: 14

    S,

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    When a variable is created in SQL with the declare statement it is created with no data and stored in the variable table (vtable) inside SQLs memory space. The vtable contains the name and memory address of the variable. However, when the variable is created no memory address is allocated to the variable and thus the variable is not defined in terms of memory

    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

    This statement appears to make sense to me. However I can only speak from a logical programming standpoint:

    -There is a table to hold references to variables for the declare statement (vtable)

    - vtable -

    NAME

    MEMORY ADDRESS

    - Since a table can contain nulls it would stand that the vtable can have nulls for MEMORY ADDRESS.

    - When a variable is created with a declare statement the vtable gets the NAME you gave it and a null for the MEMORY ADDRESS ??? (needs verification, I dont know if this is true)

    NAME | MEMORY ADDRESS

    ----------------

    | ID | NULL |

    ----------------

    - When a variable is assigned a value THEN it gets a memory address that points to that value. ??? (needs verification, I dont know if this is true)

    NAME | MEMORY ADDRESS

    ---------------------------

    | ID | 9001232412321 |

    ---------------------------

    Again this is subjectory siince I am a SQL NEWB and am only writing this from reading the statement you have posted above. But it would seem to be the case.

    -SQL NEWB

  • mmcginty

    SSC Veteran

    Points: 296

    Interesting article, but I'm afraid your comments about C++ are not technically accurate:

    In C++ when a variable is created the variable has an address of 0xddddddd (in debug but it can be different non-real addresses as well). When you set the variable the first time checking the address will give you a valid memory address where the data is being stored.

    It can be easily proven that this is not the case. In C++ a variable has both an address and storage space upon declaration. In debug mode that storage is initialized with a distinctive pattern (such as 0xdd, depending on compiler version.) In release mode, it will contain whatever random garbage happened to be in memory.

    This is true of both pointer types and non pointer types (in the strictest sense) though pointer types require a bit more explanation: A pointer type, as its name suggests, is a variable that stores an address that points to something else.

    When a pointer is declared, it is true that no memory has been allocated to store anything of the type it points to, that space must be separately allocated, and its address is assigned to the pointer. Even so, at declaration a pointer type does have an address of it's own, and enough memory to store a pointer has been allocated for it.

    cout.flags(ios::hex);

    int i; // sizeof(int) bytes allocated on the stack

    int *p; // size of a memory address allocated on the stack

    cout << i << endl; // garbage value

    cout << (int)&i << endl; // valid mem location

    cout << (int)p << endl; // garbage value

    cout << *p << endl; // access fault! can't dereference invalid location

    cout << (int)&p << endl; // valid mem location (double indirection)

    p = &i; // storage for an int now assigned to pointer

    cout << *p << endl; // value is still garbage, but valid to defererence

    *p = 1; // assigning value to mem pointed to

    cout << i << endl; // pop quiz: what will this output be? 🙂

    -Mark McGinty

    [font="Comic Sans MS"]The Black Knight ALWAYS triumphs. Have at you![/font]

  • Arnie Stewart

    Grasshopper

    Points: 15

    I was going to add a reply very similar to that made by Grasshopper, but in that he did, I will just say that I have do the same and found it to be very trust worthy.

    Arnie

  • stefanbeeli

    Newbie

    Points: 7

    Let me preface these statements with "IN GENERAL"

    Using Null values in a database is just bad design.

    It adds a third state to the value of a variable. (=, <>, IsNull)

    Good database designers will create a database that doesn't contains Nulls whereever possible. I've been doing this for years and I don't have a Null issue. There may be other applications that require it, but in most real-world business programming it's never an issue.

    Just say no to NULL. It makes life much easier.

  • gatorspike

    SSC Journeyman

    Points: 90

    I am calling Bull%&!$ on this one because it starts wrong - big time. Heck it was not even tested.

    Try this: (The first three statements are from the article)

    DECLARE @val CHAR(4)

    SET @val = NULL

    If @val = NULL

    select 1

    else

    select 0

    You get 0. Nothing is = to null. not even null.

    Try this:

    If NULL = NULL

    select 1

    else

    select 0

    You get 0

    Try this:

    DECLARE @val2 CHAR(4)

    DECLARE @val3 CHAR(4)

    SET @val2 = NULL

    SET @val3 = NULL

    If @val2 = @val3

    select 1

    else

    select 0

    You get 0

    You cannot ask for equals to null ever. You must ask if it is null.

  • Matt Miller (4)

    SSC Guru

    Points: 124208

    Mike -

    If you happen to set ANSI_NULLS, then it CAN be true....

    Try this:

    set ANSI_NULLS OFF

    go

    select case when NULL=NULL then 0 else 1 end

    go

    set ANSI_NULLS ON

    go

    select case when NULL=NULL then 0 else 1 end

    go

    set ANSI_NULLS OFF

    Of course - IMO messing with ANSI_NULLS is just asking for trouble (and is a deprecated setting, so stop playing with it!!!!!!!!!), but that is an entirely different issue.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • gatorspike

    SSC Journeyman

    Points: 90

    You are correct Matt. I guess I was a bit hasty in my remarks. I, like you, do not mess with the ANSI_NULLS option.

    Mike

Viewing 11 posts - 31 through 41 (of 41 total)

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