Jack Corbett (3/14/2008)
Hugo Kornelis (3/14/2008)
Could you post the where the documentation of it producing indeterminate results is? I'd be interested in reading that.
Sure. It's in BOL, or the online equivalent on MSDN: http://msdn2.microsoft.com/en-us/library/ms177523.aspx, scroll down to the heading "Using UPDATE with the FROM Clause". You'll see this:
The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.
(followed by an example)
You mention adding and index and the performance gained from it. You do mention, in a parenthetical, being careful not to add too many indexes, but I think you could have more clearly stated that you need to really study out the afffects of adding an index and that will take a lot more than 5 minutes. There are many beginners that are on this site regularly who may read that and start adding indexes whenever they encounter performance issues with a single query and then start having problems elsewhere.
Fair enough. Adding indexes by trial and error is always a bad idea, you really have to understand what they are, how they work, and how they impact every aspect of SQL Server.
Thanks to Jeff and Hugo for sharing their expertise and making me think before I code!
And thanks to you for your welll though out reaction!
(edit - changed the BOL address to a proper hyperlink)