In SQL, is UPDATE always faster than DELETE+INSERT?

A bit too late with this answer, but since I faced a similar question, I made a test with JMeter and a MySQL server on same machine, where I have used:

1. A transaction Controller (generating parent sample) that contained two JDBC Requests: a Delete and an Insert statement
2. A sepparate JDBC Request containing the Update statement.

After running the test for 500 loops, I have obtained the following results:

DEL + INSERT - Average: 62ms

Update - Average: 30ms

Results:

The bigger the table (number of and size of columns) the more expensive it becomes to delete and insert rather than update. Because you have to pay the price of UNDO and REDO. DELETEs consume more UNDO space than UPDATEs, and your REDO contains twice as many statements as are necessary.

Besides, it is plain wrong from a business point of view. Consider how much harder it would be to understand a notional audit trail on that table.

There are some scenarios involving bulk updates of all the rows in a table where it is faster to create a new table using CTAS from the old table (applying the update in the the projection of the SELECT clause), dropping the old table and renaming the new table. The side-effects are creating indexes, managing constraints and renewing privileges, but it is worth considering.

One command on the same row should always be faster than two on that same row. So the UPDATE only would be better.

EDITset up the table:

create table YourTable(YourName  varchar(50)  primary key,Tag int)insert into YourTable values ('first value',1)

run this, which takes 1 second on my system (sql server 2005):

SET NOCOUNT ONdeclare @x intdeclare @y intselect @x=0,@y=0UPDATE YourTable set YourName='new name'while @x<10000begin    Set @x=@x+1    update YourTable set YourName='new name' where YourName='new name'    SET @y=@y+@@ROWCOUNTendprint @y

run this, which took 2 seconds on my system:

SET NOCOUNT ONdeclare @x intdeclare @y intselect @x=0,@y=0while @x<10000begin    Set @x=@x+1    DELETE YourTable WHERE YourName='new name'    insert into YourTable values ('new name',1)    SET @y=@y+@@ROWCOUNTendprint @y