Deferred Updates versus In-Place Updates

The following question was posted on http://support.microsoft.com/kb/195760/en-us:

Q. What are the conditions under which deferred updates occur in SQL Server 7.0?

A. In SQL Server 7.0, all updates are in-place (direct), provided that the column or columns participating in the clustered index key are not changed. If a change is made to a UNIQUE clustered key, the update plan runs in a hybrid mode where the query processor combines direct and deferred updates. If the clustered key is not UNIQUE, all of the updates are run as deferred.

You can use SHOWPLAN to see examples of how this behavior works. Look for the SPLIT and COLLAPSE operators in the plan. If you find a SPLIT below the clustered index update, one of the clustering keys must have been changed. If a COLLAPSE operator is found, the update is running in a hybrid mode; SQL Server collapses delete and insert to the same key values into an in-place update.

NOTE: This behavior holds true for any index.

Has this behavior changed in SQL Server 2005?

Ken, whenever you are modifying the key columns of an index, the operation will be implemented as a delete followed by an insert, as the row needs to move within the B-Tree from the old location to the new. This can be explictly visible in the query plan showed in SHOWPLAN with the SPLIT operator, or be implicitly performed at the Storage Engine level. In SQL 2005, the number of cases where the SPLIT operator is employed is somewhat decreased, but it is still used. See the following trivial example:

use tempdb
go

create table t(p int primary key clustered)
go

update t set p = p + 1
go

Please let me know if you have more questions on this.
Thanks

|||

Okay, that tells me how changes to the primary key columns will be processed. Now what will happen if only the non-key columns are changed?

Ken

|||When only non key columns are updated, changes are performed in place, i.e. without moving the row inside the B-Tree. This is of course a much cheaper operation. You might want to take a look at a new SQL 2005 feature, the ability to include nonkey columns in nonclustered indexes.

Bottom line: an update statement will not maintain the nonclustered indexes whose columns are not being modified, will trigger row movements inside the indexes whose keys are being updated, and perform in place update to indexes whose only non key columns are being modified.