L' Eomot Inversé (9/7/2013)
Not sure what one row at a time means (or indeed what the relevance of a batch is) here; the specification of T-SQL makes it quite clear that the statement will update all the rows that satisfy the where condition unless prevented/modifed so that only one row is changed by one of two things:(a) set rowcount = 1
(b) a trigger (either an instead of trigger that handles only one of the rows in the inserted/deleted tables, or an after trigger that restores the original value of all but one of the rows affected).
I imagine you would have noticed if either of those applied, so maybe one row at a time means something else?
It is updating the table (142,000+ rows) one at a time, literally. As it runs it reports 1 row updated.
I would look at providing the estimated execution plans for the two queries but I'm not too sure about doing it for security reasons.
What I have found digging into the two execution plans is this:
The weird update is using a clustered index scan with an output list of only the TextKey (this is primary and clustered index key). Parallel is set to false.
The working update is using a clustered index scan with an output list of TextKey, Col1, Col 2 (where Col1 and Col2 are the columns I am trying to set to null). Parallel is true. This one is using a Table Spool (Eager Spool) while the other is not.
Both, at the end, are doing a Clustered Index Update. The weird update has a cost of 90% for its plane, where the good update has a cost of 0%.
Again, the tables are structurally identical.
I'm lost.