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


Table Variable


Table Variable

Author
Message
ronmoses
ronmoses
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2610 Visits: 1023
Sean Lange (4/30/2013)
I don't think I follow you here Tom. The condition checks is @ToCreate = 1. It does not equal 1 because it is NULL. Therefore the boolean check would be false. How could it return unknown? It is very clearly known that @ToCreate is not 1.

I believe he's in agreement with you, but indicating that the documentation says otherwise.

ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Hugo Kornelis
Hugo Kornelis
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34279 Visits: 13112
Sean Lange (4/30/2013)
I don't think I follow you here Tom. The condition checks is @ToCreate = 1. It does not equal 1 because it is NULL. Therefore the boolean check would be false.

SQL uses three-valued logic, not true Boolean logic. Any comparison with a NULL (except when using IS [NOT] NULL) will not result in True or False, but in Unknown.

For WHERE and IF, a logic result of True qualifies, and False and Unknown do not qualify.
For CHECK constraints, a logic result of True or Unknown qualifies and False doesn't.


Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Sean Lange
Sean Lange
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148194 Visits: 18571
Hugo Kornelis (4/30/2013)
Sean Lange (4/30/2013)
I don't think I follow you here Tom. The condition checks is @ToCreate = 1. It does not equal 1 because it is NULL. Therefore the boolean check would be false.

SQL uses three-valued logic, not true Boolean logic. Any comparison with a NULL (except when using IS [NOT] NULL) will not result in True or False, but in Unknown.

For WHERE and IF, a logic result of True qualifies, and False and Unknown do not qualify.
For CHECK constraints, a logic result of True or Unknown qualifies and False doesn't.


I understand the way NULL is evaluated when doing comparisons.


select * from table where MyNullColumn = MyNonColumn.


I understand that returns NULL.

However for the boolean condition in this question, the TRUE condition is not returned. Therefore it will fall to the else. Maybe I am missing the semantics here. Maybe Tom is just splitting hairs about the verbiage of BOL. Pretty sure we are all on the same page.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
mtassin
mtassin
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13256 Visits: 72528
Danny Ocean (4/29/2013)
Good question with important information.


I'll second that... then again my standards are to do any and all declares at the beginning of my SQL Batches/Stored Procs/Functions so I've never run into this... but it is good to know.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Lon-860191
Lon-860191
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2137 Visits: 278
Good question, learned something new today.
Thanks,
Lon
Hugo Kornelis
Hugo Kornelis
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34279 Visits: 13112
Sean Lange (4/30/2013)
I understand the way NULL is evaluated when doing comparisons.


select * from table where MyNullColumn = MyNonColumn.


I understand that returns NULL.


<nitpicking>
Not NULL - Unknown. Quite different concepts! (NULL means the absence of any data; Unknown is a result in three-valued logic that is the result of attempting to do a comparison with data that is absent)
</nitpicking>


However for the boolean condition in this question, the TRUE condition is not returned. Therefore it will fall to the else. Maybe I am missing the semantics here. Maybe Tom is just splitting hairs about the verbiage of BOL. Pretty sure we are all on the same page.

I'm not sure if I'd call it splitting hairs, but Tom is indeed commenting on the BOL verbiage. BOL says that the first statement or block is executed if the expression evaluates to True, and the second (following ELSE) is it evaluates to False. That would imply that neither is executed when it evaluates to Unknown. The correct wording would be either "first statement/block on true, second (following ELSE) otherwise", or "first statement/block on true, second (following ELSE) on false or unknown".


Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Sean Lange
Sean Lange
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148194 Visits: 18571
Hugo Kornelis (4/30/2013)
[quote]
<nitpicking>
Not NULL - Unknown. Quite different concepts! (NULL means the absence of any data; Unknown is a result in three-valued logic that is the result of attempting to do a comparison with data that is absent)
</nitpicking>


My example was a bad one for sure. I am well aware of the three way "boolean" logic in sql.


The correct wording would be either "first statement/block on true, second (following ELSE) otherwise", or "first statement/block on true, second (following ELSE) on false or unknown".


I agree. They should just make it "Otherwise". This is actually what happens. For any reason that the condition is NOT true the ELSE block will execute.

For me, no matter how many ways I try to read the BOL entry it is clear (even given that the reason it is not true is rather ambiguous). Removing the word FALSE in this case would actually add clarity. ;-)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
sneumersky
sneumersky
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5212 Visits: 487
Nice!
webrunner
webrunner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16726 Visits: 4141
Cool question. Thanks.

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
wolfkillj
wolfkillj
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5514 Visits: 2582
Hugo Kornelis (4/30/2013)

For WHERE and IF, a logic result of True qualifies, and False and Unknown do not qualify.
For CHECK constraints, a logic result of True or Unknown qualifies and False doesn't.


Thanks for pointing this out, Hugo. I wonder why MS would inflict this on the unwary:

A CHECK constraint returns TRUE when the condition it is checking is not FALSE for any row in the table.


As Hugo pointed out, SQL Server uses 3-part Boolean logic, where it can't be proven categorically that (NOT FALSE) = TRUE.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
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