MS SQL Server - When is a CURSOR good?

Question

Many times when I've written stored procedures, etc. I use a CURSOR at first and later find some performance issue with my procedure.

Every thing I read says CURSORS are awful, cause unnecessary locking, etc. and performance testing proves the same.

My question is when do you use a CURSOR and in what situations are they useful or good?

If there is no use, why would they make such a bad control structure/type for SQL?

Answer 1

I asked a guy on the SQL Server team one time, if you could add one feature that would make the product better for everyone what would it be?

His response was 'Add? Huh, I would take one away. If you get rid of cursors you force programmers all over the world to start thinking about things in a SET based way and that will be the biggest world wide increase in DB performance you will ever see.'

For my part however I tend to see a pattern, there seems to be a lot of procedural coders who use cursors because they need to be able to do an operation one element at a time and miss the old fashion WHILE loop concept. Same basic idea without the cursor overhead. Still not near as fast/effective as something SET based but 90% of the time when someone claims 'I cant do this set based, I have to use cursors' I can get them to do it with a while loop.

Answer 2

The MCTS prep manual for SQL Server 2008 that I'm studying recommends using external CLR code anywhere that a CURSOR would be required in T-SQL, especially now that SQL Server 2008 supports custom aggregate functions.

5 years ago, I worked with them for extensive reporting features, but I don't think I could come up with a good use case for them now. CLR aggregates and functions perform similarly to built-in aggregate functions.

Answer 3

You use cursor for rebuilding or reorganizing table indexes individually
unless there is a way of running ALTER INDEX... as a set-based operation.