5

For example, the following query works just as intended in Microsoft SQL Server (T-SQL).

UPDATE Customer
SET ContactName = Customer.City, City = Customer.ContactName;

I would like to know why the above works. I was expecting Customer.City to remain the same. Why does this happen?

Would appreciate if you could provide some sources so that I can read more on these topics.

Thank you.

0
7

It works out what it’s going to do using a bunch of operators. One does the update, but only once it has all the values it’s going to set everything to. So it doesn’t set ContactName and then figure out what it’s going to set City to, it works out that on the first row, it’s going to set ContactName to 'London' and the City to 'Rob', and then it does that.

I’d suggest reading any of Itzik Ben-Gan’s books on T-SQL Fundamentals, and any Kalen Delaney’s books on SQL Internals. They both explain the basics of how queries run.

10

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:

  1. A read-only search determines the records to be changed and the new column values.
  2. Changes are applied to affected records.
  3. 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:

UPDATE Customer
SET NEW.ContactName = OLD.City,
    NEW.City = OLD.ContactName;

Optimizations

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.