Effects of update types and indexes on update modes

Table 5-2 shows how indexes affect the update mode for three different types of updates. In all cases, duplicate rows are not allowed. For the indexed cases, the index is on title_id. The three types of updates are:

Table 5-2 shows how a unique index can promote a more efficient update mode than a nonunique index on the same key. Pay particular attention to the differences between direct and deferred in the shaded areas of the table. For example, with a unique clustered index, all of these updates can be performed in direct mode, but they must be performed in deferred mode if the index is nonunique.

For a table with a nonunique clustered index, a unique index on any other column in the table provides improved update performance. In some cases, you may want to add an IDENTITY column to a table in order to include the column as a key in an index that would otherwise be nonunique.

Table 5-2: Effects of indexing on update mode

Update To:

Index

Variable- length key

Fixed-length column

Variable- length column

No index

N/A

direct

deferred_varcol

Clustered, unique

direct

direct

direct

Clustered, not unique

deferred

deferred

deferred

Clustered, not unique, with a unique index on another column

deferred

direct

deferred_varcol

Nonclustered, unique

deferred_varcol

direct

direct

Nonclustered, not unique

deferred_varcol

direct

deferred_varcol

If the key for an index is fixed length, the only difference in update modes from those shown in the table occurs for nonclustered indexes. For a nonclustered, nonunique index, the update mode is deferred_index for updates to the key. For a nonclustered, unique index, the update mode is direct for updates to the key.

If the length of varchar or varbinary is close to the maximum length, use char or binary instead. Each variable-length column adds row overhead and increases the possibility of deferred updates.

Using max_rows_per_page to reduce the number of rows allowed on a page increases direct updates, because an update that increases the length of a variable-length column may still fit on the same page.

For more information on using max_rows_per_page, see “Using max_rows_per_page on allpages-locked tables” on page 202 in the book Performance and Tuning: Basics.