• sqldba_newbie (3/1/2013)


    Jeff Moden (3/1/2013)


    opc.three (3/1/2013)


    Jeff Moden (3/1/2013)


    Actually, the change of an index CAN very much change the result especially where UPDATEs are concerned.

    I think we covered the OP's question about SELECT statements so I don't feel bad taking a sidebar here.

    Is this something to do with the quirky update? Can you provide some documentation that explains the behavior you're alluding too?

    It certainly applies to the quirky update but I wasn't referring to such an arcane method. Gail is spot on for what I meant.

    They can also affect SELECTs that use TOP with no ORDER BY. The addition of a index can instantly change what the TOP returns if the index comes into play. In most cases, of course, it is NOT something that should be relied on.

    Does have a order by something like this:

    (SELECT DISTINCT Row_number()

    OVER (

    ORDER BY AdDate ASC ) Row

    Then at the end of the query result set has:

    ORDER BY ROW ASC

    However if i remove the "inappropriate" index, query makes use of another index and provides results as expected? I did do a update stats with full scan.

    Without an ORDER BY that query will not be guaranteed to return the same result every time. The ORDER BY in the window function determines the ranking each row coming back from the FROM-clause yields. You need the ORDER BY on the query to guarantee order else the engine will just return the results in the quickest way it can find with no attention paid to ordering.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato