Is it ALWAYS possible to avoid cursor?

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!

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

StackExchange Database Administrators Q#33757, answer score: 14