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?
there a quite a few questions out here that talk about this same thing, like this one
Check out this search as well
Here's an article by a rather opinionated fellow, who gives reasoning for not using Cursors and some answers as to how they came to be: There Must be 15 Ways to Lose Your Cursors.
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.
I don't typically use cursors but when I do, it must be a "one-off" query that I'm running locally or a daily job. You want to refrain from having production code call a cursor that would be invoked frequently like in response to a web request.
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.
Normally they are to be avoided, but the feature is there for a reason and there are times to use them. I'd say 90+% of the cursors I've seen are not needed. If you are using them for CRUD operations, that can almost always be redone in a set-based fashion. I've often seen people use cursors for this becasue they don't know how to use joins in an update or delte or that they can use a select statment instead of a values clause in an insert. Another unnecessary use when people think they need them for slightly more complex processing that actually could easily be handled with a case statement.
Cursors are sometimes faster for calculating something like a running total.
Cursors are also handy for multiple executions of a stored proc that is set up to handle only one input value at a time. I do not use this feature for running user stored procs (unless I know I will be hitting a very small set of data) but it is very handy for database admins when neeeding to run system procs against multiple tables.
If you are creating emails in SQl (not the best place to do it, but in some systems that's where they do it) and do not want the entire audience of the email to see the other people on the list or you want to personalize each email with information about the addressee, cursors are the way to go.
Cursors or loops can be used also to process batches of records if the entire set-based insert/update/delete will take too long and lock up the tables. This is a sort of a hybrid between the cursors and the set-based solution and is often the best one for large changes on production systems.
Cursors are useful when 1) you need to do something that you cannot do with a set operation, or 2) it doesn't make sense to do the same work by making iterative calls from the application layer. Or sometimes you have a procedure that must remain on the database layer, and you simply can't break back out to the app layer midstream to iterate over some result set.
One recommendation I would make though, is that people use cursor variables rather than normal cursors, because you avoid the cursor allocation/deallocation issues that surround normal cursors. With a normal cursor, if you don't deallocate them they persist, which can be a source of memory leaks. Not so with variable-based cursors (i.e. DECLARE @cursor CURSOR).
The bottom line is, avoid them if you possibly can, and if you can't, use them minimally and wisely.
Only time I'll use them is when whatever is been done inside the cursor absolutely has to be done one item at a time and where whatever is been done inside the cursor takes so long that the overhead of the cursor fades into insignificance.
Eg database backups, integrity checks, index rebuilds. In short, admin tasks.
You use cursor for rebuilding or reorganizing table indexes individually
unless there is a way of running ALTER INDEX... as a set-based operation.