Hi Puja63,

First, let me reinforce that "avoid cursors" is a good rule of thumb. This is not true of all databases, but SQL Server is heavily optimized for set-based processing. In 99.9% (estimation backed only by personal observation) of all cases, a set-based solution runs rings around any row-based attempt. (To avoid misunderstanding - set-based means that one [or possibly a few] queries are used that process all qualifying rows at once; row-based means that a cursor or other technique is used that to process qualifying rows one by one - on this site, the term RBAR [Row By Agoniziing Row], coined and popularized by Jeff Moden, is often used for row-based).

In practice, most cursors are not implemented after considering possible set-based alternatives, but because a developer with a 3GL backgrund found that instrument to fit the mindset he/she is accustomed to. Not newbie developers, but newbie SQL developers. I'd say that a cursor is actually an instrument that should be reserved for the experts. Not because coding a cursor is hard, but because deciding when this is indeed the best alternative is hard.

The problem is that the "avoid cursors" advise is often misunderstoood and misreprersented in two ways, And both have occured in this discussion.

1) Some people extend the advise to "avoid row-based processing always and in all circumstances". That does no justice to the 0.1% of cases where the cursor is indeed the best alternative.

2) And some people take the "avoid cursors" too literally, without realising that it actually means to "avoid all types of row-based processing". They replace a cursor by a WHILE loop that basically does the same, thinking they have followed best practice. And if they actually do performance measurements (most probably don't), they might even feel reinforced. However, that is not because a WHILE loop performs better than a cursor, but only because a WHILE loop (if coded with care) performs better than a cursor with default cursor options.

A few years back, I have compared all possible cursor options to find the performance differences between the various possiblities. If you are interested, then please read these articles:

* Curious cursor optimization options

* Poor men see sharp - more cursor optimization

Note however that I later found that my research was incomplete. My conclusion that FAST_FORWARD is never faster than a STATIC FORWARD_ONLY READ_ONLY cursor is only correct for cursors that process an amount of data that fits entirely in the available cache. As soon as the amount of data processed by the query exceeds available cache, FAST_FORWARD starts to shine. I guess I should take the time to do an update of that article.

I'd say that the cursor code for the challenge posted should be pretty straight-forward. But since you ask, I'll post the code. Just not now, I'm a bit pressed for time. Feel free to remind me if you think I've forgotten my promise.