Cursors Be Gone!

  • Tom.Thomson (6/22/2010)

    YSLGuru (6/21/2010)

    Wow Tom, you really need to whip up on this guy bad ey? Is that he must confess that every word is his story is wrong and you are his SQL superior or what? I think its safe to say by now that everyone realizes what was intended and what is best when approaching the use of Cursors instead of some standard Set based methods to solve some unique problem. Are you done yet with beating up on this article and its author?

    No, I don't need to whip up on Gaby, I'm well aware that he understands the issues as that's made clear by his response to comments made last year, when the \rtile was first published. But I do think it's very important that people don't start by asking "cursor or no cursor" but instead "set-oriented or row by row iterative" (as one can write row by row iteration in a single query without resorting to a cursor the distinction bewteen the two questions is non-trivial). The discussion so far doesn't really make that point clea. It's also important that on those rare occassions when the answer to that starting question is "iteration" people don't discard the cursor option in favour of inferior iterative methods like the temporary table one illustrated in the article - and (unless I've missed somethiong) only Hugo had commented on that, so I thought it would be useful to make the two points together in one comment.

    If Gaby thinks I was whipping up oon him I'm sure he'll let me know and if he does I will apologise profusely since that is certainly not an impression I wanted to give him.

    Hi folks, it's all good, no harm done, but I am a little older and wiser in this regard. I've submitted a preamble to the article (left the article intact as that was what generated so much conversation in the first place) and hopefully it should get updated soon. Cheers.

    Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

  • Having read through umpteen threads on here like this, I think that every article on SSC should have a disclaimer:

    "All concepts, views, and results in this Article are as shown in good faith by the author. They may not be applicable to your situation, servers or configuration, and may in fact prove detrimental in certain border cases. Your mileage may vary. Your house may be repossessed if you do not keep up your mortgage repayments. If you have any empirical evidence of cases where this method is worse than than the obvious solution, please post them with results."


  • I agree that cursors are a drag on performance but what a way to go about avoiding them! A Count(*) and a delete for every row of the table, REALLY?

    You can achieve the same effect far more efficiently by declaring a temporary table with an Identity column as Primary Key. Then all you need to do is increment a variable and do select where Id = @Iterator, you can test @@Rowcount or value IS NULL to manage the loop.

Viewing 3 posts - 271 through 272 (of 272 total)

You must be logged in to reply to this topic. Login to reply