INF: What Happens When UPDATE Performs INSERT/DELETE

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

Four criteria must be met for an UPDATE statement to simply update the existing value in a column without performing a DELETE of the existing value and an INSERT of the new value.

MORE INFORMATION

These criteria are as follows:

1. The table being updated must not contain an update trigger. If the table has an update trigger on it, the trigger uses the logical tables "inserted" and "deleted" to first DELETE the existing value and then INSERT the new value.

2. The update statement must not be attempting to update a variable-length column, such as varchar(20). If an update is applied to a variable-length column, the existing value will be deleted, and the new value will then be inserted. This also applies to columns that allow nulls. SQL Server handles columns that allow nulls the same way as variable-length columns, so a DELETE followed by an INSERT must be performed.

3. The column being referenced in the update must not be an index field. If the column is part of an index, the update will DELETE the existing value and then INSERT the new value.

4. The update must reference only one row, and that row must be able to be determined prior to the update. For example, if the "employee_id" is defined as a unique index to the "Employee" table, the following update would simply update the column, WITHOUT having to DELETE the existing value and then INSERT the new value:

         UPDATE Employee
         SET employee_age = 32
         WHERE employee_id = 5167
   

   However, the next update would NOT simply do an update, but would have to DELETE the existing value and then INSERT the new value (because there is not a single row explicitly being referenced):

         UPDATE Employee
         SET employee_age = 0
   

5. The update cannot change more than half the total number of bytes in the row. SQL Server will determine that it is more efficient to DELETE and INSERT the record.