March 23, 2012 at 8:18 am
... make sure you declare your variables outside the loop.
yeah, it is widely used practice by T-SQL programmers.
However, from performance prospective, it never was any difference as the query optimizer would make all required variable reservations at the start of the batch.
But, since SQLServer2008 now allows to declare and initialise the variable in one statement, declaring and initialising variables inside the loop (DECLARE @var int = null) looks fine to me. In this case, variable will be re-initialised to the requested value at every loop iteration.
I cannot be sure for 100%, but I do believe that in this scenario, SQLServer still reserves variable once and simply resets it in the loop.
Therefore, I guess the "coding standards" now can be amended to state something like:
It is a good practice in T-SQL to declare all your variables at the beginning of the SQL batch (eg. programming objects) and outside of the loops. However, where you find declaring the variable inside the loop is more representative, make sure you declare it with immediate initialisation.
What do you think?
March 23, 2012 at 9:04 am
Sean Lange (3/23/2012)
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.
The biggest problem is that most people coming into the database world are coming from the application world and they think "I need to do this for each row" instead of "what do I want to do to this column."
It is a paradigm shift that is difficult for some to make, sort of like moving from 3rd generation programming languages like COBOL to object-oriented languages.
March 23, 2012 at 9:07 am
...
It is a paradigm shift that is difficult for some to make, sort of like moving from 3rd generation programming languages like COBOL to object-oriented languages.
Is it really such difficult? Try this one:
http://www.amazon.co.uk/Object-Oriented-COBOL-SIGS-Advances-Technology/dp/0132611406
:hehe:
March 23, 2012 at 9:10 am
Eugene Elutin (3/23/2012)
...
It is a paradigm shift that is difficult for some to make, sort of like moving from 3rd generation programming languages like COBOL to object-oriented languages.
Is it really such difficult? Try this one:
http://www.amazon.co.uk/Object-Oriented-COBOL-SIGS-Advances-Technology/dp/0132611406
:hehe:
For some people, yes.
March 23, 2012 at 9:11 am
Eugene Elutin (3/23/2012)
...
It is a paradigm shift that is difficult for some to make, sort of like moving from 3rd generation programming languages like COBOL to object-oriented languages.
Is it really such difficult? Try this one:
http://www.amazon.co.uk/Object-Oriented-COBOL-SIGS-Advances-Technology/dp/0132611406
:hehe:
Object oriented COBOL is a perfect example of what we talking about. Yes it can be done, but that does not mean it is a good idea. Oh wait...that sounds exactly like looping in t-sql.
_______________________________________________________________
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 9:22 am
Sean Lange (3/23/2012)
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.
I totally agree with you all about the usage of loops in T-SQL. I too rarely use loops, because I know the impact.
But yes, I agree that I was not aware about why the variable is not getting reset (as mentioned in original post), but it is clear now:-)
-Vikas Bindra
March 23, 2012 at 9:32 am
...
Object oriented COBOL is a perfect example of what we talking about. Yes it can be done, but that does not mean it is a good idea...
It is not as bad as it may look...
From http://www.c-sharpcorner.com/UploadFile/rickmalek/Art02-OOIntro12052005040650AM/Art02-OOIntro.aspx
It
enhances the productivity of organizations that use COBOL. Not only will COBOL programmers have access to a wide variety of new technologies, including .NET class libraries and ASP.NET, they will be able to work seamlessly with developers of code in other languages, such as C++ and Visual Basic. Instead of having a development organization with a schism between COBOL programmers and other programmers, the new environment allows programmers to bridge the barriers using common interfaces and programming tools. The transition to OO COBOL opens up the world for COBOL to be accessed by many other languages and to open itself up as well. Within .NET an OOCOBOL program can inherit from any other language or be inherited. Business logic will no longer "have to be rewritten because it's in COBOL", but merely recompiled and made accessible.
You can believe me, there is a significant number of production core systems written in COBOL which are currently in use and not planned to be replaced in any time soon...
March 23, 2012 at 9:44 am
Eugene Elutin (3/23/2012)
...
Object oriented COBOL is a perfect example of what we talking about. Yes it can be done, but that does not mean it is a good idea...
It is not as bad as it may look...
From http://www.c-sharpcorner.com/UploadFile/rickmalek/Art02-OOIntro12052005040650AM/Art02-OOIntro.aspx
It
enhances the productivity of organizations that use COBOL. Not only will COBOL programmers have access to a wide variety of new technologies, including .NET class libraries and ASP.NET, they will be able to work seamlessly with developers of code in other languages, such as C++ and Visual Basic. Instead of having a development organization with a schism between COBOL programmers and other programmers, the new environment allows programmers to bridge the barriers using common interfaces and programming tools. The transition to OO COBOL opens up the world for COBOL to be accessed by many other languages and to open itself up as well. Within .NET an OOCOBOL program can inherit from any other language or be inherited. Business logic will no longer "have to be rewritten because it's in COBOL", but merely recompiled and made accessible.
You can believe me, there is a significant number of production core systems written in COBOL which are currently in use and not planned to be replaced in any time soon...
I actually was a consultant on a project before Y2K that was using OO-COBOL in a cgi program for an online shopping cart. :w00t:
_______________________________________________________________
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 24, 2012 at 12:58 pm
Eugene Elutin (3/23/2012)
...
Instead of having a development organization with a schism between COBOL programmers and other programmers, the new environment allows programmers to bridge the barriers using common interfaces and programming tools.
Now all we have to do is eleminate the huge schism between most of the application Developers I've run into and database Developers/DBAs. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 24, 2012 at 1:03 pm
Lynn Pettis (3/23/2012)
Sean Lange (3/23/2012)
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.
The biggest problem is that most people coming into the database world are coming from the application world and they think "I need to do this for each row" instead of "what do I want to do to this column."
It is a paradigm shift that is difficult for some to make, sort of like moving from 3rd generation programming languages like COBOL to object-oriented languages.
Absolute agreed! It IS the single most important thing to remember when making the shift from procedural languages to declarative languages like T-SQL which is why I even carry it in my signature line.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2012 at 2:25 pm
Jeff Moden (3/24/2012)
Lynn Pettis (3/23/2012)
Sean Lange (3/23/2012)
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.
The biggest problem is that most people coming into the database world are coming from the application world and they think "I need to do this for each row" instead of "what do I want to do to this column."
It is a paradigm shift that is difficult for some to make, sort of like moving from 3rd generation programming languages like COBOL to object-oriented languages.
Absolute agreed! It IS the single most important thing to remember when making the shift from procedural languages to declarative languages like T-SQL which is why I even carry it in my signature line.
The problem comes when you have to define a declarative language that does everything that needs doing. With procedural languages, you pretty much have a set number of operations to implement, after that its additions for programming efficiency. Witness the quirky update for the declarative counterpoint to this.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply