question

Not really. In terms of what it is doing, a while loop and a cursor both do the same thing, they operate on one row at a time. A lot of people when trying to remove cursor-based code, simply replace it with a while loop, in the hope that it will run faster, because it's not a *nasty* cursor. In fact I think I've seen examples where a cursor is faster than a while loop, as a cursor is a data structure designed for that very purpose. What they should be doing is re-writing so that the code operates in a set-based fashion. Write the query declaratively, not procedurally. I've recently taken a chunk of code that, amongst other things, had nested cursors - execution times have fallen from 40 mins to 5 seconds. I'm sure there's bound to be some circumstances where a while loop is preferred over a cursor, just as there are ones where a cursor beats set-based code hands down, but they will be edge cases.

In addition to @Kev Riley 's answer, you should give a try to FAST_FORWARD CURSOR approach (If not used already). It could do wonders if you cannot do the design changes. And for the example where WHILE LOOP is slower, go to the following hyperlink http://www.techrepublic.com/blog/datacenter/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/1741

+1 to @Kev Riley When I started at my former position, about 10 years ago, there were tons of cursor-based stored procedures. Some of them were created for batch-loading data and I didn't really bother about them, because their performance was OK (they ran outside of bussiness hours). But there were also cursors in online operations, like a statistical module, where the users were complaining over response times. Knowing what I know now, I would replace them with set-based logic. In most cases it would be rather straight forward, it would have been possible to replace the whole row-by-row-insert-to-temporary-tables-and-query-them with one single select. But some would have required for example a Tally table. What I did at that time was to replace cursors with WHILE-loops. Not the prettiest solution, but - as it turned out - a MUCH faster solution. The reason for it being faster was that the cursors were only used to extract a date period, which was already known and could be replaced with a loop from 1 to 12, or from 1 to 7 or from 1 to 365/366, all depending on the operation at hand. What was saved with a WHILE-loop instead of a cursor at that time was the totally unnecessary initial operation to query table(s) to extract the period data, and maintaining it. But I want to stress that using a set based approach would have been a much, much, much better solution. If I saved time from 20 to 10 seconds per query with the WHILE-loop, I would have saved time from 10 to 0 seconds using a set based approach. Perhaps you're right - it might take more development time to replace a cursor with a set-based approach than replacing it with a WHILE-loop. But that won't have to be the case. I would either try to convince my manager to allow me to test also the set based approach on a few procedures, or simply do it without even bothering the manager, and then, when proven that they perform much better and also that they won't demand much more working hours build my case from there. Except the fact that set-based almost always beat loop-based logic in SQL Server, another advantage is that there are almost always fewer lines of code to maintain in a set-based procedure than a loop-based procedure.

I'm going to go into the uncomfortable position of respectfully disagreeing with @Kev Riley at least partially. I want to reiterate what he said about trying to do things in a set based fashion. That will virtually always be much faster and more effecient than either a cursor or while based loop. I fully agree on that part. Where I disagree is that when you really must operate on the rows one by one, then I think a while loop is better than cursor. I have yet to see a time when a cursor is faster than a while loop (though that doesn't mean they don't exist!) but I have seen times where a while loop is faster than a cursor. More significantly, I think that most programmers will find a while loop much easier to read. Remember that most of us learned to program outside of a database before learning to deal with databases. A while loop is a very natural entity that occurs in some form in virtually every language out there, cursors do not have that property. But I want to once again emphasize that while I would definitely choose a while loop over a cursor, I would only use either if it could not be done in set based operations. I normally encounter this as part of a data cleaning process where I may need to execute DDL statements (sometimes with variations depending on the structure of the table) against numerous tables in sequence, or in the (pre-Powershell) days when I sometimes found it made sense to do limited file operations from inside of T-SQL so I was looping through lists of file names.

while loop,do while, for loops advantage on one and other ??????????????? plz ans me