July 30, 2014 at 6:44 am
But you still can't use a variable before you declare it...
This 'logic' makes my head hurt.
July 30, 2014 at 6:51 am
Xavon (7/30/2014)
But you still can't use a variable before you declare it...This 'logic' makes my head hurt.
When that error is picked up, its actually checking to see if its defined, remember the source code is still being translated into a form the computer will actually read from when executing. Some interpreters start executing immediately, I'm hazarding a guess that SQL Server doesn't!
http://en.wikipedia.org/wiki/Translator_(computing)
http://en.wikipedia.org/wiki/Compiler probably a better article
July 30, 2014 at 6:57 am
patrickmcginnis59 10839 (7/30/2014)
Xavon (7/30/2014)
But you still can't use a variable before you declare it...This 'logic' makes my head hurt.
When that error is picked up, its actually checking to see if its defined, remember the source code is still being translated into a form the computer will actually read from when executing. Some interpreters start executing immediately, I'm hazarding a guess that SQL Server doesn't!
Oh I get it. Still gives me a headache.
July 30, 2014 at 7:01 am
Xavon (7/30/2014)
patrickmcginnis59 10839 (7/30/2014)
Xavon (7/30/2014)
But you still can't use a variable before you declare it...This 'logic' makes my head hurt.
When that error is picked up, its actually checking to see if its defined, remember the source code is still being translated into a form the computer will actually read from when executing. Some interpreters start executing immediately, I'm hazarding a guess that SQL Server doesn't!
Oh I get it. Still gives me a headache.
Oh, sorry about that! More appropriate link then:
July 30, 2014 at 7:04 am
patrickmcginnis59 10839 (7/30/2014)
Some interpreters start executing immediately, I'm hazarding a guess that SQL Server doesn't!
Correct. SQL Server will parse and compile the entire batch (and the compiling part includes generating execution plans for all queries), and only start executing when the compilation is done.
(And then, during execution, some conditions may trigger a recompilation of specific statements - if there are reasons to believe that intermittent changes may result in poor performance of the original execution plan)
July 30, 2014 at 8:00 am
Nice question, Sreepathi, thanks!
July 30, 2014 at 8:48 am
Thanks for the question. I've never given much thought to the topic, so it's been good to dig around and learn something new. Thanks to everyone who added to the discussion on this topic.
July 30, 2014 at 11:04 am
Nice - thanks, Sreepathi!
July 30, 2014 at 11:57 am
Xavon (7/30/2014)
patrickmcginnis59 10839 (7/30/2014)
Xavon (7/30/2014)
But you still can't use a variable before you declare it...This 'logic' makes my head hurt.
When that error is picked up, its actually checking to see if its defined, remember the source code is still being translated into a form the computer will actually read from when executing. Some interpreters start executing immediately, I'm hazarding a guess that SQL Server doesn't!
Oh I get it. Still gives me a headache.
Maybe this will help your headache (to get worse :hehe:)
GOTO Loop
Declaration:
PRINT 'DECLARE @i'
DECLARE @i INT = 1
GOTO Finish
Loop:
PRINT 'Loop'
WHILE @i < 10 -- While loop will not be entered. Can you guess why?
BEGIN
PRINT 'DECLARE @j-2'
DECLARE @j-2 AS TABLE
(
i INT
)
SET @i = @i + 1
INSERT INTO @j-2
SELECT @i
END
GOTO Declaration
Finish:
PRINT 'Finish'
SELECT * FROM @j-2
July 30, 2014 at 1:00 pm
Nice question, correct answer, appallingly wrong explanation.
The reason this works is nothing to do with scope (which isn't the batch unless the declaration is the first statement in the batch, so the explanation was wrong on that too).
The reason that it works is that all declarations are executed when the batch is parsed (so that the parser can know what variables are in scope and what their types are at every point in the source code without requiring multiple passes), they are not exectuted at run time, so being in the body of a loop doesn't mean the declaration gets executed each time round a loop.
Its important to note that something like declare @i int = @j-2*@k;is not a declaration, it's not even a single statement, it stands for the two statementsdeclare @i int;
set @i=@j*@k;which are a declaration and an assignment; if it occurrs in the body of a loop, the declaration is executed once (at parse time) but the assignment is executed every time round the loop (which might be never).
edit: (spoiler:) and Luis' post above gives a perfect example of the "never" case.
edit; fix []s
Tom
July 31, 2014 at 12:27 am
Good question
-Vijred (http://vijredblog.wordpress.com)
July 31, 2014 at 2:39 am
Nice one
Thanks
August 6, 2014 at 7:10 am
TomThomson (7/30/2014)
Nice question, correct answer, appallingly wrong explanation.The reason this works is nothing to do with scope (which isn't the batch unless the declaration is the first statement in the batch, so the explanation was wrong on that too).
The reason that it works is that all declarations are executed when the batch is parsed (so that the parser can know what variables are in scope and what their types are at every point in the source code without requiring multiple passes), they are not exectuted at run time, so being in the body of a loop doesn't mean the declaration gets executed each time round a loop.
Its important to note that something like
declare @i int = @j-2*@k;is not a declaration, it's not even a single statement, it stands for the two statementsdeclare @i int;set @i=@j*@k;which are a declaration and an assignment; if it occurrs in the body of a loop, the declaration is executed once (at parse time) but the assignment is executed every time round the loop (which might be never).
Hi Tom , that sounds interesting , I wander if this is documented somewhere.
I based my answer on the feeling that table variables might work somehow as temporary tables so not necessary dependent of the scope of the loop. I also have to admit I have not checked the docs on this so I was lucky to answer correctly.
But now I am curious how table vars are implemented.
Cheers,
Iulian
August 6, 2014 at 1:28 pm
Iulian -207023 (8/6/2014)
Hi Tom , that sounds interesting , I wander if this is documented somewhere.
I don't think it's documented. And of course it's slightly more complicated than that, because the scoping rules are somewhat screwed up for commas separated declarations like declare @i int - 7, @j-2 int = @i*3 ; in that none of the variables declared in any of the comma separated declarations is in scope on the RHS of any of the associated comma separated assignments, so my example will complain that @i does not exist when it parses the second declaration and assignment.
I guess the best way of seeing how table variables work will be to run declarations and assignments one statement at a time and look in tempdb to see what is happening. A good rule of thumb is don't believe what you see in blogs about table variables unless you know that the author is someone you can trust (like one of sqlkiwi, gilamonster, sqlrnnr, jeff moden) because there are a lot of popular myths about them.
Tom
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply