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

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 because they don't know how to use joins in an update or delete 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 needing 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.