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?
I would guess it has to do with the UPDATE ... FROM where there's not a 1-1 relationship between the table and the results of the FROM. In that case, what value is used for the update depends on the order that the rows in the FROM are returned and that can be changed by an index (or parallelism or a few other things)
The quirky update also depends on a particular order for the rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability