Post reply

Cursor within DTS

  • July 23, 2010 at 8:40 am

    #1197549

    Thanks Chris, this is awesome. Will go ahead and include all the other required bits and see how I get on with it.

    Thanks

  • July 24, 2010 at 8:41 am

    #1197862

    LadyG (7/23/2010)

    Thanks Chris, this is awesome. Will go ahead and include all the other required bits and see how I get on with it.

    Thanks

    Just to be absolutely sure, this calculation relies on certain mandatory rules and I want to make sure that you're absolutely aware of the fact that if you violate any of them, things will go wrong.

    You have to make sure that there are no joins in your final query.

    You have to make sure that the table is not partitioned.

    You have to make sure that that you do use the WITH(TABLOCKX) so no one makes any changes whatsoever to the table while the calculation/update is executing.

    You have to make sure the OPTION(MAXDOP 1) is used to prevent parallelism which would destroy the serial nature of the calculation.

    You have to make sure the clustered index is, in fact, in the correct order to support the calculation/update.

    You have to make sure you use an "anchor" column.

    All of these rules (and a couple of obvious ones not listed here) are described in the article on the subject at http://www.sqlservercentral.com/articles/T-SQL/68467/.

    While all that sounds a bit scary, the rules are manifested in code very easily... and they're still less complicated than the rules you would have to follow to build a cursor to do the same thing. The code will also work a million rows in just a couple of seconds instead of minutes like a cursor would take. I just wanted to make sure you were fully aware of the rules. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 26, 2010 at 1:07 pm

    #1198405

    Thanks Jeff, will do.

Viewing 3 posts - 31 through 32 (of 32 total)

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