Ms SQL Server - When Is a Cursor Good

MS SQL Server - When is a CURSOR good?

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.

Why is it considered bad practice to use cursors in SQL Server?

Because cursors take up memory and create locks.

What you are really doing is attempting to force set-based technology into non-set based functionality. And, in all fairness, I should point out that cursors do have a use, but they are frowned upon because many folks who are not used to using set-based solutions use cursors instead of figuring out the set-based solution.

But, when you open a cursor, you are basically loading those rows into memory and locking them, creating potential blocks. Then, as you cycle through the cursor, you are making changes to other tables and still keeping all of the memory and locks of the cursor open.

All of which has the potential to cause performance issues for other users.

So, as a general rule, cursors are frowned upon. Especially if that's the first solution arrived at in solving a problem.

SQL Cursors...Any use cases you would defend?

Sure, there are a number of places where cursors might be better than set-based operations.

One is if you're updating a lot of data in a table (for example a SQL Agent job to pre-compute data on a schedule) then you might use cursors to do it in multiple small sets rather than one large one to reduce the amount of concurrent locking and thus reduce the chance of lock contention and/or deadlocks with other processes accessing the data.

Another is if you want to take application-level locks using the sp_getapplock stored procedure, which is useful when you want to ensure rows that are being polled for by multiple processes are retrieved exactly once (example here).

In general though, I'd agree that it's best to start using set based operations if possible, and only move to cursors if required either for functionality or performance reasons (with evidence to back the latter up).

What is the use of a cursor in SQL Server?

Cursors are a mechanism to explicitly enumerate through the rows of a result set, rather than retrieving it as such.

However, while they may be more comfortable to use for programmers accustomed to writing While Not RS.EOF Do ..., they are typically a thing to be avoided within SQL Server stored procedures if at all possible -- if you can write a query without the use of cursors, you give the optimizer a much better chance to find a fast way to implement it.

In all honesty, I've never found a realistic use case for a cursor that couldn't be avoided, with the exception of a few administrative tasks such as looping over all indexes in the catalog and rebuilding them. I suppose they might have some uses in report generation or mail merges, but it's probably more efficient to do the cursor-like work in an application that talks to the database, letting the database engine do what it does best -- set manipulation.

Using cursors in Microsoft SQL Server with a loop

Using a CURSOR is usually slower than better alternate options. That being said, the first thing you would need to work on is getting the SELECT statement working. I don't think it will work as you have it. If you are using a COUNT, you are aggregating. If you want to filter the results by the aggregate, you can't do that with the WHERE clause. Instead you would need to move it into a HAVING clause. Also, since you are selecting more than the aggregate counts, you need to GROUP BY the CourseID. To continue this route, you would have a SELECT like:

SELECT c.CourseID, COUNT(sc.StudentID) FROM Courses c JOIN StudentCourses sc
ON c.CourseID= sc.CourseID
GROUP BY c.CourseID
HAVING COUNT(sc.StudentID) < 5  OR COUNT(sc.StudentID) > 10;

Not that it is a bad idea to limit the number of rows you are going to run through in the CURSOR, but if you are going to be checking for the number of students as part of the CURSOR, maybe they are looking for you to do the logic in the CURSOR itself. You could just eliminate the HAVING clause and go through all of the rows in the CURSOR instead.

Once you get the SELECT straightened out, and you FETCH from the CURSOR, you want to pull the items you SELECT into variables you can then use to generate your messages. Therefore, you want to DECLARE them earlier, and then pull the CURSOR values into them. Something like

FETCH NEXT FROM Students_Cursor INTO @CourseID, @StudentCount;
WHILE @@FETCH_STATUS <> -1

Then you can do your IF/THEN conditions on the @StudentCount, and use @CourseID in the message. Just make sure that after that, you FETCH NEXT FROM into the variables again inside of the BEGIN/END block, then of course CLOSE and DEALLOCATE the CURSOR.

Is there any performance impact using cursors in stored procedure?

Cursors are one of the worst things you can do for performance. They run row by row instead of impacting the whole set of data. No one except an experienced dba should ever consider writing one.

http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

Pros and cons of using a cursor (in SQL server)

There are several scenarios where cursors actually perform better than set-based equivalents. Running totals is the one that always comes to mind - look for Itzik's words on that (and ignore any that involve SQL Server 2012, which adds new windowing functions that give cursors a run for their money in this situation).

One of the big problems people have with cursors is that they perform slowly, they use temporary storage, etc. This is partially because the default syntax is a global cursor with all kinds of inefficient default options. The next time you're doing something with a cursor that doesn't need to do things like UPDATE...WHERE CURRENT OF (which I've been able to avoid my entire career), give it a fair shake by comparing these two syntax options:

DECLARE c CURSOR 
    FOR <SELECT QUERY>;

DECLARE c CURSOR 
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR <SELECT QUERY>;

In fact the first version represents a bug in the undocumented stored procedure sp_MSforeachdb which makes it skip databases if the status of any database changes during execution. I subsequently wrote my own version of the stored procedure (see here) which both fixed the bug (simply by using the latter version of the syntax above) and added several parameters to control which databases would be chosen.

A lot of people think that a methodology is not a cursor because it doesn't say DECLARE CURSOR. I've seen people argue that a while loop is faster than a cursor (which I hope I've dispelled here) or that using FOR XML PATH to perform group concatenation is not performing a hidden cursor operation. Looking at the plan in a lot of cases will show the truth.

In a lot of cases cursors are used where set-based is more appropriate. But there are plenty of valid use cases where a set-based equivalent is much more complicated to write, for the optimizer to generate a plan for, both, or not possible (e.g. maintenance tasks where you're looping through tables to update statistics, calling a stored procedure for each value in a result, etc.). The same is true for a lot of big multi-table queries where the plan gets too monstrous for the optimizer to handle. In these cases it can be better to dump some of the intermediate results into a temporary structure first. The same goes for some set-based equivalents to cursors (like running totals). I've also written about the other way, where people almost always think instinctively to use a while loop / cursor and there are clever set-based alternatives that are much better.

UPDATE 2013-07-25

Just wanted to add some additional blog posts I've written about cursors, which options you should be using if you do have to use them, and using set-based queries instead of loops to generate sets:

Best Approaches for Running Totals - Updated for SQL Server 2012

What impact can different cursor options have?

Generate a Set or Sequence Without Loops: [Part 1] [Part 2] [Part 3]

---

Related Topics