Update in Place

When an update is performed in-place, a log record of type MODIFY is recorded in the log. This record contains only the differences between the old and new row values, not their actual values, and it cannot handle changes in row length. For in-place updates, the following restrictions apply:

The table cannot contain an UPDATE trigger.
The table cannot be marked for replication.
There can only be one table involved in the UPDATE statement. Primary/foreign key references automatically mean that more than one table is involved. This rule includes any subselect in the update query including a self-join. Whenever more than one table is involved, a deferred update takes place.
The column being updated cannot be part of a clustered index.
The column being updated can be of variable length, but the new row length must be exactly the same as the old row length.
The new row cannot be too different from the old one. The number of differing bytes cannot be more than one half the total row size, and the total number of discontiguous differing blocks cannot be more than three. Differing blocks within 8 bytes are considered a single block.
Consider the following code sample:

create table t1 (col1 int, col2 char(60))

insert t1 values

(1,'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz')

insert t1 values

(2,'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz')

— continue for 1000 rows —

create unique index idx1 on t1 (col1)

If you try to change more than one half of the row size—do not confuse this with one half of the column length—delete/insert is performed instead of in-place. Since this example changes 33 bytes of a 64-byte row size, delete/insert is performed.

update t1 set col2 =

'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzhijklmnopqrstuvwxyz'

where col1 = 1

If you have more than three differing blocks within the column being updated, delete/insert is performed. Since this example changes 4 bytes, each in a separate block, delete/insert is performed.

update t1 set col2 =

'ZbcdefghijZlmnopqrstuvZxyzabcdefghijkZmnopqrstuvwxyz'

where col1 = 2

For single-row, in-place updates, the optimizer must be able to determine ahead of time that only one row will qualify. That is, there must be a unique index, and that index must be used to drive the search. The column being updated can be a nonclustered index key, and that index may also be used to drive the search.

Multiple rows can be updated in-place but only if the following conditions apply:

The updated column is fixed length.
The clustered index is not updated.
The updated column cannot be a nonclustered index column used to drive the search. However, other nonclustered index columns can be updated.