Does sql update do a delete then insert?

Question

How exactly does Sql server perform an update operation? Does it do a in place update? or it does a delete then insert for each of the rows affacted? I understand that an update operation is similar to that of delete then insert for the old value is stored in the deleted and new value is stored to the inserted...

I was told the update operation is accomplished by deleting then inserting, but I am not sure why it would be implemented as so. Please enlighten me.

Answer 1

MSDN Update section:

The Database Engine converts a partial update to a full update when the UPDATE statement causes either of these actions:

• Changes a key column of the partitioned view or table.

• Modifies more than one row and also updates the key of a nonunique clustered index to a nonconstant value.

And partial update

Partial-update transaction directly writes a character string at a user-defined position of a table column without issuing a delete and replace command, as would happen in a full update.

Partial update does not support multibyte character set conversion. Partial update support is restricted to Microsoft SQL Server.

I did some searching and it looks like the answer to your question depends on what you are updateing.

Hope this helps.

Answer 2

Depends what you mean. If you're asking if an delete and insert are similar to an update in performance, the answer would be no. People have already asked that question. They say Update is faster because of it must only be logged once and they might have listed other reasons but I didn't read on. In SQL, is UPDATE always faster than DELETE+INSERT?

In relation to a trigger that fires after an update statement, you can access the "deleted" and "inserted" tables which will contain the old and new row respectively. So it may seem like it deletes then updates.

For me I don't really care what happens in the background. My best guess is that SQL Server simply changes the old value to the new value because that's probably the most efficient. But either way, just choose whichever performs the best.