Assigning current of _Cursor to local @variable

  • Using "INDEX(0)" is a later manifestation of the code... don't need to know the name of the clustered index that way and that allows you to easily use this on temp tables where the PK constraint should never be explicity named.

    Jeff, you copmpletely lost me there, Would you please elaborate ?

    Dunno if I explained that all clearly... lemme know if you have any more questions

    Great! Very clear.

    I am wondering WHY the HINT should be necesswary in the statement

    UPDATE Products

    SET @PrevF2 = F2 = CASE WHEN F2 IS NULL THEN @PrevF2 ELSE F2 END,

    @PrevTempID = TempID --Just an "anchor"

    FROM Products WITH (INDEX(0))

    If there is a clustered primary key in a temp table, should'nt the optimizer try an index scan without having to be told to ?

    Regards

  • J (10/30/2008)


    I am wondering WHY the HINT should be necesswary in the statement

    UPDATE Products

    SET @PrevF2 = F2 = CASE WHEN F2 IS NULL THEN @PrevF2 ELSE F2 END,

    @PrevTempID = TempID --Just an "anchor"

    FROM Products WITH (INDEX(0))

    If there is a clustered primary key in a temp table, should'nt the optimizer try an index scan without having to be told to ?

    Regards

    Yes... it absolutely should... kinda like the kids should take out the trash on Friday mornings... the only way to guarantee it is to tell them every Friday morning and then what them do it... 😉

    --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)

Viewing 2 posts - 31 through 31 (of 31 total)

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