I very much agree with the answers you've already received. SQL isn't meant to be a "looping" language. It's designed to work on the concepts of sets. Yes, there are certainly exceptions, but the general rule of thumb is, avoid loops where you can. That said, I'll take a stab at your specific questions.
rajemessage 14195 wrote:
We have a master table, single table which I loop thought, to do some task, the master table is very rarely changed.
So while looping through , I put some records in a temp table and do the looping ?
- Will it help in any case, or can worsen it in any case? (pls consider change in master is quite less and basically new inserts are there)
There is unlikely to be a single definitive answer. Testing is your friend. In addition to trying it with a temporary table, also try it with a table variable. Depending on how you're processing the data, specifically if you're doing JOINs or searches that need statistics to perform well, you may find one mechanism or the other to see good performance.
2) and the main question is, should it be good practice to always make a temp table for looping, even if I have one single table to loop through.
Always? No. Under most circumstances, avoid the loop. After that, sometimes using a cursor is better. Sometimes, table variables or temp tables. Testing and evaluation is necessary because every scenario is different and the query optimizer will make different choices. There just is NOT an "always" beyond, always test.
3) pls consider loops in our case are quite small as far as the number of records are concerned and even the master table is also small,
Small loops are less painful than big ones, but I'd still avoid them where I could.
4) loops are small, but master table can grow to a million.
As was already asked, which is it, small or large?
A common approach to dealing with larger data sets can be to use temp tables (or table variables) to load them up in a series of queries, then join & filter on the temp tables, divide & conquer. However, usually while doing this, it's set based operations to fill the temp tables and then set based operations to combine them.
I hope this helps some. Still, the other answers are dead on.