Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm looking for any example where using a cursor is unavoidable. I'm pretty sure there are some rare cases like that, but I'm unable to come up with any scenario. Any help would be wonderful. Thanks!

share|improve this question
2  
Well you can always simulate a static cursor with a while loop and a temporary table but does that count as avoiding a cursor? Are you asking for cases where a cursor is the best solution? –  Martin Smith Jan 29 '13 at 16:24
1  
What is your goal? –  Aaron Bertrand Jan 29 '13 at 16:36
1  
Never say never. There's probably always a legitimate case for using any given feature in SQL Server, no matter how infrequently. There's probably a case for auto-shrinking even. –  Cade Roux Jan 29 '13 at 16:49
    
@CadeRoux I don't know if I'd go that far... –  Aaron Bertrand Jan 29 '13 at 16:50
    

1 Answer 1

up vote 12 down vote accepted

Sure (and let's assume that a while loop and a cursor are basically the same thing for the purposes of the discussion, throwing out any arguments that using a while loop avois a cursor, which is simply not true except in the most pedantic way):

  • You need to execute a stored procedure (say, a vendor stored procedure that you aren't allowed to touch, and that you must use to stay in compliance/support). For every row in some set.
  • You need to send a distinct e-mail to each of a set of users (as opposed to just building a massive To/CC list - not only because that doesn't scale but also because you may need to personalize the e-mail for each user).

There are also some metadata operations where you might want to use a cursor, e.g. to disable and re-enable all foreign key constraints on every table. Personally, I've always preferred building a string in a query that I can execute at once, but you may not want to do this for various reasons. As an example, I didn't use a single cursor in this answer, though I could have and it would have been simpler to write. (Also note that solutions like that one - and many seemingly set-based queries - still technically use a cursor under the covers - you just don't actually write DECLARE CURSOR...)

It can also be quite complex to build a set-based query that does exactly what a cursor does, and often isn't worth the time investment or the impact to future maintainability due to query complexity, when the cursor is actually much more straightforward (even if it is less efficient). And for efficiency, if you're comparing a cursor solution to a set-based solution, make sure you use the most efficient cursor options - these can make a big difference.

For normal queries you're building from scratch and without any of these constraints, I don't know that there is a situation where you have to use a cursor, especially as SQL Server continues to add windowing functions and other features that assist in more advanced set-based programming that used to typically require cursors.

If you're writing a cursor, you should be asking yourself, "Is there a set-based way to do this?" Depending on the purpose of the code, how much it will be re-used, whether it is a one-off task, etc. the answer is almost always, unilaterally, "Yes!" But a perfectly valid answer to that question might also be, "Yes, but who cares?"

Would an example where a cursor is actually the best performing, supported, documented and guaranteed solution to a problem suffice? One such example is calculating running totals in SQL Server < 2012. I blogged about it here - and actually demonstrated that in my use case a cursor was faster than all the other common approaches, even the often recommended (but not guaranteed, documented or supported) "quirky update" method. (Only if the new 2012 LAG feature is not an option, in which case, it runs circles around everything else).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.