When is TSQL Cursors the best or only option?

Question

I have this argument about using cursors in TSQL lately ...

First of all, I am not a supporter of the discussion. But every time someone says a cursor , there is always some kind of knucklehead (or 50) that pounce on the obligatory "cursors - evil" mantra. I know that SQL-Server has been optimized for set-based operations, and perhaps the cursors are really evil, but if I wanted to put an objective thought behind this ...

Here where my mind goes:

1) The only difference between cursors and typing operations of one of the performance ?

Change It was a good case, because for him it was not just a performance issue - for example, starting a single batch over-and-over for a list of identifiers or, alternatively, executing the actual SQL text stored in the table field row by row.

2) Follow-up: Do cursors always perform worse?

EDIT : @Martin shows a good case where Cursors exit operations with many operations quite abruptly. I suspect that this would not be what you did too often (before resorting to some OLAP / Data Warehouse solutions), but, nevertheless, it looks like the case when you really could not live without a cursor.

- A link to TPC tests , suggesting that cursors be more competitive than people usually think.
- link to optimize memory usage for cursors with Sql-Server 2005

3) Are there any problems that you may think that cursors are better suited for solving than dial-based operations?

EDIT . Operations with settings literally cannot Execute stored procedures, etc. (see edit for paragraph 1 above).
EDIT . Settings operations are exponentially slower than line by line when it comes to aggregating large datasets.

• An article from MSDN explaining their perspective on the most common problems people resort to cursors for (and some explanation of set-based methods that will work better.)
• Microsoft says (vaguely) in the 2008 Transact SQL Reference on MSDN : "... there are times when the results are best processed a row at a time," but do not give any examples of which case they relate to.

Basically, I mean converting cursors to set operations in my old code if / as any significant updates for various applications, if there is something that can be obtained from it. (I often relate to laziness in cleanliness, i.e. If it has not broken, do not correct it.)

Answer 1

To answer your question directly:

I have yet to face a situation where the given operations could not do what otherwise could be done with the help of cursors. Nevertheless, there are situations when using cursors to break a large set of problems into more manageable fragments proves a better solution for the convenience of code maintenance, logging, transaction management, etc. But I doubt that there are strict rules that will tell you what types of requirements can lead to one solution or another - individual databases and needs are simply too diverse.

Nevertheless, I completely agree with your approach "if it has not broken, do not correct it." Reorganizing procedural code is not enough to establish operations for a procedure that works very well. However, the correct rule is to first look for a solution based on a set and only if necessary access the process code. Do you feel If you use cursors more than 20% of the time, you are doing something wrong.

And for what I really want to say:

When I interview programmers, I always throw them some moderately complex SQL questions and ask them to explain how they will solve them. These are problems that, as I know, can be solved with the help of given operations, and I am specifically looking for candidates who can solve them without procedural approaches (i.e. Cursors).

This is not because I believe that in any approach there is something inherently good or more effective - different situations give different results. Rather, because, in my experience, programmers either get a concept of set-based operations or not. If they do not, they will spend too much time developing complex procedural solutions for tasks that can be solved much faster and simply using operations with settings.

And vice versa, a programmer who receives operations with a set almost never encounters problems with a procedural solution when it is really necessary.

Answer 2

Running totals is a classic case when the number of rows becomes larger, cursors can perform operations based on the set, because, despite the higher fixed cost of the cursor, the required work grows linearly and not exponentially, as when using the triangular connection set, an approach.

Itzik Ben Gan makes several comparisons here .

Denali has more complete support for the OVER clause, but this should make useless.

Answer 3

Since I have seen that people manage to redefine cursors (in all their various forms) using other TSQL constructs (usually including at least one while loop), nothing that cursors could achieve cannot be done using other constructs.

This is not to say that repetitive implementations are not as inefficient as the cursors that were avoided without including the word “cursor” in this solution. Some people seem to just hate the word, not the mechanics.

In one place that I successfully claimed to hold cursors were for transferring / converting data between two different databases (we dealt with clients here). Despite the fact that we could implement this transfer based on a set (indeed, we previously had), there was problematic data that could cause problems for several clients. In a set-based solution, we had either:

• Continue the migration, excluding invalid client data in each table, leaving these clients partially transferred or
• interrupt the whole batch

Taking into account that by making the transfer unit a separate client (using the cursor to select each client), we could make each transfer of the client between systems either completely or completely rollback (i.e. place each transfer in its own transaction)

I can’t think of any situations where I would like to use the cursor below the “top level” of such transfers, though (for example, choosing which client will transmit next)

Answer 4

Often, when you create dynamic sql, you have to use cursors. Imagine a script that scans all tables in a database for the same value in different fields. The best solution would be a cursor. The question of where the problem was raised is here. How to use EXEC or sp_executeSQL without a loop in this case? I will be really impressed if anyone can solve this better without a cursor.