I've been having a chat with Google and haven't been able to find an answer to it so here goes ...

I have 2 servers, one SQL 2008 R2 & the other SQL 2000, both using the same data, same SQL Statements & same Collation and they are returning different results.

The SELECT statement of the UPDATE returns multiple rows for each row that is updated. SQL 2008 R2 uses the values from the First row returned and SQL 2000 is using the values from the Last row returned for each update.

Example below ... - If I run the code below on a SQL 2008R2 machine the result is 1. - If I run the code below on a SQL 2005 machine the result is 1.- If I run the code below on a SQL 2000 machine the result is 3.

CREATE TABLE #Test( Row INT IDENTITY (1, 1), Result INT)

CREATE TABLE #UpdateResult( Result INT)

INSERT #UpdateResultSELECT 0

INSERT #TestSELECT 1

INSERT #TestSELECT 2

INSERT #TestSELECT 3

UPDATE #UpdateResultSET Result = #Test.Result FROM #Test

SELECT * FROM #UpdateResult

DROP TABLE #UpdateResultDROP TABLE #Test

Questions ...- Huh?- Is this a setting that is specified anywhere? If so can it be changed?

My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.