Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Variable


Table Variable

Author
Message
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 996
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
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8329 Visits: 11580
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Sean Lange
Sean Lange
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: 16624 Visits: 17024
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 Moden's 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
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: 4109 Visits: 72512
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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1887 Visits: 278
Good question, learned something new today.
Thanks,
Lon
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8329 Visits: 11580
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Sean Lange
Sean Lange
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: 16624 Visits: 17024
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 Moden's 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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2164 Visits: 487
Nice!
webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3035 Visits: 3755
Cool question. Thanks.

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

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