Table Variable

Question

Post reply

Table Variable

Viewing 15 posts - 46 through 60 (of 60 total)

You must be logged in to reply to this topic. Login to reply

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 1

Great question. Learned something....again!

Rob Schripsema
Propack, Inc.

TomThomson SSC Guru Points: 104773 More actions · Answer 2

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 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-2 int = 2 end else if @j-2 = 2 begin declare @i int = 3 end

select @i, @j-2, @k

The values selected are NULL, NULL, 1. So the declarations of @j-2 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 SSCoach Points: 17191 More actions · Answer 3

Nice question and great explanations from everyone. Thanks!

Sergiy SSC Guru Points: 110208 More actions · Answer 4

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).

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 5

ronmoses (4/30/2013)

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.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 6

L' Eomot Inversé (4/30/2013)
edit: I think this is the first QotD that I've seen which managed to point up two BOL errors.

There were times when you could teach students by BOL...

Oh, good ol' times....

:doze:

_____________
Code for TallyGenerator

antony-688446 Ten Centuries Points: 1221 More actions · Answer 7

Sergiy (4/30/2013)
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).

I understand that 🙂 I didn't explain my example thoroughly enough - I was trying to provide an example to Tom's explanation of tri-state logic. In my example, it looks like the condition should be met, but it isn't...

Technically, the DECLARE isn't executed - see the other example around initialising a variable as part of the DECLARE. The statement is passed, and the variable is created, but the statement itself is NOT executed, so maybe I was correct. Depends on the definition of EXECUTED....

Sergiy SSC Guru Points: 110208 More actions · Answer 8

antony-688446 (4/30/2013)
I understand that 🙂 I didn't explain my example thoroughly enough - I was trying to provide an example to Tom's explanation of tri-state logic. In my example, it looks like the condition should be met, but it isn't...

I understand that too. 🙂

And I agree with the logic of your explanation.

That's why I added this there:

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

Technically, the DECLARE isn't executed - see the other example around initialising a variable as part of the DECLARE. The statement is passed, and the variable is created, but the statement itself is NOT executed, so maybe I was correct. Depends on the definition of EXECUTED....

Well, memory is allocated, references are created, for table variable even an object created in tempdb - some actions are taken.

I'd say it's executed.

Just not on the run time but during compilation stage.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 9

antony-688446 (4/30/2013)
see the other example around initialising a variable as part of the DECLARE. The statement is passed, and the variable is created, but the statement itself is NOT executed

That example is actually very good to prove the point but may be very confusing because of the "shortcut" syntax.

if @k = 0 begin declare @j-2 int = 2 end

Since @k = 1 the block "begin-end" is not executed.

Right?

Wrong. Otherwise variable @j-2 would not be declared and we would get "must declare variable @j-2" error further in the code.

So, it is executed. Right?

Wrong. Because @j-2 does not get initialised with value 2.

What's the... ?

Explanation is simple.

There are actually 2 statements combined together:

declare @j-2 int set @j-2 = 2

Now it all becomes clear:

- DECLARE is executed during compilation, outside of IF..THEN control;

- SET is executed during run time, under IF..THEN control.

Actually, this syntax is one of the worst and most dangerous things introduced by MS in SQL2008.

I saw extremely powerful servers brought down by improper use of this syntax.

Not just tables, not databases. Whole servers, with losing ability to connect to master database.

I probably need to write an article about that experience, if nobody did it before.

_____________
Code for TallyGenerator

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Sergiy (4/30/2013)
Actually, this syntax is one of the worst and most dangerous things introduced by MS in SQL2008.
I saw extremely powerful servers brought down by improper use of this syntax.
Not just tables, not databases. Whole servers, with losing ability to connect to master database.
I probably need to write an article about that experience, if nobody did it before.

I think that would be a useful article. Might make people understand a little more about language design issues generally as well as in T-SQL.

Tom

Mike Dougherty-384281 SSCrazy Points: 2794 More actions · Answer 11

L' Eomot Inversé (5/1/2013)

Sergiy (4/30/2013)
antony-688446 (4/30/2013)
Actually, this syntax is one of the worst and most dangerous things introduced by MS in SQL2008.
I saw extremely powerful servers brought down by improper use of this syntax.
Not just tables, not databases. Whole servers, with losing ability to connect to master database.
I probably need to write an article about that experience, if nobody did it before.
I think that would be a useful article. Might make people understand a little more about language design issues generally as well as in T-SQL.

I second that. It sounds like a great story.

Jeff Moden SSC Guru Points: 1004053 More actions · Answer 12

VERY cool question, ol' friend. I really had to stop and think about it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Divine Flame SSCoach Points: 15943 More actions · Answer 13

Divine Flame

SSCoach

Points: 15943

May 2, 2013 at 4:47 am

#1611842

Nice question. Thanks.

Sujeet Singh

manik_anu SSCrazy Points: 2377 More actions · Answer 14

nice question...

Manik
You cannot get to the top by sitting on your bottom.

David Conn SSCertifiable Points: 6010 More actions · Answer 15

Good Discussion on IF...ELSE and Nulls.

I have to agree with Sean.

The IF isn't TRUE therefore execute the ELSE. This is what happened so no problem.

The Variable had no Value therefore it wasn't 1 therefore the IF condition wasn't met. Therefore use the ELSE.

Regards

David