Assigning current of _Cursor to local @variable

• J-440512

  SSCertifiable

  Points: 6285

  More actions

  October 30, 2008 at 12:00 pm

  #892026

  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

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  October 30, 2008 at 6:26 pm

  #892129

  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)
  Intro to Tally Tables and Functions