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 «««12345»»

Understanding the difference between IS NULL and = NULL Expand / Collapse
Author
Message
Posted Saturday, October 29, 2005 8:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:17 AM
Points: 2,281, Visits: 4,227
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
Post #233627
Posted Wednesday, August 22, 2007 11:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 9, 2014 3:10 AM
Points: 156, Visits: 385
Thanks Travis to provide interesting topics like that before that i was very confuse about NULL.
Thanks ................................
Excellent jobs
Post #393086
Posted Thursday, January 24, 2008 3:07 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:02 AM
Points: 141, Visits: 260
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. [color=#ff#00#00]The vtable contains the name and memory address of the variable[/color]. [color=#00#ff#00]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[/color].

Hi,

Out of confusion aroused with the above two statements (highlighted )contradicting. Can any one explain them.
Post #446802
Posted Tuesday, January 29, 2008 6:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 29, 2008 6:19 PM
Points: 2, Visits: 1
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
Post #449139
Posted Tuesday, January 29, 2008 6:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 29, 2008 6:19 PM
Points: 2, Visits: 1
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
Post #449140
Posted Friday, February 22, 2008 3:09 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 3:10 AM
Points: 40, Visits: 73
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


The Black Knight ALWAYS triumphs. Have at you!
Post #458986
Posted Friday, February 22, 2008 8:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 12:40 PM
Points: 3, Visits: 43
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
Post #459124
Posted Friday, February 22, 2008 8:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 4, 2013 9:23 AM
Points: 1, Visits: 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.



Post #459127
Posted Friday, February 22, 2008 1:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 9:09 AM
Points: 16, Visits: 20
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.




Post #459277
Posted Friday, February 22, 2008 1:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:00 PM
Points: 7,105, Visits: 15,443
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?
Post #459282
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse