Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»»

Table Variable Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 10:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:57 PM
Points: 21,223, Visits: 14,924
Thanks for the question



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1448131
Posted Tuesday, April 30, 2013 10:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:18 PM
Points: 2,266, Visits: 1,320
Sean Lange (4/30/2013)
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.



Hugo,

Question if you have time.

<furthernitpicking>
Not NULL - Unknown. Is unknown also called uninitialized?
</furthernitpicking>

Just wondering?

Thanks



Not all gray hairs are Dinosaurs!
Post #1448133
Posted Tuesday, April 30, 2013 11:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 5,925, Visits: 8,173
Miles Neale (4/30/2013)
Hugo,

Question if you have time.

<furthernitpicking>
Not NULL - Unknown. Is unknown also called uninitialized?
</furthernitpicking>

Just wondering?

This will cost you more time than it'll cost me!

Short answer: no.

Long answer:
1. NULL - The database's black hole
2. The logic of three-valued logic
3. Dr. Unknown, or how I learned to stop worrying and love the NULL
4. What if null if null is null null null is null?

Enjoy!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1448156
Posted Tuesday, April 30, 2013 12:26 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 4,386, Visits: 3,396
Thanks for the question, Sergiy!

And thanks to Hugo for his insightful comments!
Post #1448195
Posted Tuesday, April 30, 2013 3:03 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:53 PM
Points: 262, Visits: 300
For those struggling with the difference between 'False' and 'Unknown', and how the IF statement works, consider what happens if we change the example code a little bit (I think this could have added a couple of wrong answers to the results )
DECLARE @ToCreate bit;

IF @ToCreate <> 1
DECLARE @Table TABLE
(id int, name varchar(50) )
ELSE
INSERT INTO @Table (id, name)
select 1, 'a';

SELECT * FROM @Table;

It now looks like the DECLARE should be executed, as @ToCreate is clearly not 1, but it isn't, as the comparison evaluates to 'Unknown'. This forces the ELSE part of the IF statement.

Hope this helps to clarify why Tom's whinge on BOL is important!

And, on the subject of missing information on BOL, I couldn't find any reference to when variables are created on the link provided...
Post #1448251
Posted Tuesday, April 30, 2013 3:48 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:48 AM
Points: 1,935, Visits: 10,956
Great question. Learned something....again!


Rob Schripsema
Accelitec, Inc
Post #1448262
Posted Tuesday, April 30, 2013 4:33 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 8,562, Visits: 9,057
ronmoses (4/30/2013)
So here's a question. If variables are initialized at parsing time - I take this to mean that the DECLARE is interpreted prior to the INSERT - why can't you declare a variable later in the code than it's used? You can't swap out the IF and ELSE branches in this example. Not that you'd want to do it, it's just an academic question.

ron

Oh, variables are set to null, not initialised, at parse time; initialisation to a real value (as opposed to leaving the variable marked as not having a real value at all - ie null) can only happen at execution time, even when that initialisation is syntactically part of the statement which declares the variable. Declarations happen when the smallest statement (so for example not an if...else statement, but one of the statements which are [sub]components of it) which includes the declaration is parsed, and parsing goes strictly to lines from top to bottom and within a line to characters from left to right, so the scope begins at the declaration and ends at the end of the batch. There is yet another (see my post a few posts above for the first one) error in BOL that claims that the scope is the batch in which the variable is declared; that is wrong, the scope begins at the declaration, not at the beginning of the batch - we know this is true because the parser will object to any use of the variable anywhere earlier in the batch than the declaration.

To see that initialisation is at execution time not at parse try the following code:-
declare  @k int = 1;
if @k = 0 begin declare @j int = 2 end else if @j = 2 begin declare @i int = 3 end
select @i, @j, @k

The values selected are NULL, NULL, 1. So the declarations of @j and @i occured while the if...else statement was being parsed (since execution reaches neither of the declarations) but their initialisations - even though they were written as parts of the statements that contained the declarations - were not executed because execution didn't reach the declaration statements.

edit: I think this is the first QotD that I've seen which managed to point up two BOL errors.


Tom
Post #1448266
Posted Tuesday, April 30, 2013 4:44 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:34 AM
Points: 3,261, Visits: 1,955
Nice question and great explanations from everyone. Thanks!
Post #1448269
Posted Tuesday, April 30, 2013 6:29 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
antony-688446 (4/30/2013)
For those struggling with the difference between 'False' and 'Unknown', and how the IF statement works, consider what happens if we change the example code a little bit (I think this could have added a couple of wrong answers to the results )
DECLARE @ToCreate bit;

IF @ToCreate <> 1
DECLARE @Table TABLE
(id int, name varchar(50) )
ELSE
INSERT INTO @Table (id, name)
select 1, 'a';

SELECT * FROM @Table;

It now looks like the DECLARE should be executed, as @ToCreate is clearly not 1, but it isn't, as the comparison evaluates to 'Unknown'. This forces the ELSE part of the IF statement.

Hope this helps to clarify why Tom's whinge on BOL is important!

And, on the subject of missing information on BOL, I couldn't find any reference to when variables are created on the link provided...


Actually, DECLARE is executed.
It's executed in both version of the batch - original from the question and your, modifies one.
It will be executed even if you set @ToCreate to "non-executable" value:

DECLARE @ToCreate bit;
SET @ToCreate = 1
IF @ToCreate <> 1
DECLARE @Table TABLE
(id int, name varchar(50) )
ELSE
INSERT INTO @Table (id, name)
select 1, 'a';

SELECT * FROM @Table;

The whole point of the question was - DECLARE is executed regardless of the flow control logic in the code.
Any outcome of IF check - TRUE, FALSE or UNKNOWN - won't affect execution of DECLARE.

The comment would be correct if we put any other "run-time" command in place of DECLARE - SET, SELECT, etc.

ELSE is executed only when IF returns not TRUE (FALSE or NULL).
Post #1448281
Posted Tuesday, April 30, 2013 6:40 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
ronmoses (4/30/2013)
[quote]Bob Cullen-434885 (4/30/2013)
So here's a question. If variables are initialized at parsing time - I take this to mean that the DECLARE is interpreted prior to the INSERT - why can't you declare a variable later in the code than it's used? You can't swap out the IF and ELSE branches in this example. Not that you'd want to do it, it's just an academic question.

ron


Because the code will fail during parse time - it references an object which does not exist.
Parser will just stop reporting an error and never reach the line in code where the variable is declared.

Therefore it will never get to compilation stage when DECLARE would be executed and @Variable could be materialised in memory.
Post #1448283
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse