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:
Update of a variable-length key column:
update titles set title_id = value
where title_id = "T1234"
Update of a fixed-length nonkey column:
update titles set pub_date = value
where title_id = "T1234"
Update of a variable-length nonkey column:
update titles set notes = value
where title_id = "T1234"
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.
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.
