Four Rules for NULLs

Question

Four Rules for NULLs

Viewing 15 posts - 121 through 135 (of 152 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Mike C (3/28/2008)
I get a clustered index scan when I run your code. The optimizer expands it out to RowNum <> 1 AND RowNum <> 2 AND RowNum <> 3.

Hmmm... I get Clustered Index Seeks on both... I'm using Dev Edition of 2k sp3a (simto Enterprise Edition) in this case, Mike... what are you using?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kaspencer SSCarpal Tunnel Points: 4880 More actions · Answer 2

Sorry not to be repentant, but:

1. NULL is indeed "not [yet] assigned". That is the case in any database column which has no value, but which has the capacity to be assigned a value.

2. NULL can also represent "no value" as a result in a query. If you read my previous post in connection with the question on NULLs a week or two ago, you find an illustration of this scenario whereby a query can return a result having no value, i.e. NULL.

It is partly a question of semantics, yes, but that is what enables accuracy and precision of expression.

Thanks,

Kenneth Spencer

You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer

kaspencer SSCarpal Tunnel Points: 4880 More actions · Answer 3

Sorry, but because I couldn't read your post while writing my reply, I forgot to add my third point:

3. Zero ( 0 ) is most definitely NOT the same as NULL. Absolutely and definitely NOT. Zero is a proper numerical value, and has been since ancient Indian mathematicians came to see it as such. And every zero is equal to every other zero. And I and others in this discussion have repeated the fact that no two NULLs are equal, as a NULL cannot be evaluated.

Thanks again,

Kenneth Spencer

You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Ok... it's all semantics... I call zero "nothing" and an empty string "nothing" and I say "Null is NOT nothing". You say Null is nothing. I disagree, but I understand what you mean.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

kaspencer (3/28/2008)
Sorry, but because I couldn't read your post while writing my reply, I forgot to add my third point:

By the way, if you scroll down when constructing a reply, you'll find a dark blue bar that says "Last 10 Posts in Descenting Order". If you click on the "+" on that same bar, you can see the last 10 posts. Not always fortunate enough to have the post you want show up, though. When that happens, I just open another tab or window with the same post so I can see what I'm writing and the post I'm writing about. Sometimes, I'll reply with a quote so I can see the post I'm replying to and maybe delete the quote when I'm done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike C SSC-Insane Points: 23224 More actions · Answer 6

Jeff Moden (3/28/2008)
Mike C (3/28/2008)
I get a clustered index scan when I run your code. The optimizer expands it out to RowNum <> 1 AND RowNum <> 2 AND RowNum <> 3.
Hmmm... I get Clustered Index Seeks on both... I'm using Dev Edition of 2k sp3a (simto Enterprise Edition) in this case, Mike... what are you using?

I ran it on Enterprise Edition 2005 SP 2 here.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Mike C (3/28/2008)
Jeff Moden (3/28/2008)
Mike C (3/28/2008)
I get a clustered index scan when I run your code. The optimizer expands it out to RowNum <> 1 AND RowNum <> 2 AND RowNum <> 3.
Hmmm... I get Clustered Index Seeks on both... I'm using Dev Edition of 2k sp3a (simto Enterprise Edition) in this case, Mike... what are you using?
I ran it on Enterprise Edition 2005 SP 2 here.

Heh... confirmed! I got the same thing in 2k5 SP2... Clustered Index Scan... I wonder which release is right? I've seen a lot of strange stuff in the execution plans on 2k5...

Thanks for the info and the test, Mike.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Jeff Moden (3/28/2008)
Mike C (3/28/2008)
Jeff Moden (3/28/2008)
Mike C (3/28/2008)
I get a clustered index scan when I run your code. The optimizer expands it out to RowNum <> 1 AND RowNum <> 2 AND RowNum <> 3.
Hmmm... I get Clustered Index Seeks on both... I'm using Dev Edition of 2k sp3a (simto Enterprise Edition) in this case, Mike... what are you using?
I ran it on Enterprise Edition 2005 SP 2 here.
Heh... confirmed! I got the same thing in 2k5 SP2... Clustered Index Scan... I wonder which release is right? I've seen a lot of strange stuff in the execution plans on 2k5...
Thanks for the info and the test, Mike.

Maybe I'm thinking of it the wrong way, but when I see nothing but inequalities in the WHERE, I think "scan". I guess I'm also presuming that the scan indicates you're removing a few records as compared to the whole.

----------------------------------------------------------------------------------
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?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Yeaup... I normally do too. I think the difference between 2k and 2k5 might be that 2k is identifying how the first row is found and 2k5 is identifying how the rest of the rows are found.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 10

I know this is an old article, but this is the first time I read it, and it is the best written piece explaining nulls I have seen yet. I have passed it on to some of my less experienced colleagues. Thank you for supplying it.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Ian Crandell SSCommitted Points: 1603 More actions · Answer 11

timothyawiseman (3/31/2008)
I know this is an old article, but this is the first time I read it, and it is the best written piece explaining nulls I have seen yet. I have passed it on to some of my less experienced colleagues. Thank you for supplying it.

This is also my first time reading this and I completely concur. This makes NULLs much clearer.

I have one question about the truth table (and this might have been answered previously, but I didn't have time to read all the posts). I was wondering why "False OR Unknown" returns Unknown and yet "True OR Unknown" returns True. Maybe it is too late in the day to wrap my head around that.:hehe:

Mike - Not only a great article, but I love your avatar.

Thanks,

Ian.

"If you are going through hell, keep going."
-- Winston Churchill

Mike C SSC-Insane Points: 23224 More actions · Answer 12

Ian Crandell (4/1/2008)
This is also my first time reading this and I completely concur. This makes NULLs much clearer.
I have one question about the truth table (and this might have been answered previously, but I didn't have time to read all the posts). I was wondering why "False OR Unknown" returns Unknown and yet "True OR Unknown" returns True. Maybe it is too late in the day to wrap my head around that.:hehe:
Mike - Not only a great article, but I love your avatar.
Thanks,

No problem, and thanks (big Family Guy fan here) 🙂

This is an oversimplification (no flames please), but I suppose you could think of it like this: replacing "Unknown" with a variable like "x" for which you don't know the value results in something like this:

"False OR x" --> The "x" is not known, but it could be "True" or "False". Those two possible values result in two different results when substituted for "x" ("False OR True" = "True" and "False OR False" = "False"). So you really don't know what the result of "False OR x" is - it's unknown.

"True OR x" --> The "x" could stand for "True" or "False". Whichever one is used as a value for "x" doesn't matter since "True OR anything" always yields "True".

Like I said, this is an oversimplification, but it's the basic idea.

Ian Crandell SSCommitted Points: 1603 More actions · Answer 13

Mike C (4/1/2008)
No problem, and thanks (big Family Guy fan here) 🙂
This is an oversimplification (no flames please), but I suppose you could think of it like this: replacing "Unknown" with a variable like "x" for which you don't know the value results in something like this:
"False OR x" --> The "x" is not known, but it could be "True" or "False". Those two possible values result in two different results when substituted for "x" ("False OR True" = "True" and "False OR False" = "False"). So you really don't know what the result of "False OR x" is - it's unknown.
"True OR x" --> The "x" could stand for "True" or "False". Whichever one is used as a value for "x" doesn't matter since "True OR anything" always yields "True".
Like I said, this is an oversimplification, but it's the basic idea.

Ok, that makes sense. I figured there was an easy explanation, but my mind was fried when I read it. However, based on that description wouldn't "False AND Unknown" produce Unknown? :pinch:Oops, never mind, I just looked at the truth table a little more carefully (caffeine is kicking in) and realized that False AND anything is always False (have I mentioned that it has been some time since I worked with truth tables).

(Family Guy is a great show. I think Brian has some of the best lines).

Ian.

"If you are going through hell, keep going."
-- Winston Churchill

Sean_S Newbie Points: 1 More actions · Answer 14

Sean_S

Newbie

Points: 1

April 2, 2008 at 2:16 pm

#797505

Very helpful...thanks!

dbishop Mr or Mrs. 500 Points: 565 More actions · Answer 15

So, when you boil all this down, how do you handle things like DateTerminated, DisconnectDate (specifically DATETIME datatypes) when the event has not occurred. You know that an employee will someday be terminated, or a customer will someday terminate service, but is the use of NULL in this case sonsidered acceptable? I've seen all kinds of workarounds (NULLS, using a false date (1/1/1900) making the date VARCHAR and using ISDATE() logic...)

What is the preferred solution?