Set Based Operations Versus Cursors

Normally when you run a SQL command, you are performing set based operations. You are working with data in tables as a whole. The results are processed as a set. There is no inherent ordering (barring an ORDER BY of the results). This is the recommended method in most cases because it provides the best performance.

You could, instead, execute SQL iteratively. Then you are going to have to use cursors. Here is the order of operations then:

1. OPEN the cursor
2. FETCH data from the cursor
3. DELLOCATE the cursor
4. CLOSE the cursor

Another way to execute SQL one row at a time is to always use TOP(1) to limit results to a single row, and then repeat.

I find that there are some operations that are so complicated, or volume so huge, that a set based approach just does not work. In those scenarios, I put cursors to work. There might be some optimizations to committing the changes in batches to make this work as fast as possible. It is sometimes just a necessary evil to go iterative.