Are all updates split into delete-insert?

This should be another quick one.

Earlier I saw a forum post where someone asserted that SQL always executes an update as a delete of the row and then an insert of the row with the new values. Now I need another excuse to play with undocumented features, so let’s see if that’s true (or at least true for all the common cases I try)

First up, a heap, no indexes at all, an update that does not change the size of the row.

CREATE TABLE TestingUpdate1 (
ID INT IDENTITY,
SomeString CHAR(50)
)

INSERT INTO TestingUpdate1 (SomeString)
VALUES
('One'),('Two'),('Three'),('Four'),('Five'),('Six'),('Seven'),('Eight'),('Nine')

CHECKPOINT -- truncate the log, DB is in simple recovery.

UPDATE TestingUpdate1
SET SomeString = 'NotFour'
WHERE ID = 4 -- one row

SELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog

DROP TABLE TestingUpdate1

UpdateHeapFixedSize

The log operation here is Modify Row. so in this case, the update was done as an in-place update.

Second test, a heap, no indexes at all, and an update that changes the row size (however plenty free space on the page, so no issues with forwarding pointers here)

CREATE TABLE TestingUpdate2 (
ID INT IDENTITY,
SomeString VARCHAR(50)
)

INSERT INTO TestingUpdate2 (SomeString)
VALUES
('One'),('Two'),('Three'),('Four'),('Five'),('Six'),('Seven'),('Eight'),('Nine')

CHECKPOINT -- truncate the log, DB is in simple recovery.

UPDATE TestingUpdate2
SET SomeString = 'NotFour'
WHERE ID = 4 -- one row

SELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog

DROP TABLE TestingUpdate2

UpdateHeapChangingSize

Again we have a modify row, so that again was an in-place update.

Last on the heap, let’s see if things change when there’s lots and lots of rows (I’m going to use an update that does not change the row size, I don’t want to involve forwarding pointers in the discussion here)

CREATE TABLE TestingUpdate3 (
ID INT IDENTITY,
SomeString CHAR(50)
)

INSERT INTO TestingUpdate3 (SomeString)
SELECT TOP (1000000) ' ' FROM msdb.sys.columns a CROSS JOIN msdb.sys.columns b

CHECKPOINT -- truncate the log, DB is in simple recovery.

UPDATE TestingUpdate3
SET SomeString = 'Something'

SELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog

DROP TABLE TestingUpdate3

UpdateHeapLotsOfRows

Still the modify row operation, so it’s not a case that lots of rows will cause SQL to split the update into a delete-insert pair.

Moving on, let’s try a table with a clustered index, an update of a non-key column that does not change the size of the row. Anyone willing to bet what we’ll see?

CREATE TABLE TestingUpdate4 (
ID INT IDENTITY,
SomeString CHAR(50)
)

CREATE CLUSTERED INDEX idx_ID ON TestingUpdate4 (ID)

INSERT INTO TestingUpdate4 (SomeString)
VALUES
('One'),('Two'),('Three'),('Four'),('Five'),('Six'),('Seven'),('Eight'),('Nine')

CHECKPOINT -- truncate the log, DB is in simple recovery.

UPDATE TestingUpdate4
SET SomeString = 'NotFour'
WHERE ID = 4 -- one row

SELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog

DROP TABLE TestingUpdate4

The log records are a little more complex, there’s a few more than in the previous case, but the operation for the update is still the same – LOP_Modify_Row

UpdateClusterFixedSize

Second test with a clustered index, an update of a non-key column that does change the size of the row.

CREATE TABLE TestingUpdate5 (
ID INT IDENTITY,
SomeString VARCHAR(50)
)

CREATE CLUSTERED INDEX idx_ID ON TestingUpdate5 (ID)

INSERT INTO TestingUpdate5 (SomeString)
VALUES
('One'),('Two'),('Three'),('Four'),('Five'),('Six'),('Seven'),('Eight'),('Nine')

CHECKPOINT -- truncate the log, DB is in simple recovery.

UPDATE TestingUpdate5
SET SomeString = 'NotFour'
WHERE ID = 4 -- one row

SELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog

DROP TABLE TestingUpdate5

Still have a modify row operation here. Still an in-place update.

UpdateClusterChangingSize

Lastly an update of the clustered index key value.

CREATE TABLE TestingUpdate6 (
ID INT,
SomeString CHAR(50)
)

CREATE CLUSTERED INDEX idx_ID ON TestingUpdate6 (ID)

INSERT INTO TestingUpdate6 (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 TestingUpdate6
SET SomeString = 'NotFour',
ID = 42
WHERE ID = 4 -- one row

SELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog

DROP TABLE TestingUpdate6

SplitUpdate

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

5 Responses to “Are all updates split into delete-insert?”

  1. Gail

    Thanks for the post.

  2. [...] Are all updates split into delete-insert? – Looking at the internals of the transaction log this week from Gail Shaw (Blog|Twitter). I had the privilege to sit along side Gail this week at the SQLskills event, although how she managed to find the time to squeeze in yet more sql stuff I have no idea [...]

  3. Hi Gail

    I have seen Paul’s blog as well.Other things are looking fine but when you use page id from fn_dblog() you get the page Id for modified rows either for table sysrscols or sysobjvalues or syssingleobjrefs.Why is that? Is fn_dblog is showing wrong data?

  4. This answers my question which I asked on sqlserver central from you through email.
    But in one post i stairways article i read that DML causes the delete/insert rather than in place update but that doesnt seem true.

  5. What page ID from where?

Leave a Reply