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 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.

Answer 2

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.

Answer 3

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.

Answer 4

OMG, how did I forget about Group By? I took the cursor based query you see below and replaced it with the one after it. Now I get a single result set so there are no issues with using sqlsrv_next_result() in php.

DECLARE @thisday datetime;

DECLARE daycursor CURSOR FOR
SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as thisday
FROM computerusedata

OPEN daycursor;
FETCH NEXT FROM daycursor
INTO @thisday;
WHILE @@FETCH_STATUS = 0
    BEGIN
    select distinct left(ComputerName,5) as CompGroup,DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as day
    FROM computerusedata
    where DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) = @thisday
    order by CompGroup;
    FETCH NEXT FROM daycursor;
    END;
CLOSE daycursor;
DEALLOCATE daycursor;";


select DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as day,left(ComputerName,5) as CompGroup
from ComputerUseData
group by DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)),left(ComputerName,5)
order by day,CompGroup

Answer 5

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.

Answer 6

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.