As I understand it, when you update one or more rows in SQL Server, the record is deleted and reinserted with the new values. Does this therefore mean that an INSERT event is triggered, or just an UPDATE when rows are updated?
EDIT: To highlight the main info for any lazy readers (although I recommend that you read the full link details in davek 's answer below):
Does SQL do all updates as split updates?
Short answer is:
NO
Slight longer answer:
For updates that change the key values, SQL will not do those as
in-place updates.
I think that (the split into delete + insert) is only true when the update requires the index to be updated. See this link:
http://www.sqlservercentral.com/blogs/sqlinthewild/2011/06/21/are-all-updates-split-into-delete_2D00_insert_3F00_/
and particularly the last paragraph:
Now we do have a split update. We’ve got a delete_rows and an
insert_rows operation in the log. This was not done as an in-place
update So what can we conclude here? Does SQL do all updates as split
updates? It should be clear that, for cases where the index key is not
changed, SQL can do updates as in-place updates. I’m not going to try
and claim that it always will, that would be silly, there are lots of
scenarios that I haven’t looked at (page splits and forwarded rows
being among the most obvious), but it can and will do in-place
updates. For updates that change the key values, SQL will not do those
as in-place updates. Paul explained that in one of his debunking posts
a while back –
http://sqlskills.com/BLOGS/PAUL/post/Do-changes-to-index-keys-really-do-in-place-updates.aspx
An update never triggers an insert event even if physically it is implemented as an insert/delete as logically the operation is still an UPDATE.
There is a phrase in the accepted answer that is not quite true if taken to be talking about logical updates of the key column.
For updates that change the key values, SQL will not do those as
in-place updates
This is not the case for a multirow update against a unique index. For those SQL Server gives a plan with split/sort/collapse operators. So in the following example the 9 update operations get converted to 1 delete, 8 updates, and an insert.
CREATE TABLE TestingUpdate7 (
ID INT,
SomeString CHAR(50)
)
CREATE UNIQUE CLUSTERED INDEX idx_ID ON TestingUpdate7 (ID)
INSERT INTO TestingUpdate7 (ID, SomeString)
VALUES
(1,'One'),(2,'Two'),(3,'Three'),(4,'Four'),
(5,'Five'),(6,'Six'),(7,'Seven'),(8,'Eight'),(9,'Nine')
CHECKPOINT -- truncate the log, DB is in simple recovery.
UPDATE TestingUpdate7
SET ID +=1
SELECT Operation, Context, AllocUnitName
FROM fn_dblog(NULL, NULL)
Returns
+-----------------+--------------------+---------------------------+
| Operation | Context | AllocUnitName |
+-----------------+--------------------+---------------------------+
| LOP_BEGIN_CKPT | LCX_NULL | NULL |
| LOP_XACT_CKPT | LCX_BOOT_PAGE_CKPT | NULL |
| LOP_END_CKPT | LCX_NULL | NULL |
| LOP_BEGIN_XACT | LCX_NULL | NULL |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | dbo.TestingUpdate7.idx_ID |
| LOP_SET_BITS | LCX_PFS | dbo.TestingUpdate7.idx_ID |
| LOP_MODIFY_ROW | LCX_CLUSTERED | dbo.TestingUpdate7.idx_ID |
| LOP_MODIFY_ROW | LCX_CLUSTERED | dbo.TestingUpdate7.idx_ID |
| LOP_MODIFY_ROW | LCX_CLUSTERED | dbo.TestingUpdate7.idx_ID |
| LOP_MODIFY_ROW | LCX_CLUSTERED | dbo.TestingUpdate7.idx_ID |
| LOP_MODIFY_ROW | LCX_CLUSTERED | dbo.TestingUpdate7.idx_ID |
| LOP_MODIFY_ROW | LCX_CLUSTERED | dbo.TestingUpdate7.idx_ID |
| LOP_MODIFY_ROW | LCX_CLUSTERED | dbo.TestingUpdate7.idx_ID |
| LOP_MODIFY_ROW | LCX_CLUSTERED | dbo.TestingUpdate7.idx_ID |
| LOP_INSERT_ROWS | LCX_CLUSTERED | dbo.TestingUpdate7.idx_ID |
| LOP_COMMIT_XACT | LCX_NULL | NULL |
+-----------------+--------------------+---------------------------+
