There are two methods, or modes, that SQL Server can use to perform update operations such as INSERT, DELETE, UPDATE, and SELECT INTO. These methods are called deferred update and direct update. When the deferred method is used, the changes are applied to all rows of the table by making log records in the transaction log to reflect the old and new value of the column(s) being modified (in the case of UPDATE operations), or the values that will be inserted or deleted (in the case of INSERT and DELETE, respectively).
When all the log records have been constructed, the changes are then applied to the data pages. This method may generate more log records than a direct update (discussed later in this chapter), but it has the advantage of allowing the execution of statements that might cascade changes throughout a table.
For example, consider a table that has a fixed-length column col1 with a unique index on it and data values numbered consecutively from 1 through 100 in that column. Assume that an UPDATE statement is executed to increase the value in each row by 1:
UPDATE Mytable SET col1 = col1 1
STEP 1 The type of query is UPDATE The update mode is deferred FROM TABLE Mytable Nested iteration Table Scan TO TABLE Mytable
Consider the consequences of starting at the first row in the table and updating each row, through the end of the table. Updating the first row (which has an initial value of 1) to 2 would cause an error because the unique index would be violated (there is already a value of 2 in the table); similarly, updating the second row (which has an initial value of 2) to 3 would also cause a unique key violation, as would all rows through the end of the table, except for the last row. By using deferred updates, this problem is easily avoided. The log records are first constructed to show what the new values for each row will be, the existing rows are deleted, and then the new values inserted.
As with UPDATE statements, INSERT statements can also be deferred for similar reasons. Consider the following query (there is no clustered index or unique index on the Mytable2 table):
INSERT Mytable2 SELECT * FROM Mytable2
STEP 1 The type of query is INSERT The update mode is deferred FROM TABLE Mytable2 Nested iteration Table Scan TO TABLE Mytable2
Because there is no clustered index on the table, the new rows will be added to the end of the table. The query processor needs to be able to differentiate between the existing rows that are currently in the table (prior to the INSERT statement) and the rows that will be inserted, so as to not get into a continuous loop of selecting a row, inserting it at the end of the table, selecting the row just inserted, and re-inserting it.
By using the deferred method of inserting, the log records can first be constructed to show all the existing values in the table, and then SQL Server will re-read those log records to insert them into the table.