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.

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.

Default Resolver

Dear all,
I'm just trying to do merge replication between to DB with Dialup connection.
- If any conflict occur, does the default resolver let the publisher win? or should we use another resolver? how?
- What will happen if the connection totally broken during running synchronization?
Thanks in advance.
ech
Yes, by default the publisher wins a conflict with a subscriber and the
first subscriber takes the place of the publisher in any conflicts with
other subscribers. You can use global conflict resolution (priority numbers
assigned to each subscriber), one of the many inbuilt conflict resolution
algorithms or use a custom resolver. If you want to use a custom resolver
there is an average price stored procedure in the samples which is good to
start with, or you might want to make a COM resolver.
If the connection is broken ('Network Failure') just restart the merge agent
once the connection is up and it will continue.
HTH,
Paul Ibison
|||It's helpful. Thanks Paul.