dwain.c - Article body
This is interesting. We updated the Value column of our #Test1 table twice for each row. Or did we? [...(later)...] In reality we find that somehow the SQL Server Query Optimizer was smart enough to decide that it only needed to update each row in our #Test1 table once. How it chose the row to use to make that update will ultimately remain its own dirty little secret.
There's a Stream Aggregate in the query plan. It applies an ANY() aggregate to the Value column, grouped by ID. Therefore, Only one update per row is applied at the Clustered Index Update iterator. How the ANY() aggregate chooses a Value per ID group is undocumented, and therefore undefined.
dwain.c - Article body
Since we always hear that SQL does not guarantee row ordering without an explicit ORDER BY clause, we assume that the same case is true here.
Indeed. ORDER BY only guarantees the final (presentation) order anyway, it says nothing about row order between query plan iterators, and nothing should be inferred.
dwain.c - Article body
In this particularly contrived example, there’s no way to ensure the distinctness of the rows you’re applying as the source to the target table for the update [...] Why couldn’t our UPDATE statement throw a nice clear warning like that?
Well, you could write the query using SQL standard syntax:
UPDATE #Test1
SET Value =
(
SELECT b.Value
FROM #Test2 AS b
WHERE b.ID = #Test1.ID
)
The query plans contains the same Stream Aggregate, but also an Assert to check the subquery returns one row per iteration. An error is thrown when multiple Values are encountered.
dwain.c - Article body
Is it a good thing to have a piece of code like this running in Production, where the results of the update may be unpredictable?
Probably not, unless non-deterministic results are intended. A non-deterministic update is almost always not the programmer's intention in my experience.
A 2008 Connect suggestion by Hugo Kornelis to deprecate the UPDATE FROM and DELETE FROM T-SQL extension syntax generated some good discussion, but overall it is one of the most down-voted I have encountered:
http://connect.microsoft.com/SQLServer/feedback/details/332437
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi