Assigning current of _Cursor to local @variable

Question

Assigning current of _Cursor to local @variable

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

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

J-440512 SSCertifiable Points: 6285 More actions · Answer 1

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: 1004362 More actions · Answer 2

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)