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.
TOP is an interesting example and granted, it could change the presented result, but without an ORDER BY, TOP makes little sense.
I do not feel that the UPDATE...FROM/JOIN and the Quirky Update apply here however. The UPDATE...FROM/JOIN is not the case that came to mind initially but is why I have begun using MERGE to replace UPDATE...FROM/JOIN queries, to gain protection from ambiguous update scenarios. iirc UPDATE...FROM/JOIN is a proprietary T-SQL implementation.
My comment was that the yield from the FROM, WHERE, GROUP BY and HAVING clauses is not altered based on which index the engine picks. Diving into the UPDATE scenarios I think you have to parse out the modification of the data from the selection of the data to evaluate what the "resultset" would be in a SELECT scenario.
Just my take.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato