Cursor vs While loop. What are advantage/disadvantages of cursors - mssql

Question

Is it a good idea to use while loop instead of cursor. What are advantage/disadvantages of cursors.

Answer 1

Some disadvantages of cursors are here

Then there is also this (Actually a very intersting problem called - "Halloween problem")

Answer 2

Some of these depends on the DBMS, but generally:

Pros:

• Outperform loops when it comes to row-by-row processing

• Works reasonably well with large datasets

Cons:

• Don't scale as well

• Use more server resources

• Increases load on tempdb

• Can cause leaks if used incorrectly (eg. Open without corresponding Close)

Answer 3

Take a look at this discussion on triggers..

http://stackoverflow.com/questions/14031/database-triggers#15093

Answer 4

I would ask you what you are doing with that cursor/while loop.

If you are updating or returning data why don't you use a proper WHERE clause. I know people who would say you should never use cursors.