ID Number: Q66938
1.10 1.11 4.20
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. 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.
Additional reference words: update insert delete