Deferred updates are always required for:
Updates that use self-joins
Updates to columns used for self-referential integrity
Updates to a table referenced in a correlated subquery
Deferred updates are also required when:
The update moves a row to a new page while the table is being accessed via a table scan or a clustered index.
Duplicate rows are not allowed in the table, and there is no unique index to prevent them.
The index used to find the data row is not unique, and the row is moved because the update changes the clustered index key or because the new row does not fit on the page.
Deferred updates incur more overhead than direct updates because they require Adaptive Server to reread the transaction log to make the final changes to the data and indexes. This involves additional traversal of the index trees.
For example, if there is a clustered index on title, this query performs a deferred update:
update titles set title = "Portable C Software" where title = "Designing Portable Software"
