The update mode is deferred.
In deferred mode, processing takes place in these steps:
For each qualifying data row, Adaptive Server writes transaction log records for one deferred delete and one deferred insert.
Adaptive Server scans the transaction log to process the deferred inserts, changing the data pages and any affected index pages.
Consider the following insert...select 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
mytable
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
mytable
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:
It scans the existing table and writes insert records into the transaction log for each row that it finds.
When all the “old” rows have been read, it scans the log and performs the insert operations.
