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


Output of Query


Output of Query

Author
Message
Daniel Bowlin
Daniel Bowlin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4114 Visits: 2629
Regular reiteration of how nulls behave is very useful in QoTD. Thanks.
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2694 Visits: 2204
Thanks for the question!
SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 1046
Hugo Kornelis (10/28/2010)
Good question, but a small (yet significan) mistake in the explanation.
NULL does not represent the value 'UNKNOWN'.


Hugo, perhaps you could ask M$ to update the books online to match your opinion?

<Quote from BOL>
When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, the following comparison always yields UNKNOWN when ANSI_NULLS is ON:

Copyytd_sales > NULL

The following comparison also yields UNKNOWN any time the variable contains the value NULL:

ytd_sales > @MyVariable

<Quote from BOL>

Youre comment is correct with one small but significant correction.
When ANSI_NULLS is OFF NULL is not value UNKNOWN.
SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 1046
Great question Sunil.
Good example of how NULLS are counted in a column of data.

I also give this question 5 stars for having no typos or confusion in the code, question, or answers!

Cool
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11044 Visits: 11997
SanDroid (10/28/2010)
Hugo Kornelis (10/28/2010)
Good question, but a small (yet significan) mistake in the explanation.
NULL does not represent the value 'UNKNOWN'.


Hugo, perhaps you could ask M$ to update the books online to match your opinion?

<Quote from BOL>
When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, the following comparison always yields UNKNOWN when ANSI_NULLS is ON:

Copyytd_sales > NULL

The following comparison also yields UNKNOWN any time the variable contains the value NULL:

ytd_sales > @MyVariable

<Quote from BOL>


I wrote that NULL does not represent unknown. I also wrote that a comparison to NULL does result in the truth value Unknown.
You quote an excerpt from BOL that states that comparisons in which one or more of the operands are NULL yield Unknown. I fail to see the mismatch between the BOL quote and my post.

That being said, BOL does contain lots of errors - it is an excellent reference for the product SQL Server, but it is far from perfect when it comes to relational theory.

Youre comment is correct with one small but significant correction.
When ANSI_NULLS is OFF NULL is not value UNKNOWN.

I already admitted in a previous post that I never use ANSI_NULLS off. (And that no one should, really)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 1046
Hugo Kornelis (10/28/2010)
[quote]I wrote that NULL does not represent unknown.


I understand that is what you wrote.
I thought maybe you would look at the referenced article before responding.

Let me grab another cut and paste of the BOL that was referenced for the question.
"The value NULL means the data value for the column is unknown or not available."

That is the very first sentance from the Books online:
http://msdn.microsoft.com/en-us/library/ms191270%28v=SQL.100%29.aspx

Now I understand that the Books Online have often been wrong.
Do you believe they are in this instance?
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11044 Visits: 11997
SanDroid (10/28/2010)
Hugo Kornelis (10/28/2010)
[quote]I wrote that NULL does not represent unknown.


I understand that is what you wrote.
I thought maybe you would look at the referenced article before responding.


Since you didn't provide a reference and didn't indicate in any way that you wanted me to comment on the part you didn't quote, I could only assume that the quoted text was what you wanted me to comment on.

Let me grab another cut and paste of the BOL that was referenced for the question.
"The value NULL means the data value for the column is unknown or not available."

That is the very first sentance from the Books online:
http://msdn.microsoft.com/en-us/library/ms191270%28v=SQL.100%29.aspx

Now I understand that the Books Online have often been wrong.
Do you believe they are in this instance?

Thanks for the reference. I have now read the entire article.
I qualify it as sloppy, not as wrong. The first line you quote above is "sort of" correct. Since NULL represents missing data, "not available" is spot-on. The additional "unknown" in that sentence is superfluous, a bit like describing someone as "New Yorker or American" - unknown is a subset of missing value, as New Yorker is a subset of American.

In the paragraph on camparing NULL values, I find this sentence, as an explanation of why NULL comparisons yield UNKNOWN:
"This is because a value that is unknown cannot be compared logically against any other value."
The "value is unknown" here is the old incorrect interpretation of NULL. But after the introductory paragraph above, this is easily identified as a sloppy reference, and it won't be taken as a change to the previous explanation.

This kind of sloppiness annoys me. But I count my blessings - I've seen BOL pages that were much, much worse than this one.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32527 Visits: 18556
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5278 Visits: 4076
Thanks to OP and special thanks to HUGO for great explanation.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
cthomas1975
cthomas1975
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: 5
Um, NO! You have inverted the condition on the <> '1' to = '1' with this "IN" statement.
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