INF: Update Methods Used in SQL Server 6.0

• Microsoft SQL Server, version 6.0

SUMMARY

Microsoft SQL Server version 6.0 can process an UPDATE statement using one of four possible methods. These methods are: deferred, full delete/insert, on-page delete/insert, and in-place. This article discusses the rules that determine which method is used.

MORE INFORMATION

The decision concerning deferred update is made at compile time; the decision concerning the three non-deferred methods is made at execution time on a row-by-row basis. Since Showplan is based on compile-time information and not run-time, the update mode it reports may be different from how the query was actually executed at runtime. You should note that if the showplan output indicates DEFERRED, the actual query plan used will always be deferred. To see exactly how the query was processed for non- deferred udpates, you can use trace flag 323. This is slightly different from other trace flags; it will only display output to the errorlog, not to the local session, and it will only write to the errorlog if SQL Server is started from the command line (for example, sqlservr -c - dc:\c:\sql60\data\master.dat). (NOTE: This will likely change in post- version 6.0 builds of SQL Server; it should follow the normal method of trace output using 3604/3605.)

DEFERRED

When multiple rows can qualify for the update, if the row can increase in size or if indexed columns are updated the rows could move out ahead of the current position and be processed again (the "Halloween problem"). For example:

   UPDATE T1
   SET col1 = col1 + 1

IN-PLACE

When an update is done in-place, a log record of type MODIFY is recorded in the log. This record does not contain the old and new values of the row (only the differences), and it cannot handle changes in row length. For in-place updates, the following restrictions apply:

• Table cannot contain an UPDATE trigger.
• Table cannot be marked for replication.
• Can only be one table involved in the UPDATE statement. Note that primary/foreign key references automatically mean that more than one table is involved. This rule includes any sub-select in the update query including a self-join. Whenever more than one table is involved, a deferred update takes place.
• Column being updated cannot be part of a clustered index.
• Column being updated can be variable length, but the new row length must be exactly the same as the old row length.
• New row can not be "too different" from the old one. The number of differing bytes can not be more than one half the total row size, and the total number of discontiguous differing blocks is not more than three (differing blocks within 8 bytes are considered a single block).

  Examples:

      create table t1 (col1 int, col2 char(60))
      go
      insert t1 values
      (1,'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz')
  

      insert t1 values
      (2,'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz')
  

  - continue for 1000 rows -

      go
      create unique index idx1 on t1 (col1)
      go
  

   confuse this with one half of the column length), it will do
   delete/insert instead of in-place:

       update t1 set col2 =
       'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzjklmnopqrstuvwxyz'
       where col1 = 1
       go

   In the above case you have a row size of 64 bytes and you are changing
   35 bytes so it will do delete/insert.

 B) If you have more than 3 differing blocks within the column being
    updated:

       update t1 set col2 =
       'ZbcdefghijZlmnopqrstuvZxyzabcdefghijkZmnopqrstuvwxyz'
       where col1 = 2
       go

    In the above example you are changing 4 bytes, each of which is in a
    separate "block", so it must use delete/insert.

    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:

      - Updated column is fixed-length
      - Clustered index is not updated
      - Updated column can not be nonclustered index column used to
        drive the search (other nc index columns can be updated though)

ON-PAGE DELETE/INSERT

On-page delete/insert is an option when one of the above requirements for in-place update is not met. It is more efficient than a full delete/insert because the same row slot is used and only those nonclustered indexes that are actually updated are "fixed-up." If the new row is not the same size as the old row, but there is still enough room on that page for the new row, then on-page delete/insert is used.

FULL DELETE/INSERT

In full delete/insert, full delete and insert log records are recorded for the row being updated. In addition, all nonclustered index keys are deleted and re-inserted, regardless of whether or not they were updated. Full delete/insert is used when on-page delete/insert cannot be used, either when there is not enough room on the page for the new row, or when a clustered index key is updated.