The SQL Standard specifies that the result of an update must be the same as if it had been executed in three separate and non-overlapping phases:
- A read-only search determines the records to be changed and the new column values.
- Changes are applied to affected records.
- Database consistency constraints are verified.
This is known as update phase separation: Logically, the read phase completes before the write phases, and finally the verification phase.
This is the underlying reason why the update has the effect it does. SQL Server is bound to ensure the final committed state of the database is exactly as if all read operations (step 1) had completed before any changes were made (step 2).
It is as if the update specification were:
SET NEW.ContactName = OLD.City,
NEW.City = OLD.ContactName;
It would be inefficient in many cases if SQL Server were to literally calculate new column values (based on existing column values) for all qualifying rows and store them somewhere before embarking on a second step of writes.
SQL Server is aware of this, and will attempt to physically perform as many operations as it can accessing old data by reference rather than making a copy (by value), and processing row-by-row in a streaming fashion where that can be done safely.
The are many additional optimizations in the engine around update processing because is it otherwise very resource-intensive. Regardless, all optimizations preserve the semantic expressed in the standard. The end result is always as if complete phase separation had occurred.
Other database products use row versioning (MVCC) to ensure the read phase always sees the pre-update data. SQL Server uses a different arrangement, which you can read more about in my series about Halloween Protection.
It is often possible to get some insight to the way SQL Server processes an update by looking at operators in execution plans as the other answer implies.
That said, execution plans do not expose all the details needed to understand an update operation at the lowest levels. A number of important optimizations and implementation details are simply not exposed to users that way. For example, it is not possible to tell whether an update operator will access values from the current row by reference or by value; or whether the update will perform an in-place update or a delete-then-insert.