October 29, 2005 at 8:53 am
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
August 22, 2007 at 11:54 pm
Thanks Travis to provide interesting topics like that before that i was very confuse about NULL.
Thanks ................................
Excellent jobs
January 24, 2008 at 3:07 am
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.
January 29, 2008 at 6:19 pm
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
January 29, 2008 at 6:27 pm
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
February 22, 2008 at 3:09 am
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]
February 22, 2008 at 8:11 am
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
February 22, 2008 at 8:17 am
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.
February 22, 2008 at 1:20 pm
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.
February 22, 2008 at 1:28 pm
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?
February 23, 2008 at 9:36 am
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 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply