March 22, 2012 at 8:16 am
My colleague told me this weired thing that he observed in T-SQL. I am just wondering why is that so.
See the below simple loop.
A variable @test-2 is declared inside the loop. I am expecting that when the variable is declared inside the loop it should get reset everytime the loop runs. But it retains the value from the first execution.
DECLARE @cnt INT
SET @cnt = 1
WHILE( @cnt <= 2)
BEGIN
DECLARE @test-2 INT
SELECT @test-2
SELECT @test-2 = 10
SELECT @test-2
SET @cnt = @cnt + 1
END
the loop returns
NULL
10
10
10
I was expecting
NULL
10
NULL
10
Why the variable is not resetted when the loop runs the second time?:unsure:
-Vikas Bindra
March 22, 2012 at 8:30 am
the variable lasts for the scope of the batch, so as this is one batch the variable is never reset, you would have to drop out of the loop and finish the batch before the variable is removed from memory
The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared. For example, the following script generates a syntax error because the variable is declared in one batch and referenced in another:
March 22, 2012 at 8:31 am
In a short: because T-SQL is not the same as C# :hehe:
It doesn't mater where you declare your variable, inside or outside of the loop. Declaration will only happen once and it will work as long as you declare a variable before its first use.
However!
In SQL Server 2008, you can declare and initialise the variable at once.
So if you would, inside of your loop, declare and initialise it as:
DECLARE @test-2 INT = NULL
then you will get your "expected" results...
March 22, 2012 at 8:43 am
Thanks guys for clarification. Got it now.
-Vikas Bindra
March 22, 2012 at 1:55 pm
Even better, don't use loops. :hehe:
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 22, 2012 at 2:23 pm
Sean Lange (3/22/2012)
Even better, don't use loops. :hehe:
i was waiting for that one. :w00t:
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 22, 2012 at 2:34 pm
capn.hector (3/22/2012)
Sean Lange (3/22/2012)
Even better, don't use loops. :hehe:i was waiting for that one. :w00t:
Somebody had to say it...and I was kind of surprised that neither Eugene nor Anthony said it. 😉
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 22, 2012 at 3:01 pm
Sean Lange (3/22/2012)
capn.hector (3/22/2012)
Sean Lange (3/22/2012)
Even better, don't use loops. :hehe:i was waiting for that one. :w00t:
Somebody had to say it...and I was kind of surprised that neither Eugene nor Anthony said it. 😉
or lynn or jeff?
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 22, 2012 at 3:03 pm
capn.hector (3/22/2012)
Sean Lange (3/22/2012)
capn.hector (3/22/2012)
Sean Lange (3/22/2012)
Even better, don't use loops. :hehe:i was waiting for that one. :w00t:
Somebody had to say it...and I was kind of surprised that neither Eugene nor Anthony said it. 😉
or lynn or jeff?
I guess I just happened to find this thread before they did. 😎
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 22, 2012 at 10:13 pm
Sean Lange (3/22/2012)
capn.hector (3/22/2012)
Sean Lange (3/22/2012)
capn.hector (3/22/2012)
Sean Lange (3/22/2012)
Even better, don't use loops. :hehe:i was waiting for that one. :w00t:
Somebody had to say it...and I was kind of surprised that neither Eugene nor Anthony said it. 😉
or lynn or jeff?
I guess I just happened to find this thread before they did. 😎
I was just getting ready to hit "Post Quoted Reply" when I decided to scroll down and see if anyone else said it. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2012 at 5:49 am
Jeff Moden (3/22/2012)
Sean Lange (3/22/2012)
capn.hector (3/22/2012)
Sean Lange (3/22/2012)
capn.hector (3/22/2012)
Sean Lange (3/22/2012)
Even better, don't use loops. :hehe:i was waiting for that one. :w00t:
Somebody had to say it...and I was kind of surprised that neither Eugene nor Anthony said it. 😉
or lynn or jeff?
I guess I just happened to find this thread before they did. 😎
I was just getting ready to hit "Post Quoted Reply" when I decided to scroll down and see if anyone else said it. 🙂
Please! "Even better, don't use loops."? Why? :w00t:
I do not agree!
If you say that, you should say it right! Something like:
Don't use loops where they are avoidable and therefore may not be needed!
What about:
admin processes?
generating dynamic sql?
and, at the end, some scalar UDF functions (one to replace GUID's as in the recent post by Jeff? http://www.sqlservercentral.com/Forums/Topic1269889-392-1.aspx );-)
March 23, 2012 at 7:08 am
Eugene Elutin (3/23/2012)
Jeff Moden (3/22/2012)
Sean Lange (3/22/2012)
capn.hector (3/22/2012)
Sean Lange (3/22/2012)
capn.hector (3/22/2012)
Sean Lange (3/22/2012)
Even better, don't use loops. :hehe:i was waiting for that one. :w00t:
Somebody had to say it...and I was kind of surprised that neither Eugene nor Anthony said it. 😉
or lynn or jeff?
I guess I just happened to find this thread before they did. 😎
I was just getting ready to hit "Post Quoted Reply" when I decided to scroll down and see if anyone else said it. 🙂
Please! "Even better, don't use loops."? Why? :w00t:
I do not agree!
If you say that, you should say it right! Something like:
Don't use loops where they are avoidable and therefore may not be needed!
What about:
admin processes?
generating dynamic sql?
and, at the end, some scalar UDF functions (one to replace GUID's as in the recent post by Jeff? http://www.sqlservercentral.com/Forums/Topic1269889-392-1.aspx );-)
Loops aren't a bad paradigm. Set oriented programming's saving grace is that it does allow amortizing T-SQL's poor translator performance over the life of the job, and Microsoft has decided that T-SQL needs no additional investment performancewise. They have a point, theres been a native code interface in the server for a while, and now we have dot net assemblies available.
The tough performance seems to come down to the fact that T-SQL is translated once for every time it is executed, and queries are only executed once per batch, even if they're recursive CTEs, whereas with loops statements are repeatedly translated. Cached plans seem to reuse translations in a manner of speaking.
Its not that set oriented programming is so good (because it is rbar under the hood anyways, you just don't have to spec it), its just that T-SQL is really that bad.
March 23, 2012 at 7:19 am
Please! "Even better, don't use loops."? Why? :w00t:
I do not agree!
If you say that, you should say it right!
OK, don't use loops in general. They tend to be horribly slow. In almost every situation you can find a set based solution that will blow the doors off RBAR processing. There are a few exceptions but these are pretty few and far between.
Better? 😀
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 23, 2012 at 7:43 am
Sean Lange (3/23/2012)
Please! "Even better, don't use loops."? Why? :w00t:
I do not agree!
If you say that, you should say it right!
OK, don't use loops in general. They tend to be horribly slow. In almost every situation you can find a set based solution that will blow the doors off RBAR processing. There are a few exceptions but these are pretty few and far between.
Better? 😀
Better, but still, worded to harsh to using loops... As there are quite "a few"
exceptions here...
Again, I'm totally agree with everyone (in right mind) about set-based solutions in T-SQL. Whenever they are possible, they almost always will "blow the doors off" loop based ones.
March 23, 2012 at 7:56 am
Eugene Elutin (3/23/2012)
Sean Lange (3/23/2012)
Please! "Even better, don't use loops."? Why? :w00t:
I do not agree!
If you say that, you should say it right!
OK, don't use loops in general. They tend to be horribly slow. In almost every situation you can find a set based solution that will blow the doors off RBAR processing. There are a few exceptions but these are pretty few and far between.
Better? 😀
Better, but still, worded to harsh to using loops... As there are quite "a few"
exceptions here...
Again, I'm totally agree with everyone (in right mind) about set-based solutions in T-SQL. Whenever they are possible, they almost always will "blow the doors off" loop based ones.
Well in my experience around here the amount of times when a loop was actually required is rather small. Yes things like some admin tasks (update all tables, do something on every database, there are a few others) but in general they really should be avoided.
It seems to be a common thing that a lot of people just immediately think they need to loop because they don't know how to do some things set based. For that matter the schools should stop teaching them, let the admin types discover how to loop when it is actually needed.
Yes I agree my position is probably a bit harsh, but in general before I would support a decision of using a loop I would like to know why it must be a loop.
Now that we have totally taken over the OP's thread...it was certainly a valid point the OP made and one that should be considered in those very rare (at least imho) times you need to loop, make sure you declare your variables outside the loop.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply