Saturday, February 10, 2007

SQL Server cursors are not always evil !!

In her blog post, Robyn Page reopened a discussion that took place many times. She provided a problem (calculating a running total) as a proof that in some cases using cursors can give better performance than using other T-SQL techniques, as (sub queries-joins etc.)
The claim that T-SQL cursors are bad and to be avoided by any means is well advocated for, and many developers assume that whatever a solution they come to using set-based statements is necessarily better than using cursors.
In this SQLTeam article the writer intended to use the same problem (calculating a running total) as a proof, but surprisingly he found that the results of using cursors were better.

So what is the bottom line? When are cursors good and when are they bad?

We can generalize the result of the above mentioned tests and say that, in general, operations that needs sequential processing are cases where we should consider using cursors.

I also faced a situation about 2 years ago when I needed to provide paging to query results, which required jumping to a specific recored in a result set. I applied a technique using dynamic cursors and it worked great even with thousands of records and . Later I found a codeproject article that proves this method to be recommended to a great extent, another case that cursors proved to be useful.

I'm not trying to count the cases when cursors are good. I just don't like taking assumptions for granted. But I tried to give some cases where it's a good idea to use try different techniques including cursors and judge by yourself.

No comments: