T-SQL Update Operations: An Answer For Jared

Jared Rypka-Hauer poses a question here:

As it appears (mostly from looking at how triggers work), a T-SQL update operation reads the row to be edited, concatenates the changes, then writes the whole thing to the table... but is that really how it works?

This description is correct as far as it goes, but a couple of steps are missing.

When SQL Server makes an update, it will first lock the data with the least aggressive strategy acceptable to the optimizer (this might be a row lock, a page lock, or a table lock). It will then write the updates to the transaction log, and write the appropriate pages of the transaction log to disk (which includes both a "before" and "after" image of the updated page -- the source for the virtual inserted and deleted tables available inside triggers). Only then will it write the table pages to disk.

Jared's particular concern is an update statement in an ORM facility:

Can you help settle a debate for me? I'm arguing with someone in a forum about whether an ORM framework should allow for subsets of columns within a generated class.

My contention is that a "row" (or in the case of ORM, a "record") should handle the entire row as an atomic entity, and that, as he refers to them, "selective updates" aren't a great idea.

My contention would be, given that you've already logically factored your data into classes, that you're probably on the right track, Jared. It's important to note that in my original post which spawned your question, I was discussing how to avoid expensive post-processing on data in an UPDATE statement. In that case, we're attempting to update every column in the table, but we don't want to run the expensive function on a column if its data hasn't changed. The net effect of this could be interpretted to be a "selective update", but I don't think the classification is completely accurate.

The other issue to consider here is what constitutes "an atomic entity" in your database. This will be a function of the extent to which you've normalized your schema -- I've got a database in which "an atomic entity" spans six tables; all of those tables must be updated correctly in order to correctly apply this particular unit of work.

So, Jared, with these caveats, I'd say you're on the right track in your design (and your "debate" in the forum). Please let me know if you need any clarification.

     -wp