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.

Whoever told you this "update = delete+insert" is wrong - SQL Server updates the data that is already stored on the page by setting the new values you provide. — marc_s, Apr 22 '15 at 04:57

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

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.

Yes, this is what I am looking for. Also as Damien mentioned above, if it is part of the clustered index, it would get complex. Here is what I found in sql server community forum: — junli antolovich, Apr 22 '15 at 16:48

score 2 · Answer 2 · edited May 23 '17 at 12:32

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.

In this case, I am interested in what happens in the database engine during the update operation. As I found the answer in SQL server community here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d5bd0c18-ac1b-4a8a-ac22-a7ac9cc36d72/update-delete-insert?forum=transactsql — junli antolovich, Apr 22 '15 at 16:31

score 2 · Answer 3 · answered Apr 22 '15 at 06:08

It depends. If you're updating the column(s) that are part of the clustered index, then yes, SQL Server may, internally, change the operation to be more like a delete of the old row(s) and an insert of the new row(s)¹.

If you're not changing the clustered index, then SQL Server will usually perform a more efficient update in place.

But at the end of the day, it doesn't really matter because what physical operations actually occur shouldn't have any visible effects for you.

¹Where things can get really funky is if your update changes the clustered index of multiple rows. The system may decompose the update into deletes and inserts, but if one of the rows to insert will occupy the same location as one of the rows being deleted, those operations may get recombined into an update, even if, at a logical level, they were originally dealing with different rows.

Does sql update do a delete then insert?

3 Answers3