SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Understanding the difference between IS NULL and = NULL


Understanding the difference between IS NULL and = NULL

Author
Message
Carl Federl
Carl Federl
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3242 Visits: 4350
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
Rajni Kant Ranjan
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 500
Thanks Travis to provide interesting topics like that before that i was very confuse about NULL.
Thanks ................................
Excellent jobs
S-322532
S-322532
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 Visits: 268
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.
ben012453
ben012453
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
ben012453
ben012453
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
mmcginty
mmcginty
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 75
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!
Arnie Stewart
Arnie Stewart
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
stefanbeeli
stefanbeeli
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.



gatorspike
gatorspike
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 21
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)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12173 Visits: 18572
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search