The update mode is direct

Whenever possible, SQL Server attempts to use the direct method of applying updates to tables because it is faster and requires fewer log records to be generated than the deferred method. Depending on the type of statement, certain criteria must be met in order for SQL Server to perform the update using the direct method. Those criteria are:

Rules for an Update-In-Place

In SQL Server 6.0, various techniques are used to determine whether or not an UPDATE can be performed "in-place." When an "update-in-place" occurs, the overall performance of the UPDATE statement is improved because the update is direct (only the row modifications to the page are logged). The decision as to whether or not an update-in-place can occur is based on the following requirements. If these requirements are not true, a deferred update will take place. A deferred update is a delete followed by an insert.

• The UPDATE cannot affect the column(s) that participates in a clustered index.
• The table cannot have an UPDATE trigger.
• The table cannot be marked for replication.
• For single-row updates:
  • The updated column(s) can be variable length, but the new total row size must fit on the same page as the old row.
  • The updated column(s) can participate in a nonunique nonclustered index only if the index key is a fixed-width column.
  • The updated column(s) can participate in a unique nonclustered index only if the index key is fixed-width and the WHERE clause criteria must have an exact match (based on an unique index match¾the index could be the same as the updated column).
  • The new row cannot include differing bytes by more than 50 percent of the original row size, and the total number of discontiguous differing bytes is not more than 24.
• For multirow updates:
  • The updated column must be fixed length.
  • The updated column cannot participate in a unique nonclustered index.
  • The updated column can participate in a nonunique nonclustered index only if the column is a fixed-width column (the index used to find rows cannot be the same as the updated column).
  • The table cannot include a column with the timestamp datatype.

To see the update strategy used (DIRECT or DEFERRED), set the SHOWPLAN session setting. For details, see the SET statement.

Query 1:

UPDATE titles
SET price = $5.99
WHERE title_id = 'BU2075'

SHOWPLAN 1:

STEP 1
The type of query is UPDATE
The update mode is direct
FROM TABLE
titles
Nested iteration
Using Clustered Index
TO TABLE
titles

Query 2:

UPDATE titles
SET price = $5.99
WHERE title_id like 'BU%'

SHOWPLAN 2:

STEP 1
The type of query is UPDATE
The update mode is deferred
FROM TABLE
titles
Nested iteration
Using Clustered Index
TO TABLE
titles

Note that the only difference between Query 1 and Query 2 is qualification of the WHERE clause. In both queries, the column being updated allows nulls. In Query 1, no more than one row is guaranteed to qualify because of the unique clustered index on the title_id column. Therefore, the direct method can be used for the update. In Query 2, multiple rows may qualify for the update, and since the updated column allows nulls the deferred method must be used.