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


Table Variable


Table Variable

Author
Message
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66235 Visits: 18570
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

Miles Neale
Miles Neale
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4222 Visits: 1695
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!
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18735 Visits: 12426
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! :-P

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
Revenant
Revenant
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12463 Visits: 5010
Thanks for the question, Sergiy!

And thanks to Hugo for his insightful comments!
antony-688446
antony-688446
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 371
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...
Rob Schripsema
Rob Schripsema
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3193 Visits: 11042
Great question. Learned something....again!

Rob Schripsema
Propack, Inc.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25877 Visits: 12494
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

Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6577 Visits: 2396
Nice question and great explanations from everyone. Thanks!
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25443 Visits: 12464
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).
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25443 Visits: 12464
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.
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