Deferred mode

The update mode is deferred.

In deferred mode, processing takes place in these steps:

  1. For each qualifying data row, Adaptive Server writes transaction log records for one deferred delete and one deferred insert.

  2. Adaptive Server scans the transaction log to process the deferred inserts, changing the data pages and any affected index pages.

Consider the following operation, where mytable is a heap without a clustered index or a unique nonclustered index:

insert mytable
    select title, price * 2
        from mytable
   QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is INSERT.
        The update mode is deferred.

        FROM TABLE
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE

This command copies every row in the table and appends the rows to the end of the table.

It needs to differentiate between the rows that are currently in the table (prior to the insert command) and the rows being inserted so that it does not get into a continuous loop of selecting a row, inserting it at the end of the table, selecting the row that it just inserted, and reinserting it.

The query processor solves this problem by performing the operation in two steps:

  1. It scans the existing table and writes insert records into the transaction log for each row that it finds.

  2. When all the “old” rows have been read, it scans the log and performs the insert operations.