Re: When does Update become Delene then Insert, Ora817

This is for MS SQL Server, not sure which version.

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.

"Keith Boulton" <kboulton_at_ntlworld.com> wrote in message news:j92d8.1936$hM6.116901_at_news6-win.server.ntlworld.com...
> I think you're right.
>
> In ss (from memory) I think there were three possible update mechanisms:
>
> update in place - this is why there was a performance advantage to using
> char columns and not varchar
> update rewrite - I think this was like an oracle update where the row is
> reformatted and rewritten to the block
> delete/insert - the record was physically deleted and then re-inserted
e.g.
> when the primary key of clustered table was updated. Presumably, this is
> what happens in Oracle when row-migration between partitions occurs.
>
> My apologies if this is not exactly right, but I think it explains the
> issue.
>
Received on Thu Feb 21 2002 - 09:13:45 CST